Microsoft Excel

Append Multiple Text Files into a Worksheet without Code

Today's author: Mark Gillis, an Excel and SharePoint writer, who's been through six versions of Office, survived to tell the tale, and picked up a thing or two along the way.

Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string.

Here's the deal. You have three CSV text files containing sales data, Beverages.csv, Condiments.csv, and Confections.csv. Each file has the same number of columns, the same column headers for each column, and the same type of data in each column. In this case, you're not going to use the Text Import Wizard. Rather, you'll use the Data Connection Wizard to create a connection (either OLEDB or ODBC) to one of the text files. Then, you'll modify the command type and text portion of the connection string and turn it into an SQL UNION statement, a query that effectively appends all of the text files.

Import a CSV file by using an OLEDB provider

Phase 1: Import one text file

1. In a new workbook, on the Data tab, in the Get External Data group, click From Other Sources, and then click Data Connection Wizard.
2. In the Select Data Source dialog box, click New Source, to display the Data Connection Wizard.
3. In the Welcome to the Data Connection Wizard page, click Other/Advanced to display the Data Link Properties dialog box.
4. On the Provider tab, select Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. On the Connections tab, in the Select or enter a database name box, enter the full path to the folder that contains the text files (just the path, and not with any filename).
   
6. Click the All tab, select Extended Properties, click Edit Value, enter Text;HDR=Yes, and then Click OK.
   
7. To ensure that you entered the correct information, click the Connection tab, and then click Test Connection. If you see the message, "Test connection succeeded", Click OK twice. If not, recheck your steps.
8. In the Select Database and Table page, under the Name column, select one of the text files that you want to import, such as Beverages#csv, and then click Next.
   
9. In the Save Data Connection File and Finish page, enter a name for the ODC file in the File Name box, and then click Finish.
10. In the Import Data dialog box, accept the default option values, and then click OK.

You have now imported one of the text files as an Excel table with an underlying query table.

Phase 2: Add the SQL statement and import all the text files

• With one of the cells in the Excel table selected, on the Data tab, in the Connections group, click Properties, and then in the External Data Properties dialog box, click the Connection Properties button next to the Name box.

• Click the Definition tab, and then in the Command Type box, select SQL.
• In the Command text box, remove the string, Beverages#csv, and then enter the following SQL statement:
  
  Note Use UNION when you want a unique set of rows, and remove duplicates in the process, like those pesky addresses from folks who endlessly surf the Web and have entered their personal information more than once. Use UNION ALL when you want to keep the duplicate rows for whatever reason. In general, the UNION ALL phrase should be faster, because there's no extra checking for duplicate rows.
• Click OK.
  Excel displays a message warning you that the connection string in the workbook is different than the one in the connection file you saved in step 9 above. Click Yes to continue. You can fix this a bit later.
• To close the External Data Properties dialog box and run the query, click OK.

You have reached the moment of truth. You should now see data from all three text files in one worksheet, like so. The data is sorted in asending order by Quarter so you can see some of the data from the other two text files.

If you now want to make the connection string in the connection file the same as in the workbook, get back to the Definition tab in the Connection Properties dialog box, and click Export Connection File to re-save the connection file with the new connection string.

Import a CSV file by using an ODBC driver

Now that you get the general idea, here are the ODBC instructions, but in somewhat briefer format. The basic steps are similar, and the results are the same. The main difference to be aware of is the slightly different form of the SQL statement syntax.

Phase 1: Define a User DSN (If necessary)

1. Open Microsoft Windows Control Panel, double-click the Administrative Tools icon, and then double-click the Data Sources (ODBC) icon.
2. In the ODBC Database Administrator dialog box, on the User DSN tab, click Add.
3. In the Create New Data Source dialog box, select Microsoft Text Driver (*.txt; *.csv), and then click Finish to display the ODBC Text Setup dialog box appears.
4. Enter a name in the Data Source Name.
5. Clear the Use Current Directory check box.
6. Click Select Directory.
7. In the Select Directory dialog box, locate the folder that contains the text file that you want to import, make sure that the text file appears in the list below the File Name box, and then click OK.
8. Click OK twice.

Phase 2: Import one text file

1. In a new workbook, on the Data tab, in the Get External Data group, click From Other Sources, and then click Data Connection Wizard.
2. In the Welcome to the Data Connection Wizard page, click ODBC DSN.
3. In the Connect to ODBC Data Source page, select the User DSN that you just created, and then click Next.
4. In the Select Database and Table page, under the Name column, select one of the text files that you want to import, such as Beverages.csv, and then click Next.
5. In the Save Data Connection File and Finish page, enter a name for the ODC file in the File Name box, and then click Finish.
6. In the Import Data dialog box, accept the default option values, and then click OK.

Phase 3: Add the SQL statement and import all the text files

• With one of the cells in the Excel table selected, on the Data tab, in the Connections group, click Properties, and then in the External Data Properties dialog box, click the Connection Properties button next to the Name box.

• Click the Definition tab, and in the Command Text box, remove the SQL statement, and enter the following SQL statement:
  
  Note The grave accent character (`) is required as a delimiter for each filename. This character is usually located under the tilde character (~) on a keyboard.
• Click OK, and then click Yes.
• To remove the External Data Properties dialog box, and run the query, click OK.

Try it out for yourself. Get the sample text files from the attachments at the bottom of this post.