troubleshooting Question

Help reading CSV file line in VBScript with non separator commas

Avatar of mike99c
mike99c asked on
ASPVB Script
13 Comments1 Solution2637 ViewsLast Modified:
I have written some VBScript code to read a CSV (comma separated) file. The problem is the CSV file contains text fields which may contain commas that are non separators. These would be surrounded by double quotes (").

My attached code uses
CSVArray = Split(TextLine,",")
to split each line by commas but I need to modify this so it takes into account fields surrounded by double quotes which contain commas.

Any help would be appreciated.
The CSV File
---------------------------------------
AB11011469,"This is some free text, as you see it has some commas, thanks",ZJ477190386GB
AC18515742,This is some other free text with no commas,ZJ477191449GB
ac4894221,"Some more, and commas and ""quotes"" as well",ZJ477191302GB
 
The Code
----------------------------------------
Dim CSVFile, fs, TextLine, CSVArray, i
 
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set CSVFile = fs.OpenTextFile(Server.MapPath("datafile.csv"),1, true)
 
While NOT CSVFile.AtEndOfStream 
   TextLine = CSVFile.ReadLine()
   IF TextLine <> "" THEN
      CSVArray = Split(TextLine,",")
	  For i = 0 TO UBound(CSVArray)
	     response.write "Column [" & i & "]: " & CSVArray(i) & "<br />"
	  Next
	  response.write "---------------------------------------------------------<br />"
	  
   END IF
Wend
CSVFile.Close
set CSVFile = nothing
set fs=nothing
 
The actual output
----------------------------------------------------------
Column [0]: AB11011469
Column [1]: "This is some free text
Column [2]: as you see it has some commas
Column [3]: thanks"
Column [4]: ZJ477190386GB
---------------------------------------------------------
Column [0]: AC18515742
Column [1]: This is some other free text with no commas
Column [2]: ZJ477191449GB
---------------------------------------------------------
Column [0]: ac4894221
Column [1]: "Some more
Column [2]: and commas and ""quotes"" as well"
Column [3]: ZJ477191302GB
---------------------------------------------------------
 
The desired output
------------------------------
Column [0]: AB11011469
Column [1]: "This is some free text, as you see it has some commas, thanks"
Column [2]: ZJ477190386GB
---------------------------------------------------------
Column [0]: AC18515742
Column [1]: This is some other free text with no commas
Column [2]: ZJ477191449GB
---------------------------------------------------------
Column [0]: ac4894221
Column [1]: "Some more, and commas and ""quotes"" as well"
Column [2]: ZJ477191302GB
---------------------------------------------------------
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros