Correctly parsing a CSV file

I have an application created on an AS400 which generates output as a Comma Delimited CSV file, which is then run through an Excel worksheet which pulls out various elements of the CSV file. The CSV file can contain over 250,000 records which makes it difficult to import the file directly, so I use this function in Excel:

Please Login or Register  to view this content.

The problem is with the line

Please Login or Register  to view this content.

, some of the data elements may contain commas inside strings and this line interprets that as a data element to be split which means that the data is then misplaced.

Is there any VBA command available (or function) which could correctly parse a long string of text from a CSV input and ignore commas inside strings.

To further complicate matters, numeric data which is interspersed within the data entry is simply separated by commas where as String as surrounded by quotation marks.

The only solution I have so far is to manually modify the CSV file using Wordpad. Took me 4 hours to do this so any help appreciated.

Hello Zaphod,

If each line (record) that you read contains the same number of fields, and each record will always have the same field types (string, number,etc.), you could use the Input# statement. This will read each field of the record into an assigned variable. It expects the fields to separated with commas.

Sincerely,
Leith Ross

Put this function in your code:

Please Login or Register  to view this content.

and the change your line:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

You can change the char of 'myChar' but please use a char you think you will never have in your string text else the code will not work.

This code fix only comma in string, for example: "my,text".

I hope it can help you.

Regards,
Antonio

Thanks Antonio

Your code did as expected, thanks but I have found another problem.
(trust users to come up with this one)

How can I deal with a string like this?

"FREIGHT TO STS, NEWPORT, UK "

Notice there are 2 commas in the String, so while your method deals with the first one it breaks when the second one comes along.

I need a method that can ignore ALL commas, no matter how many my users decide to pepper their strings with.

By the way if It helps I don't have a problem with removing the Comma altogether.

I'm sorry... I didn't think to more than one comma.

Please change my previous function with this:

Please Login or Register  to view this content.

Regards,
Antonio

Thanks Antonio


I will download the file again and check if your correction does the trick

Thanks Antonio

Your excellent solution sorted out my problem. Now works as it should

Regards
John

Glad to know it helped you.

Regards,
Antonio