2
I'm looking to merge CSVs that have common columns, but the columns aren't always necessarily in the same place. The CSVs do have a header row. I just want to try to get the data appended to the bottom. For example, each CSV will have the column "Name,""Favorite Color," and "Lucky Number." Some CSVs will also contain other columns, such as "Favorite Animal." Also, "Name" may not be in the same spot on every CSV. Note: the end goal here is for me to be able to do a pivot chart with the data. I know (or I think) that Excel can make a pivot table/chart from multiple worksheets -- but I'm not sure it can handle 52 worksheets that are plagued by the problems I mentioned above. Any thoughts? Note -- I think I could probably get this to work with Google App Script, but the resulting spreadsheet would be too big for Google Spreadsheets to handle.
Here's some examples of what I'm saying... https://gist.github.com/anonymous/4948945
DaneC
Posted 2013-02-13T23:18:21.757
Reputation: 21
Hello again. What Excel version do you use? Excel 2003 is limited to 255 columns. Later versions are up to 65536 columns. Are you interested in a Excel only solution? This probably involves the ugly MS query tool (guide) to create an SQL-like query. Or is Access also a valuable solution? This way its much easier to merge all CSVs to a single table. This table can be exported or viewed by Excel in every way.
– nixda – 2013-02-13T23:30:15.657Hi again Nixda, and thanks for the good advice last time I posted. I think Excel 2010 has a row limit of 1,048,576 rows by 16,384 columns, and I will be within that for this year's data. I'm looking into Access for this year's data, which will exceed those limits. I need to learn more about Access, but I haven't ever used it. Would I still need to do some programming within Access to accomplish my goals? I'm really hoping I don't have to just put in the hours and do this manually. Every one of the 52 sheets has over 60 columns and thousands of rows. – DaneC – 2013-02-14T00:24:52.007
To add to @nixda's comment, Access 2010 may well be the way for you to go, since you would be able to append your CSV files to a common table without worrying about the order of the columns. The number of columns you mention is well within Access 2010's limit of 255 columns per table (I don't know about 2013). Access also has a database size limit of 2 gigabytes (though linking databases is a workaround). However, I've run into serious performance problems when DB size went much over 1 gigabyte. – chuff – 2013-02-14T01:21:14.803
Ok. I bought Access 2013. Am importing the first CSV now. Any good resources for learning this program? Do I need to cut each column into its own table (seeing as sometimes columns are in different spots)? – DaneC – 2013-02-14T04:04:39.623
The neat thing about appending an external file to a table in Access is that only the column names have to match, not their locations. In other words, if you have a header row in your CSV with your column names, then you can append to a table with the same names. Access will squawk, though, if the field types don't match or if you have a name in your CSV file that is not in the table. – chuff – 2013-02-14T07:08:01.757
Yep, there was some serious squawking. I'm currently trying to make a master header file that I'll upload and then try to append every CSV to that... We'll see.