Processing CSV Files in Excel VBA

Folks
I receive a CSV that has 15 columns and up to 10,000 rows. Due to the potential size of the data, in my VBA, I want to process row by row as not every row is required. I created some code that reads a row of the file and uses the SPLIT command to break the data into the various columns. The problem is, one of the columns is a string that occasionally has a comma in it. When the string has a comma, the SPLIT command then breaks the string into 2 different fields and changes the location of where I expect data to be.

Can someone please point me in the right direction on the easiest way to code around this?
Thanks,
Michael

Sure Alan. I have also attached the data file I am testing with. As you can see when you run the code, the headings match what is expected. Rows 2 and 3 are offset so date for instance is no longer a date and then the remaining rows align correctly. The problem is due to the comma after "Decision" on 2 of the rows.

Please Login or Register  to view this content.

[data]
Approver Id,Emp ID,Project Name,Task Name,SubTask Name,Start Time,End Time,Date,Location,Status,Group Name,Client Name,Department,Time (hrs),Prorata Time
,[email protected],Dummy for Tool Development - Zero,Business Analysis,"""Decision, Analysis and Resolution (DAR) preparation""",0,0,4-Jan-16,N/A,Not Approved,AUS,Program Name,1,5,5
,[email protected],Dummy for Tool Development - Zero,Business Analysis,"""Decision, Analysis and Resolution (DAR) preparation""",0,0,5-Jan-16,N/A,Not Approved,AUS,Program Name,1,6,6
,[email protected],Dummy for Tool Development - Zero,Project Management,Finance,0,0,11-Jan-16,N/A,Not Approved,AUS,Program Name,1,3,3
,[email protected],Dummy for Tool Development - Zero,Project Management,Finance,0,0,13-Jan-16,N/A,Not Approved,AUS,Program Name,1,5,5
[/data]

Finally, I have an answer to this problem. By using a Regular Expression, you can replace the separators. Hopefully this helps out someone else.

Please Login or Register  to view this content.