1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Best way of using CSV as a data source in Excel

Discussion in 'Business Applications' started by drewgraham, Dec 5, 2011.

Thread Status:
Not open for further replies.
  1. drewgraham

    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!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029759

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice