Classic ASP Reading CSV File Using ADO Does Not Recognize Text Data in Column when Number Data is Present in Other Rows
I have a file that is sent to my company, which I will have no power over manipulating. What we do is upload that file, then grab the data and do some back end computing. All works well except for one scenerio.
When I have a column that has the first line as text data, then a mix of text and number data after it on the remaining rows, it does not recognize the text data, but it does the number data. Here is the code to better illustrate what is happening:
strConn = "Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Dbq=d:\data\forecast\; HDR=Yes; Extensions=csv,txt; Persist Security Info=False"
Set dbc = Server.CreateObject("ADODB
dbc.open strConn
set rs = Server.CreateObject ("adodb.RecordSet")
ssql = "SELECT * FROM [forecast.csv]"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly
do while not rs.eof
response.write(rs(1)&"<br>
rs.movenext
loop
The attached file is an example set of data that will be uploaded.
If opening in excel, then it is column B, or else it is the column with header "Item" I am working with.
The output of the attached file, when printed to the screen, is this:
22137848
22299234
23451842
23522238
23522246
23522253
23788532
38433546
39237078
39560628
39921705
85652535
Notice the blank data present where there should be data. So, any field that is text for column B comes across as NULL.
What is wierd, however, is when I do the following code:
set fs=Server.CreateObject("Sc
set t=fs.OpenTextFile("d:\data
x=t.ReadAll
t.close
Response.Write("The text in the file is: " & x)
it reads all the data as it should, but I don't really know how to work with that, and I'd rather go the approach I already have it at all possible (using ado).
Any help is appreciated.
FCS-115-47364254.csv
When I have a column that has the first line as text data, then a mix of text and number data after it on the remaining rows, it does not recognize the text data, but it does the number data. Here is the code to better illustrate what is happening:
strConn = "Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Dbq=d:\data\forecast\; HDR=Yes; Extensions=csv,txt; Persist Security Info=False"
Set dbc = Server.CreateObject("ADODB
dbc.open strConn
set rs = Server.CreateObject ("adodb.RecordSet")
ssql = "SELECT * FROM [forecast.csv]"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly
do while not rs.eof
response.write(rs(1)&"<br>
rs.movenext
loop
The attached file is an example set of data that will be uploaded.
If opening in excel, then it is column B, or else it is the column with header "Item" I am working with.
The output of the attached file, when printed to the screen, is this:
22137848
22299234
23451842
23522238
23522246
23522253
23788532
38433546
39237078
39560628
39921705
85652535
Notice the blank data present where there should be data. So, any field that is text for column B comes across as NULL.
What is wierd, however, is when I do the following code:
set fs=Server.CreateObject("Sc
set t=fs.OpenTextFile("d:\data
x=t.ReadAll
t.close
Response.Write("The text in the file is: " & x)
it reads all the data as it should, but I don't really know how to work with that, and I'd rather go the approach I already have it at all possible (using ado).
Any help is appreciated.
FCS-115-47364254.csv
apologies.... your sample file definitely has a header. i was reading the problem wrongly.
I suspect you will need to use a Schema file.... http://msdn.microsoft.com/en-us/library/ms709353.aspx to force the field to be text.
one alternate may be to try with HDR=No and then drop the first row. Maybe it will force all fields to be text but that may not be what you want.
I suspect you will need to use a Schema file.... http://msdn.microsoft.com/en-us/library/ms709353.aspx to force the field to be text.
one alternate may be to try with HDR=No and then drop the first row. Maybe it will force all fields to be text but that may not be what you want.
ASKER
I have tried the schema.ini route, as adding a blank row causes other issues with the code down the rest of the page.
Here is what I have inside my Schema.ini file:
[IRCheck.csv]
Format=CSVDeliminated
ColNameHeader=True
MaxScanRows=0
I save the file in the same directory as the actual .csv file, but from here, I'm at a loss as what to do.
Here is what I have inside my Schema.ini file:
[IRCheck.csv]
Format=CSVDeliminated
ColNameHeader=True
MaxScanRows=0
I save the file in the same directory as the actual .csv file, but from here, I'm at a loss as what to do.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the help! I tweaked your info just a tad to create the following:
[IRCzech.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Col1="Supplier Site" Char
Col2="Item" Char
Col3="Description" Char
Col4="Primary UOM" Char
I had to change the Text to Char, but the big problem was I had two typos, the file name and the Format were typed wrong.
After that, I ran into issues with the date columns, but since those are variable, I can't create a schema file for them. I instead am just having the file be saved twice in teh same folder, one with -noSchema on the filename, and I use that in my code for the column headers, and use the schema'd file for the data that is forced to text. What a mess, thanks a ton, working like a charm now!
[IRCzech.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Col1="Supplier Site" Char
Col2="Item" Char
Col3="Description" Char
Col4="Primary UOM" Char
I had to change the Text to Char, but the big problem was I had two typos, the file name and the Format were typed wrong.
After that, I ran into issues with the date columns, but since those are variable, I can't create a schema file for them. I instead am just having the file be saved twice in teh same folder, one with -noSchema on the filename, and I use that in my code for the column headers, and use the schema'd file for the data that is forced to text. What a mess, thanks a ton, working like a charm now!
strConn = "Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Dbq=d:\data\forecast\; HDR=Yes; Extensions=csv,txt; Persist Security Info=False"
Notice the HDR=Yes. that is telling the CSV ado to expect a header row that has column names.
but it appears your file does not have a header.
So set HDR=No and check the result.