Blank Rows Read from CSV File when reading with ADO.NET and ASP.NET

  • Thread starter Thread starter mj
  • Start date Start date
M

mj

Hi All,

I am uploading and reading CSV files in my web application. But it is
behaving weired. Sometimes it reads values and sometimes it just
rejects them.

My Connection Code is:

System.Data.OleDb.OleDbConnection myConnection = new
System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="+System.Web.HttpContext.Current.Request.MapPath("upload/")+";"+
"Extended Properties=\"text;\"");

System.Data.OleDb.OleDbDataAdapter myAdapter = new
System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " +
Session["FileName"] + ".csv", myConnection);
DataSet mySet = new DataSet(Session["FileName"] + ".csv");
myAdapter.Fill(mySet, Session["FileName"] + ".csv");
DataTable myTable = mySet.Tables[Session["FileName"] + ".csv"];

foreach (DataRow myRow in myTable.Rows)
{
SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["sqlConnString"],
"UpdateWithDBByIDAndOrder",
Convert.ToString(myRow[0]), Session["FileName"], myRow[1].ToString());
}
uploadmessage.Text = "File Uploaded Successfully";


The File format is:
ID,Order
9806830X,NEX
2828255U,51
2828250X,80
2828259B,73
2828258P,56
3000585,56
3182875,76
2487424E,70

Sometimes - it reads the file with all Alpha ID, Sometimes it reads
the file with all Numeric (3...) ID's, Sometimes it reads all and
sometimes it doesn't read anything. I have tried checking the value
read and that is NULL.

Can anyone Please help me with this. Anyhelp would be good.

Thanks,
Mj
 
On 11 Nov 2003 18:38:37 -0800, (e-mail address removed) (mj) wrote:

¤ Hi All,
¤
¤ I am uploading and reading CSV files in my web application. But it is
¤ behaving weired. Sometimes it reads values and sometimes it just
¤ rejects them.
¤
¤ My Connection Code is:
¤
¤ System.Data.OleDb.OleDbConnection myConnection = new
¤ System.Data.OleDb.OleDbConnection(
¤ "Provider=Microsoft.Jet.OLEDB.4.0;Data
¤ Source="+System.Web.HttpContext.Current.Request.MapPath("upload/")+";"+
¤ "Extended Properties=\"text;\"");
¤
¤ System.Data.OleDb.OleDbDataAdapter myAdapter = new
¤ System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " +
¤ Session["FileName"] + ".csv", myConnection);
¤ DataSet mySet = new DataSet(Session["FileName"] + ".csv");
¤ myAdapter.Fill(mySet, Session["FileName"] + ".csv");
¤ DataTable myTable = mySet.Tables[Session["FileName"] + ".csv"];
¤
¤ foreach (DataRow myRow in myTable.Rows)
¤ {
¤ SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["sqlConnString"],
¤ "UpdateWithDBByIDAndOrder",
¤ Convert.ToString(myRow[0]), Session["FileName"], myRow[1].ToString());
¤ }
¤ uploadmessage.Text = "File Uploaded Successfully";
¤
¤
¤ The File format is:
¤ ID,Order
¤ 9806830X,NEX
¤ 2828255U,51
¤ 2828250X,80
¤ 2828259B,73
¤ 2828258P,56
¤ 3000585,56
¤ 3182875,76
¤ 2487424E,70
¤
¤ Sometimes - it reads the file with all Alpha ID, Sometimes it reads
¤ the file with all Numeric (3...) ID's, Sometimes it reads all and
¤ sometimes it doesn't read anything. I have tried checking the value
¤ read and that is NULL.
¤
¤ Can anyone Please help me with this. Anyhelp would be good.

This is a problem when using fields that are mixed mode (numeric and alphanumeric). What you need to
do is create a schema.ini file, containing the MaxScanRows entry, and place it in the same folder as
the CSV file.

[FileName.csv]
ColNameHeader=False
MaxScanRows=0
Format=CSVDelimited

See the following for more info on schema.ini files:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp

You should also be able to change the value of MaxScanRows to zero in the following registry entry
if you don't want to use a schema.ini file:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top