mike99c

asked on 4/10/2008

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

VB ScriptASP

Last Comment

mike99c

8/22/2022 - Mon

hielo

4/10/2008

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

4/10/2008

ASKER

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

mike99c

4/10/2008

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

hielo

4/10/2008

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

4/10/2008

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

4/10/2008

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

mike99c

4/10/2008

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

4/10/2008

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

4/10/2008

ASKER

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

hielo

4/10/2008

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

4/10/2008

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