• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Connection - difference between xls and csv connections

P

PGJS

New Member
Hi

My problem is to do with data connections and the difference be .xls and .csv connections.

When I create a .xls connection in a worksheet it creates a Table, however, when I create a .csv connection it DOES NOT.

My understanding is that if I create a pivot-table on the Table created using the xls data connection, then if the Table is refreshed and it contains more data/records. Then when I refresh the pivot-table it reflects the changes (ie, the pivot-table source range adapts to the Table data range)

My problem is that I am using csv data connection which does not create a table so when I create a pivot-table on the csv data worksheet the source range is set. Therefore when the csv data connection is refreshed, the pivot-table source range isn't.

Hope this make sense.

The reason I am using pivot-tables is to summarise the data, is then I can vlookup against the pivot-table itemcount using a concatenated field to bring back the itemcount for Reds, Ambers and Greens against various suppliers.

Can you help or is there a better way?

thanks Paul

ps didn't upload a file because of data connections
 
Try this:
Data tab -> From Other Sources -> From Microsoft Query ->
In the pop-up ensure <New Data Source> is selected and press OK
1. Enter a name for the data source
2. Select from the driver dropdown "Microsoft Text Driver (*.txt, *.csv)"
3. "Connect..." Select the directory where the file is saved
4. Select the csv from the dropdown
Select the newly setup connection and press OK
Move over the desired columns
Press next a few more times and then Return Data to Microsoft Excel

This will bring up the dialog box that allows you to import to a table
And there you have it, a csv in the table format you wanted

I have never used this before so I cannot make a claim on how reliable/stable it is, but it seemed to work when I tried it on some test data.
 
Thanks for your reply.

The first time I tried it I got an error 'too few parameters' but tried again and it worked. I will use this method in future with csv files and see how reliable it is.

Thanks again.

cheers Paul
 
You must log in or register to reply here.
Back
Top