Combining two or more CSV text files in the same sheet in OpenOffice Calc

Let's say you get a bunch of CSV files exported to you each week, or each month. They're all related so you want them all together in the same sheet so you can analyze them together.

And when you export a new version of CSV file, you'd like that exported data to automatically update the spreadsheet. So if the new amount for account AB-12 is 10,000 in January and 12,000 in February, you want the spreadsheet to show 12,000 when you export the data February 1st.

This would be great with Insert > Sheet From File but you can't combine those sheets. So just use either Paste Special or, preferably, basic Sheetname.cellname syntax, in a central "combo sheet" to refer to separate sheets linked to each CSV file.

Want more detail? See this PDF.

And because I like to be sure that key search terms come up as much as possible, I'm pasting the content in here too. I know the screen shots didn't come up but I want to get this posted so am acknowledging laziness. ;>  The screen shots are all in the PDF so just use that; this is just for extra searchability on this topic.

Using Two or More CSV Files on the Same Sheet of a Spreadsheet

Solveig Haugland, GetOpenOffice.org

There are three basic steps:

1. Determine the Name and Location of the CSV Files

2. Bring Each CSV File Into a Separate Sheet

3. Combine the Separate Sheets Together in One Sheet

Notes before beginning:

You must use the “link” checkbox when bringing in CSVs.

You can rename the individual sheets that you bring the individual CSV files into, once you create them, but don’t rename them after you create the combo sheet.

Determine the Name and Location of the CSV Files

The CSV files can be in different directories and can have any names, though I strongly recommend that there be no spaces in the names.

If you want to replace the CSV information that will be brought into the spreadsheet when you export a new CSV, then when you create new CSV files, make sure each new CSV file with updated information has the same file name and is put in the same directory.

Bring Each CSV File Into a Separate Sheet

In a new or existing spreadsheet, follow these steps.

1. Choose Insert > Sheet From File.

2. A window will appear, as well as another on top of it asking you to select a file. Pick the CSV file you want to insert.

3. In the next window, you’ll be asked to pick the separator that separates the columns in the CSV file. It’s usually comma but sometimes tab. Usually the program will pick the right one automatically. If it looks good in the bottom part, with the data in columns looking right, then just click OK. Otherwise select a different checkbox; for instance, uncheck Tab and check Comma.

4. Click OK.

5. In the window that is now shown, select Link. Nothing else matters since you can drag the sheets around where you want them once they’re created.

6. Click OK.
   

7. The data will appear in a new tab in your spreadsheet. If the data in the file changes, the data here changes.

8. To clarify for yourself exactly what data is in this sheet, right-click on the sheet name and choose Rename. Type a new name for the sheet and click OK. Make a note of the name you give the sheet, you will type it in the next step. In the examples, I rename this sheet january_accounts.

9. Repeat the steps in this section for each additional CSV file you want in the same spreadsheet.

Combine the Separate Sheets Together in One Sheet

Create or select a sheet to use to combine the sheets. I recommend you name it something distinctive to be sure it’s clear this is the combined sheet. This is different from the procedure in class but addresses the problems that we had with Paste Special.

1. Go to the first sheet that’s linked to a CSV file that you want to put in the combo sheet. Note the sheet name. This is the name you gave to it when you renamed it, after importing the CSV file.
   
   Also look at the data. How many columns across is it? Does it go to G or to HH? Then note how many rows it has; does it go down to 10 or to 400?

2. Go back to the combo sheet. Click where you want the data to start appearing.

3. Type the name of the sheet, then A.1. and press Enter. The syntax is =sheetname.A.1 and an example is january_accounts.A.1.

4. Once you’ve typed the text from the previous step and pressed Enter, you’ll see the first cell from the sheet you’re bringing in.

5. Copy that cell.

6. Now select as many cells as you could possibly need to show all the data from the sheet you’re bringing in. You noted this in the first step. Select from A1 to G100, if necessary, or from A1 to JJ459.
   
   To ensure that there is always enough space for the CSV data if there are more rows next time, select a few extra rows, perhaps 10 extra rows for a small sheet or 100 extra rows for an enormous one.

7. Paste: just do a normal paste. You’ll see something like this, with 0s showing where there is not (currently) any data in the sheet with the CSV file.

8. Now go down farther in the combo sheet to where you want the data from the next CSV file’s sheet to start, and repeat the steps in this section.

---

---

Comments

[Jo]

This is really useful my website has a tool to email people by uploading csvs but sometimes I have 2, 3 or more lists of people I want to include

[ugg boots]

I recently came accross your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.