You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
You have now imported one of the text files as an Excel table with an underlying query table.
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.
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.
Try it out for yourself. Get the sample text files from the attachments at the bottom of this post.
My god, you did all that to append multiple text files into one worksheet?
What about just starting with
C:> copy *.csv single-file-to-import.csv
and then import it?
Interesting! You *could* have just copy *.csv single-file.csved as stated above, but then you are duplicating headers. Plus, I just learned something about Excel, which is one of those apps you think you know everything about--until an article like this one.
Copying the files certainly works when there are no column headers. But if you have column headers in each file, they get appended and duplicated to the data file as well. Some folks may not want this, especially if there are many files to append, and they don’t have control over the headers because the files are feeds from other systems. Also, the SQL Union clause gives added flexibility that the copy command does not. Hope that clarifies.
have u any tips on setting up reminders in outlook with excel 2007. I have a maintenance sheet with next service dates that i would like to be notified about through outlook. any help would be appreciated.
ps i dont know much about coding so make it simple
Hi, I just found your site, while researching Excel Data Experts. I really liked this particular post. Have you seen other similar Excel data and/or tool sites? I'd enjoy connecting by email if you do. My client's site is www.tableausoftware.com/fast-analytics, if you're interested. Keep up the blog posts -- good stuff!
Frank: I'm not aware of any tips. That would require some custom coding. Perhaps someone has already written an add-in that does this? I'ts a neat idea.
TJ: There are lots of dedicated Excel sites out there that are easy to find with good tips/tricks/tools/etc.
This procedure took me forever to figure out on my own... Would have been helpful if I had this post a year ago!
I must collect data from 25+ separate CSV files every 15 minutes, compile the data, and create reports based on the data. Using this method is MUCH faster than 'open-copy-paste-close'.
Glad to help.
Briefly, you might try setting up a rule in Outlook that each day runs Excel and opens your spreadsheet. Then perhaps conditionally format maintenance items that are due for that day.
However, I tried sending email to myself and it's surprisingly hard to do in a simple way without secured code as it triggers security bells and whistles.
Perhaps Windows scheduler is a better way to go. Every day open the file and display the results.
Does this only work in Office 2008? I'm working in Office 2003 and can't find the Connections group in the Data tab. If so, any ideas on how to do this in Office 2003?
Thanks, was looking for solution Phase1. Needed an odc file for sharepoint 2007 excel web services pointing to a flat file.
Should work the same way in 2003:
To create a connection to the data, use
Data | Import External Data | Import Data | New Source
To edit the query, use Data | Import External Data | Edit Query
This is great! However, I have one problem when I try to use it. My first field on each record is an IP address, formatted as x.x.x.x, and the query you describe takes out all the periods in the IP address except the first one. Is there a way to keep it from doing that? (All other field in each record are seaparated by a comma.) Thanks!!
Otherwise, very helpful.
It would be nice if you also mention the limitations of this feature especially to people who may want to use it for a business need (as agaist a demo on an blog)
The number of Colums that are allowed in each text file (or any other file) = Int(255/n)
where n = number of files you want to union
In a real life senario it is not unrealistic to expect a file with say 90 Columns
So if you try and union 3 files (=90*3 >255) you will get an error - Too many fields
Also this is another old feature from the days of Excel 97....
Excel is interpreting the first field as a number because of the first period which it assumes is a decimal point. You need to specify exactly what the data type is by using a schema.ini file which must be locatded in the same folder as the .csv files. I added an IP column to the sample data and it retested fine. Here's the Schema.ini file:
Here's a link to more info about Schema.ini files: msdn.microsoft.com/.../ms709353.aspx
Correct. There is a limit to the number of columns that an SQL Union query can process, so the total is 255, whatever combination of columns and files yields that. If you exceed the limit, you could process the files several times: Say you have 4 files of up to 127 columns each. Do the first 2, then save that out as a .csv file. Do the next 2, save that out. Now you have two larger files of 127 columns, for a total of 254. Now process one more time. Now you have 1 large file appended with the original 4 with 127 columns. You can do his indefinitely. This could easily be automated.
If your text files are even larger, then consider using XML. You can open a .csv file in Excel (not import) without hitting the 255 column limit. Then use the Excel 2003 XML Tools Add-in to convert to XML. See office.microsoft.com/.../HA102635091033.aspx. Then use the XML features of Excel to append all the data together.