reading a CSV file with asp getting null values

Question

I am using classic asp to import data into a MySQL database, however I'm running into a bit of trouble, even though the fields exist in the CSV file my code returns the values as NULL

As I want to open the CSV file as a recordset I first have to grab the remote csv file, then save a copy locally and create a schema.ini file, then open the file using an ADODB connection, then I want to move through the records grabbing the information and manipulating it before writing it into my database.

So I first grab the file using MSXML2.ServerXMLHTTP like so

sImportURL = "http://somewebsite/somecsvfile.csv"
Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objXMLHTTP.Open "GET", sImportURL, False
objXMLHTTP.setRequestHeader "User-Agent", Request.ServerVariables("HTTP_HOST")
objXMLHTTP.Send ""
sResponse = split(objXMLHTTP.responseText, vbcrlf)
Set objXMLHTTP = Nothing

sResponse is an array that has been set up, each line of the file is then added into the array using split. So far so good!

Now I use FSO to loop through the array and create a CSV file locally and also create a schema file for it

Set fsoImport = CreateObject("Scripting.FileSystemObject")
sFolder = Server.MapPath("/TempImport/")
If Not fsoImport.FolderExists(sFolder) Then sFSO.CreateFolder(sFolder) 
sFileName = Server.MapPath("/TempImport/Import.csv")
If fsoImport.FileExists(sFileName) Then fsoImport.DeleteFile(sFileName)
Set sImportFile = fsoImport.CreateTextFile(sFileName, ForWriting, True)
For i = 0 to uBound(sResponse)
    sImportFile.Writeline sResponse(i)
Next
sImportFile.Close
Set sImportFile = Nothing

sFileName = Server.MapPath("/TempImport/schema.ini")
If fsoImport.FileExists(sFileName) Then fsoImport.DeleteFile(sFileName)
Set sSchemaFile = fsoImport.CreateTextFile(sFileName, false)
sSchemaFile.WriteLine("[Import.csv]")
sSchemaFile.WriteLine("ColNameHeader=True")
sSchemaFile.WriteLine("Format=CSVDelimited")
sSchemaFile.WriteLine("Col1=SKU Text")
sSchemaFile.WriteLine("Col2=Quantity Double")

This also works successfully and the csv file looks like this

SKU,Quantity
"TEK",0
"KDL-60LX903",3
"BICBIRO",88
"WB001",10

Now I open the file I have just created using an ADODB connection like so

Set adoOpen = Server.CreateObject("ADODB.Connection")
adoOpen.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=/TempImport/;Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
adoOpen.Open
sSQL = "SELECT * FROM `Import.csv`"
Set rsOpenRS = Server.CreateObject("ADODB.Recordset")
rsOpenRS.Open sSQL, adoOpen
Do While Not rsOpenRS.EOF
    sField = "SKU"
    sValue = rsOpenRS(sField)
    Response.Write sField & " = " & sValue & "<br />"
    sField = "Quantity"
    sValue = rsOpenRS(sField)
    Response.Write sField & " = " & sValue & "<br />"
    rsOpenRS.MoveNext
Loop
rsOpenRS.Close
Set rsOpenRS = nothing
Set adoOpen = nothing

Now even though my CSV file has data in it all that gets written to the screen is

SKU =
Quantity = 
SKU = 
Quantity = 
SKU = 
Quantity =
SKU = 
Quantity =
SKU = 
Quantity =
SKU = 
Quantity =
SKU = 
Quantity =
SKU = 
Quantity =
SKU = 
Quantity =
SKU = 
Quantity =

Notice that there are 10 sets of results and only 5 lines in the CSV file including the header, I'm not really sure what is happening, does anybody have any clue?

Cheers

You have `sSchemaFile.WriteLine("ColNameHeader=True")` and then in your connection string, `HDR=NO`. Does your csv file have column headers ? — Flakes, Apr 11 '15 at 14:35

score 0 · Accepted Answer · answered Apr 14 '15 at 09:38

OK, so this was just a dumb problem, I changed

Set sImportFile = fsoImport.CreateTextFile(sFileName, ForWriting, True)

To

Set sImportFile = fsoImport.CreateTextFile(sFileName, True)

As I was writing this in classic asp I didn't need the ForWriting bit, it wasn't giving me an error it just wasn't working.

Thanks to everybody that tried to help though

reading a CSV file with asp getting null values

1 Answers1