+ Reply to Thread
Results 1 to 13 of 13

importing multiple csv into 1 table

  1. #1
    Registered User
    Join Date
    10-11-2006
    Posts
    51

    importing multiple csv into 1 table

    Hi there

    I have a bunch of csv files, and each file is data from 1 day. The csv files follow this structure:

    # Wed Oct 5 2011 12:00:03 EDT 2011
    # URL:http://abc.com/go_here_for this data/get_data?some_param1=true&Date=02-Sep-2011&some_param2=123
    #header1,header2,header3,
    data1,data2,data3
    data4,data5,data6

    I need to import all this data into 1 table in an xlsx file which looks like this:

    date,header1,header2,header3
    date_csv1,data1,data2,data3
    date_csv1,data4,data5,data6
    date_csv2,data7,data8,data9

    etc.

    Although I've programmed in college, I'm pretty new at programming macros. Could somebody point me to what are some of the functions that would be useful to accomplish this? The pseudo-code I've come up with is:

    csvfiles=list of csv files
    while csvfiles
    open current_csvfile
    data_date=date from current_csvfile.row3
    add data_date to col1 of every row starting row4 to eof
    copy current_csvfile.row4:current_csvfile.eof
    paste current_csvfile.row4:current_csvfile.eof to table.starting.col2
    csvfiles=remove_first_file
    end

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: importing multiple csv into 1 table

    if you want to upload a couple of sample file CSV files and an Excel mockup of the results of those files, can create a macro for you that will import all the CSVs from a specific folder.

    Each time the import occurs, does it ADD to the existing Excel database, or create a new database of all the file(s) imported each time?

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook and CSV files.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-11-2006
    Posts
    51

    Re: importing multiple csv into 1 table

    I've uploaded 3 sample files. Had to rename them to .txt or it wouldn't let me upload, perhaps I'm doing something wrongly. So ...

    marketsummary1.txt and marketsummary2.txt are the .csv files that I'll get from the server. As you can see, the first line is the date where the summary was generated, and the second line has the date of the data embedded in a URL.

    I would like to ADD these summaries into a master xls file like aggregate_marketsummary.txt ... which is created by copying the data rows over and adding a header from 1 of the csv's and a date column (every market summary file has info on the same markets).

    I've tried writing a macro that would find embedded date from the URL and insert it into the first column ... but everything is hard coded and hardly elegant. I'm not sure whats the best way to add the data rows from each marketsummary?.txt to the aggregate file ... Any help would be appreciated.
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: importing multiple csv into 1 table

    ok, so each time it runs we:

    1) open the Aggregate.CSV file found in a specific location
    2) add in the data from all the CSV file found in another specific location
    3) move the added files (the originals) to another location to preclude them being added again in the future.


    Can you provide the "locations" for 1-3 above?

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: importing multiple csv into 1 table

    You could integrate all csv-files before 'importing' into Excel.


    Please Login or Register  to view this content.



  6. #6
    Registered User
    Join Date
    10-11-2006
    Posts
    51

    Re: importing multiple csv into 1 table

    snb/JBeaucaire

    I think snb made an interesting suggestion. It sounds like it would be easier to
    1) "copy *.csv monthly.csv"
    2) Work on the monthly.csv file, which would look like:

    # Wed Oct 05 11:16:58 EDT 2011
    # URL:http://gohere.com/to/get/this/report...celSize=100000
    Pool Name, Quantity, Quantity(%), Average, this, that, everything, else
    Carolinas,49494,100,112,33,4,11,23
    Florida,343431,100,333,111,222,555,331
    Michigan,343411,100,332,514,2156,12,34
    New York,67845,100,7625,23,452,76,4573
    Ohio,3415623,100,3251,4516,8745,75434,22235

    # Mon Oct 03 12:11:23 EDT 2011
    # URL:http://gohere.com/to/get/this/report...celSize=100000
    Pool Name, Quantity, Quantity(%), Average, this, that, everything, else
    Carolinas,49494,100,786,768,8687,786,34
    Florida,343431,100,675,78,222,4564,74
    Michigan,343411,100,78,786,657,5464,43
    New York,67845,100,756,86,828,537,5689
    Ohio,3415623,100,75,67,456,786,389
    Then, I'd have to:

    1) use the rows with " URL" as the beginning of a new "day"
    2) add the date column
    3) delete 4 rows: the 2 rows before " URL", the " URL" row and the row after that.

    What do you think?

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: importing multiple csv into 1 table

    If SNB's macro sends you along a path you can complete, getting what you need/want, then that's great. Let us know.

  8. #8
    Registered User
    Join Date
    10-11-2006
    Posts
    51

    Re: importing multiple csv into 1 table

    It does sound that way.

    Can I get some direction as to what functions, methods etc I might need to:

    1) find the "# URL" row?
    2) use it as a reference point to work on the rows before and after it?

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: importing multiple csv into 1 table

    This uses SNB's approach to merge all the CSVs in the fPath directory into a single CSV, then reformat the resulting CSV the way you wanted.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    10-11-2006
    Posts
    51

    Re: importing multiple csv into 1 table

    Cool, thanks for all the help. Let me try to understand the code ...

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: importing multiple csv into 1 table

    Please Login or Register  to view this content.

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: importing multiple csv into 1 table

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  13. #13
    Registered User
    Join Date
    10-11-2006
    Posts
    51

    Re: importing multiple csv into 1 table

    Sorry, was hijacked to do another project so I didn't have time to work on this side project of mine.

    JBeaucaire, I think I must be doing something wrong here. I tried the code and it didn't work as intended. Can we start again ...

    Input file: marketsummary_all.txt (a csv file)
    Output file: aggregate_marketsummary.txt

    When I tried to run your code on marketsummary_all.txt (after commenting out the joining/importing bits), it only read the first date and put it in every row of col A. Any ideas?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1