ODBCDataReader NO_DATA error when reading from CSV file

  • Thread starter Thread starter simon
  • Start date Start date
S

simon

This is a strange one. In ASP.Net v1.1 using VB, I'm using an
ODBCDataReader to read from a CSV file.

Most of the time it works, but if the "description" field contains more
than 2046 chars, I get a NO_DATA error (see below for full error) when
trying to read that row. If this field contains 2046 chars or less,
then everything works fine.

There are about 6800 rows or lines in the CSV file. However, if I
reduce the amount of lines in the CSV to, say 25, then the description
field can contain more than 2046 chars without causing the error.

Here's a sample from my code:

'Save uploaded file to disk.
filCSV.PostedFile.SaveAs(server.MapPath("import/properties.csv"))

'Import data from CSV into MSSQL.
dim strTxtConString as string = "Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & Server.MapPath("import/") & ";"
dim conTxt as ODBCConnection = New ODBCConnection(strTxtConString)
dim cmdTxt as ODBCCommand
dim drTxt as ODBCDataReader

conTxt.Open()
cmdTxt = conTxt.CreateCommand()
cmdTxt.CommandText = "SELECT * FROM [properties.csv]"
cmdTxt.CommandType = CommandType.Text
drTxt = cmdTxt.ExecuteReader()
Do While drTxt.Read()
try
response.write("<br>" & drTxt("Number of characters").ToString() & "
" & drTxt("Description").ToString())
catch
response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")
exit do
finally

end try
Loop
drTxt.Close()
conTxt.Close()


Here's some code to generate the CSV file that causes the error:

dim i as integer
dim strDescription as string
for i = 1 to 2000
strDescription = strDescription & "a"
next

response.write(vbcrlf & """Number of characters"", ""Description""" &
vbcrlf)
for i = 1 to 1000
strDescription = strDescription & "a"
response.write(len(strDescription) & ", """ & strDescription & """" &
vbcrlf)
next


And here's the error message that occurs when the number of characters
in the description field of the CSV reaches 2047 chars:

NO_DATA - no error information available
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: NO_DATA - no error
information available

Source Error:


Line 53: Do While drTxt.Read()
Line 54: 'try
Line 55: response.write("<br>" & drTxt("Number of
characters").ToString() & " " & drTxt("Description").ToString())
Line 56: 'catch
Line 57: response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")

Thanks very much to anyone who can shed any light on this.

Simon
 
I'm surprised that you're able to read fields over 255 characters, let
alone the 2046. It's pretty well known that the text driver has issues
handling long field lengths. If there's a way to fix it, I'd think it'd
be a registry setting somewhere. Overall, I'd generally suggest you not
use this method for reading csv files because it's not only
problematic, obviously, but also very slow.

http://www.csvreader.com/csv_benchmarks.html

This is a strange one. In ASP.Net v1.1 using VB, I'm using an
ODBCDataReader to read from a CSV file.

Most of the time it works, but if the "description" field contains more
than 2046 chars, I get a NO_DATA error (see below for full error) when
trying to read that row. If this field contains 2046 chars or less,
then everything works fine.

There are about 6800 rows or lines in the CSV file. However, if I
reduce the amount of lines in the CSV to, say 25, then the description
field can contain more than 2046 chars without causing the error.

Here's a sample from my code:

'Save uploaded file to disk.
filCSV.PostedFile.SaveAs(server.MapPath("import/properties.csv"))

'Import data from CSV into MSSQL.
dim strTxtConString as string = "Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & Server.MapPath("import/") & ";"
dim conTxt as ODBCConnection = New ODBCConnection(strTxtConString)
dim cmdTxt as ODBCCommand
dim drTxt as ODBCDataReader

conTxt.Open()
cmdTxt = conTxt.CreateCommand()
cmdTxt.CommandText = "SELECT * FROM [properties.csv]"
cmdTxt.CommandType = CommandType.Text
drTxt = cmdTxt.ExecuteReader()
Do While drTxt.Read()
try
response.write("<br>" & drTxt("Number of characters").ToString() & "
" & drTxt("Description").ToString())
catch
response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")
exit do
finally

end try
Loop
drTxt.Close()
conTxt.Close()


Here's some code to generate the CSV file that causes the error:

dim i as integer
dim strDescription as string
for i = 1 to 2000
strDescription = strDescription & "a"
next

response.write(vbcrlf & """Number of characters"", ""Description""" &
vbcrlf)
for i = 1 to 1000
strDescription = strDescription & "a"
response.write(len(strDescription) & ", """ & strDescription & """" &
vbcrlf)
next


And here's the error message that occurs when the number of characters
in the description field of the CSV reaches 2047 chars:

NO_DATA - no error information available
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: NO_DATA - no error
information available

Source Error:


Line 53: Do While drTxt.Read()
Line 54: 'try
Line 55: response.write("<br>" & drTxt("Number of
characters").ToString() & " " & drTxt("Description").ToString())
Line 56: 'catch
Line 57: response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")

Thanks very much to anyone who can shed any light on this.

Simon
Click to expand...
 
You're absolutely right. All the long fields are truncated to 256 chars
without an error, but then I get the NO_DATA error on the fields over
2046 chars - without it even giving me the chance to just read the left
256 chars.

Thanks for the reply - I'm going to try out the CSVReader application
you mentioned.
 
Back
Top