How to import data from a messed up CSV using VBA

Question

This is sort of a "which direction is best" sort of question. I have a .CSV file that is in a less then ideal format. I'll diagram the file below so you can see what I'm talking about. This file is provided to me by a vendor who should know better... (Google, cough, hack, cough).

What is the best way to import only table2 from this data using VBA? I'll list methods I've been trying after the diagram.

.CSV file looks like this if opened:

 Title Cell
 File Info Cell
 Time Date Cell

 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 Data1, Data 2
 ....(continues for around 800 rows)

 Second Table Title Cell
 Col1Title, Col2Title, ColTitle, Col4Title ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)    
 Data 3,    Data4,     Data 5,   Data6 ....(continues for around 50 columns)
 ...(Continues for around 1500 rows)
 End of CSV

I've thus far attempted to the ADODB object but that relies on SQL query's (which to my limited SQL knowledge) assume that the data is formated correctly as a table - which it is not here.

I've also been trying reading the file line by line.

I could force either of these two solutions to work, but it's pretty messy both ways. I feel like there has to be clean way to do this?

Can anyone suggest a better approach or an efficient approach to the methods I've already tried?

Thanks in advance.

addendum

@ user3724 this is what i've been trying on the line by line approach:

Open strFile For Input As #1

  countLine = 0

  Do Until EOF(1)
     Line Input #1, LineFromFile
     (Increment countLine)
     (Break line into array)
     (strComp() each element with the value for title of Table 2)
     (When strComp() returns true return countLine as upperValue)
     (Exit Loop)
 Loop

 countLine = 0

 Do Until EOF(1)
     Line Input #1, LineFromFile
     (Increment countLine)
     If countline is >= uppervalue
         (Parse line and return it to target row of target worksheet)
     End If
 Loop

When I code this whole thing out it is a slow mess - lots of if statements and loops within loops. @user3724, could you review this based on your experience?

I made some edits. I want to import only Table2 from this data into an excel spreadsheet - using VBA of course. — Jamie Marshall
– Jamie Marshall, Commented Jan 28, 2016 at 18:15

ptrvoid · Accepted Answer · 2016-01-28 19:41:09Z

1

I have no reputation to comment...But I may add answer. If you wanna use vba then open file and read line by line (for example using line input) omit lines until you detect second empty string, then parse headers (next two lines) and store column headers in separate array (use redim preserve or predefined array size it depends on file format). The rest of "second" table just read line by line and parse each line to array (i recomend dynamic array with first dimension relying on count of headers) the second dim increase with each parsed line. After all you will recieve two arrays one with field/column captions/names and the second with data. both arrays have same first dimensions. The second stage upload data via ado using arrays. I used the method for import data to db via ado from csv files with weird structure just like yours

answered Jan 28, 2016 at 19:41

ptrvoid

763 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jamie Marshall Over a year ago

Thanks for the response. It seems you like you have a good solution but your answer is a little dense (or i'm a little dense....). Could you provide a quick example of some sort? I'll include what I'm currently experimenting w/ in an addendum to the question.

ptrvoid Over a year ago

Here: yadi.sk/d/ocV-wDNonvnTG There are two files on archive. First is *.xlsm and the second *.csv check it and modify for yourself

Jamie Marshall Over a year ago

Thanks for the download, very enlightening. Is there a reason to use ADO with this method? If i have the lines I want already parsed into memory why just write them to my excel worksheet? Or is there something i'm missing?

ptrvoid Over a year ago

No matter, probabbly I misundertood you... the goal is to parse data into memory... If you have to fill excel worksheet with imported data just write data down... of course there's no need to use ADO... Sorry for confusing you.

Jamie Marshall Over a year ago

After much testing I've found this to be the best method. I added a comparison function for comparing column names when I append the CSV to previously imported data. Other than that this is a great answer -many thanks.

Collectives™ on Stack Overflow

How to import data from a messed up CSV using VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related