asked on

Help reading CSV file line in VBScript with non separator commas

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
---------------------------------------------------------

Open in new window

hielo

Try this:

Dim CSVFile, fs, TextLine, CSVArray, i
 
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set CSVFile = fs.OpenTextFile(Server.MapPath("datafile.csv"),1, true)
 
   	Dim comma
	comma = chr(34) & "," & chr(34) 
While NOT CSVFile.AtEndOfStream 
   TextLine = CSVFile.ReadLine()
   IF TextLine <> "" THEN
   	 TextLine = Replace(TextLine,comma,"^^^")
      CSVArray = Split(TextLine,",")
        For i = 0 TO UBound(CSVArray)
           response.write "Column [" & i & "]: " & Replace(CSVArray(i), "^^^",comma) & ""
        Next
        response.write "---------------------------------------------------------"
        
   END IF
Wend
CSVFile.Close
set CSVFile = nothing
set fs=nothing

Open in new window

mike99c

ASKER

Hi Hielo,
I tried the solution and it made no difference at all to the output. Look at your solution
Thanks

mike99c

ASKER

Sorry, the desired output should be as attached. The double quotes are not meant to be displayed as they are meant to enclose the data.

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
---------------------------------------------------------

Open in new window

hielo

>>The double quotes are not meant to be displayed as they are meant to enclose the data.
Details matter! You left that part out!

Dim CSVFile, fs, TextLine, CSVArray, i
 
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set CSVFile = fs.OpenTextFile(Server.MapPath("datafile.csv"),1, true)
 
   	Dim comma
	comma = chr(34) & "," & chr(34) 
While NOT CSVFile.AtEndOfStream 
   TextLine = CSVFile.ReadLine()
   IF TextLine <> "" THEN
   	 TextLine = Replace(TextLine,comma,"^^^")
      CSVArray = Split(TextLine,",")
        For i = 0 TO UBound(CSVArray)
           response.write "Column [" & i & "]: " & Replace(CSVArray(i), "^^^",",") & ""
        Next
        response.write "---------------------------------------------------------"
        
   END IF
Wend
CSVFile.Close
set CSVFile = nothing
set fs=nothing

Open in new window

mike99c

ASKER

Hi Hielo,
I still don't see any change in the output. Please see attached.
Thanks

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
---------------------------------------------------------

Open in new window

hielo

Save this as hielo.asp and try it.

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,",")
	  response.write "Column [" & 0 & "]: " & CSVArray(i) & "<br />"
	  response.write "Column [" & 1 & "]: " 
	  Dim output
	  output=""
	  For i = 1 TO UBound(CSVArray)-1
	    output = output & "," & CSVArray(i)
	  Next
	  response.write  Mid(output,2,Len(output)) & "<br />"
	  response.write "Column [" & UBound(CSVArray) & "]: " & CSVArray( UBound(CSVArray) ) & "<br />"
	  response.write "---------------------------------------------------------<br />"
	  
   END IF
Wend
CSVFile.Close
set CSVFile = nothing

Open in new window

mike99c

ASKER

Fantastic, it is almost there. I had to change line 10 to:

response.write "Column [" & 0 & "]: " & CSVArray(0) & "<br />"

The output is shown in the snippet, I just need the quotes removed. The column indices are not sequential but that's not a major problem.

Column [0]: AB11011469
Column [1]: "This is some free text, as you see it has some commas, 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, and commas and ""quotes"" as well"
Column [3]: ZJ477191302GB
---------------------------------------------------------

Open in new window

hielo

>>, I just need the quotes removed
From everywhere or just within? Meaning, do you want the quotes on the ends removed as well? or do you just want the quotes on the end removed

>>The column indices are not sequential but that's not a major problem.
instead of response.write "Column [" & UBound(CSVArray) & "]: "...
use
response.write "Column [3]: "...

mike99c

ASKER

I just want the quotes on the ends removed. Anything within I will just replace "" with ".
Thanks

hielo

Try this

Dim CSVFile, fs, TextLine, CSVArray, i
 
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set CSVFile = fs.OpenTextFile(Server.MapPath("datafile.csv"),1, true)
Dim quotes
quotes=chr(34) & chr(34)
 
While NOT CSVFile.AtEndOfStream 
   TextLine = CSVFile.ReadLine()
   IF TextLine <> "" THEN
      CSVArray = Split(TextLine,",")
	  response.write "Column [" & 0 & "]: " & CSVArray(i) & "<br />"
	  response.write "Column [" & 1 & "]: " 
	  Dim output
	  output=""
	  For i = 1 TO UBound(CSVArray)-1
	    output = output & "," & CSVArray(i)
	  Next
	  output = Mid(output,2,Len(output))
	  output = Replace(output,quotes,"^^^")
	  output = Replace(output,chr(34), "")
	  output = Replace(output,"^^^",chr(34))
	  response.write   & "<br />"
	  response.write "Column [3]: " & CSVArray( UBound(CSVArray) ) & "<br />"
	  response.write "---------------------------------------------------------<br />"
	  
   END IF
Wend
CSVFile.Close
set CSVFile = nothing

Open in new window

mike99c

ASKER

It's missing the text now. I have attached the output.

Note I had to correct some small bugs and have attached the corrected code again.

Column [0]: AB11011469
Column [1]: 
Column [3]: ZJ477190386GB
---------------------------------------------------------
Column [0]: AC18515742
Column [1]: 
Column [3]: ZJ477191449GB
---------------------------------------------------------
Column [0]: ac4894221
Column [1]: 
Column [3]: ZJ477191302GB
---------------------------------------------------------
 
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,",")
	  response.write "Column [" & 0 & "]: " & CSVArray(0) & "<br />"
	  response.write "Column [" & 1 & "]: " 
	  Dim output
	  output=""
	  For i = 1 TO UBound(CSVArray)-1
	    output = output & "," & CSVArray(i)
	  Next
	  output = Mid(output,2,Len(output))
	  output = Replace(output,quotes,"^^^")
	  output = Replace(output,chr(34), "")
	  output = Replace(output,"^^^",chr(34))
	  response.write "<br />"
	  response.write "Column [3]: " & CSVArray( UBound(CSVArray) ) & "<br />"
	  response.write "---------------------------------------------------------<br />"
	  
   END IF
Wend
CSVFile.Close
set CSVFile = nothing

Open in new window

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mike99c

ASKER

Great thanks a lot for your help.