make split function ignore commers within quotes

mat41 · July 18th, 2012, 12:08 AM

hi all

I am looking for a way to effectively make the spilt function ignore commers that are within quotes. EG the csv file may look like:

10,some string value,"some, other, string, value",Yes,P99999

when I use the FSO to read the file I am using the split function to get this information into an array, do some things, then insert into DB. but of course "some, other, string, value" as seen above gets split into individual cells when the CSV is opened in Excel. I need this to appear in one cell.

Please note - passing the CSV file directly to the DB is not an option. I am being feed the CSV file so its a major hassle to ask for it to be re-formatted, probably no an option.

TYIA

Imar · July 18th, 2012, 04:21 AM

Hi Matt,

Sounds like regular expressions is the way to go. This article describes a similar problem: http://stackoverflow.com/questions/3...outside-quotes

The code it presents is .NET but reg ex patterns are generally language independent so youy may be able to make this work with classic ASP as well.

Cheers,

Imar

mat41 · July 18th, 2012, 04:54 PM

HI Imar - Thank you for the link. I have asked the organization that produces the CSV file if they can use another character such as the pipe for this scenario.....hopefully they will entertain it although I am unsure.

Otherwise as far as you are aware is the only option to write a custom split function?

Thanking you....

Imar · July 19th, 2012, 02:50 AM

>> Otherwise as far as you are aware is the only option to write a custom split function?

Regular expressions are extremely powerful and would make this relatively issue (once you figured out the reg ex you need). If your data is very predictable you could get those commas out of the way first. For example, you could maybe split on quotes first and then remove / replace the comma from the quoted items. Messy, but it might work.

Another solution is to use quoted identifiers where all your values are always quoted.

I would go for a reg ex if you can't change the source file.

Cheers,

Imar