pivot table external data sources, multiple csv files Any help appreciated

[FriendlyGardener]

Hi everyone

I have been reading and googling and trying and failing :-(


This is a bit advanced for me, but I am trying to bring data into a Pivot table from external data sources, namely csv files. I can get it to work for one .csv file but then when i got to do another I have problems


I am not understanding if I need to keep setting up a New data source for each csv file?

eg March.csv, april.csv, may.csv etc?


Also sometimes i don't see all the columns eg when you get to the part you choose which columns it only shows F1 F2 ( but there are many more than this) so I keep deleting the data source and redoing it.... arrgg very frustrating


Also as I have multiple csv files all set up the same, and I would ideally like to bring data from more than one at a time into my Pivot table but because they are the same I cannot find a way to "join" them..


I use Excel 2003, Any help or good books or other ways to find out more about this?


thanks

 

[oldchippy]

Hi,


Go over to the Contextures website these may offer some help?


http://www.contextures.com/xlPivot08.html


or may be this?


http://excelusergroup.org/forums/t/815.aspx

 

[Hui]

FriendlyGardener

Not my area of expertise but have a look at

http://blogs.msdn.com/excel/archive/2008/04/30/append-multiple-text-files-into-a-worksheet-without-code.aspx

It is written based on 2010 and so not sure how backward compatible it might be or applicable to your application

Let us know how you go

ps: It works in 2007

 

[FriendlyGardener]

Hi Hui

This looks brilliant!!!! Wow Thanks so much,,, I will try it out and let you know.

To get such a quick response and so useful I am so HAPPY


:--)

 

[FriendlyGardener]

Hi all

Update

Thanks to Hui advice and link and the instructions I can bring in multiple .csv files to my Pivot table... only problem is that there is a blank row between each file (table)...And this is causing problem as I cannot group dates.


My problem now is trying to work out how to get rid of this blank row. There is a statement in SQL something like


SELECT * FROM `march.csv

UNION ALL

SELECT * FROM

april.csv

which is how the query joins the files... any suggestions what to put to add to ignore blank rows?


Thanks

 

[Hui]

FriendlyGardener

Check your data doesn't have a blank row or maybe a <CR> at the end of the last row

It doesn't insert blank rows for me

 

[FriendlyGardener]

thanks Hui

I will have a look at the .csv files tommorrow... obviously there do have blank rows at the end of the data... but I will look out for anything else... Good to hear that you have done this without blank rows...


I will see how i go

 

[FriendlyGardener]

I solved the problem with the empty row by adding


WHERE

column b

IS NOT NULL


to the SQL query in the data connection. Now I can group dates and bring multiple files in... GREAT


Thanks