Regex pattern to match carriage returns inside quotation marks?

miikee55 · September 19th, 2009, 07:37 AM

Hello all,
Anyone here who can help me or point me in the right direction?

I have a CSV file like below...

ID,Name,Comment,Date
5,"Steve","Likes football and pizza",20090909
6,"Lisa","Likes to cook",20090905
7,"Nate","Reads a lot of books
Likes to run marathons",20090908

...and the carriage return in Nates comment creates problems for me, so I need to replace it with something else. A simple Replace will not work since it will turn the entire file into one line. I believe I need to use a pattern here - but I don't know how it should look.

This is what I'm trying to do, and "the_pattern" is where I fail (strText contains the entire CSV text):

Dim RE
Set RE = new RegExp
RE.IgnoreCase = True
RE.Global = True
re.Pattern = "the_pattern"
strNewText = re.replace(strText, "NEWLINE")

I guess the regex pattern should match something like "carriage returns inside quotation marks", so I can use it to replace those carriage returns with something else (to later on convert them back to carriage returns).

Any help or pointers on this is highly appreciated. I've tried on my own for a while, but cannot seem to do it right.

/Miikee

om_prakash · September 19th, 2009, 01:12 PM

Hi,

Please check the sample code which will work even the data is in multiple lines in CSV file

Code:

CONN_STRING = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=F:\FOLDER_PATH\;Extensions=csv"
Set myConn = Server.CreateObject("ADODB.Connection")
Set myRs = Server.CreateObject("ADODB.Recordset")
myConn.open CONN_STRING
myRs.open "select * from [FILENAME.csv]",myConn
while not myRs.eof
 response.write myRs(0) & " :::: " & myRs(1) & "<br>"
 myRs.movenext
wend
myRs.close
myConn.close

Old Pedant · September 19th, 2009, 03:02 PM

Excellent answer.

Miikee: Make careful note of the PATH used in the connection string. It must be the path to the *DIRECTORY* containing the CSV file, *NOT* the path to the file itself.

Then you specify the actual file in the SQL query. Because of the period on the file name, you must wrap the file name with [ ... ]

Om_Prakash coded that exactly correctly. I'm just writing this to make sure you understand the important points.

You might also want to look here:
http://www.carlprothman.net/Default....orMicrosoftJet
scroll down to " You can also open a Text file ..."
Read that and also the link to the MS knowledge base

miikee55 · September 21st, 2009, 04:16 AM

A big thank you to you Om_Prakash - that is indeed a much more elegant way to parse a CSV file than how I use to do it - and that it takes care of my multiple line problem as well is wonderful!

Also - thank you very much to Old Pedant for providing your links and making sure I understood the example right!

You've made my day - not only for helping me with this problem but also because it is great to see people taking their time to simply help someone else.

Thanks!

/Miikee

miikee55 · September 21st, 2009, 06:04 AM

Just in case anyone else get the same issue:

In my question example above I have headers in the CSV file - but now it turns out my CSV file actually wont.

This means that the data in the first line will not be returned as it will be treated as a line of headers. The only way I manage to include the first line is to change the connection string in om_prakash excellent example to the following:

CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\FOLDER_PATH\;Extended Properties='text;HDR=NO;FMT=Delimited'"

By doing so, the data in the first line is returned as well.

/Miikee