Best way of using CSV as a data source in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

drewgraham

Thread Starter
Joined
Jul 20, 2005
Messages
199
I have a csv data file that I've downloaded which I want to do some reporting on, eventually turning it into a pivot report.

However, this source csv will change weekly when I download it (mostly appended, but possible data changes anywhere). When I have worked with a dynamic data source in the past, I've created a link to it through Excel's Get External Data options. If I link it to this CSV it just imports the data.

I would like to design an elegant solution where the source csv can be downloaded, then the reporting Excel file opened and linked to this newly downloaded csv, with the changes in data reflected in the report.

If I were doing it manually, I would copy the entire csv into tab1, format it as a table, change the table ID to something unique and then link the pivot reports to it, but this is an inelegant solution.

The fields in the CSV will remain constant, but the number of rows will increase each time. I'm using Excel 2010.

Thanks!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Hi Drew,
You can still do this, all you need to do is create a macro that reads the csv file into an existing sheet already in your reporting Excel file
You will have to agree for a file location where the csv file can be found and also a naming convention, if the fileds are always the same then all you need is macro that erases the data in the Excel sheet, not the sheet, and reads the new data into this same sheet, your macro collecting the data must just specify what the last row is and your report can be refreshed.
Import thing to keep in mind:
csv file location must be specified
csv filename must answer to specific conditions (naming convention)
All these things can me made dynamic, never hard code this so that your macro will become dynamic and little or no changes have to be coded before running it.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top