Not all CSV column data being read into DataTable

[Guest]

I read the CSV file into a DataTable. This is so I can fix invalid dates
and other data I don't want in the database. Then I use SqlBulkCopy to
insert the data into the SQL database. All the rows are being read and
inserted into the table. The problem is that one of the columns in the CSV
contains the value of 1 thru 6 or C, BUT only the numbers seem to be read
into the DataTable not the letter. I tried single quotes and double quotes
around the character. I even changed the data in another (a number) to a
letter and it did the same thing.

This is the code I use fill the DataTable with the CSV file.

OleDbConnection con = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
";Extended Properties='text;HDR=Yes;Format=Delimited';");
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + sTable +
".csv", con);
DataTable dt = new DataTable("ImportedCSV");
da.Fill(dt);

I then loop over each row to to check the date fields for invalid dates and
other errors prior to performing a SqlBulkCopy this is how I know it is not
reading into the DataTable. I just changed the dt.Rows["colName"] to show
me what is in that column and everytime it should have "C" it has "" whereas
"1" shows "1".

Any ideas? Any Solutions?

 

[rossum]

I read the CSV file into a DataTable. This is so I can fix invalid dates
and other data I don't want in the database. Then I use SqlBulkCopy to
insert the data into the SQL database. All the rows are being read and
inserted into the table. The problem is that one of the columns in the CSV
contains the value of 1 thru 6 or C, BUT only the numbers seem to be read
into the DataTable not the letter. I tried single quotes and double quotes
around the character. I even changed the data in another (a number) to a
letter and it did the same thing.

This is the code I use fill the DataTable with the CSV file.

OleDbConnection con = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
";Extended Properties='text;HDR=Yes;Format=Delimited';");
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + sTable +
".csv", con);
DataTable dt = new DataTable("ImportedCSV");
da.Fill(dt);

I then loop over each row to to check the date fields for invalid dates and
other errors prior to performing a SqlBulkCopy this is how I know it is not
reading into the DataTable. I just changed the dt.Rows["colName"] to show
me what is in that column and everytime it should have "C" it has "" whereas
"1" shows "1".

Any ideas? Any Solutions?

From the symptoms you describe if may be that the datatable does not
like a 'C' in that column - perhaps it is expecting all digits? How
is that column defined in the datatable? What are the valid
characters for the type the column is defined as?

What does the 'C' in that column mean? Is there a different way of
coding for that same value to be input?

rossum

 

[Guest]

Thanks for the reply.
I agree, it does sound as if the DataTable doesn't like "C" (or any letter)
however the DataTable doesn't have any columns datatyped. The values for the
column are set in stone by SAP and I can't change them ('C' stands for
cancelled). In the CSV the columns are not datatyped either since it is a
text file and therefore contains no formatting/datatyping data. I tried
wrapping the letter in both single and double quotes and nothing worked.

--
Regards,

Mike D
Coding in C# since Feb 2007

I read the CSV file into a DataTable. This is so I can fix invalid dates
and other data I don't want in the database. Then I use SqlBulkCopy to
insert the data into the SQL database. All the rows are being read and
inserted into the table. The problem is that one of the columns in the CSV
contains the value of 1 thru 6 or C, BUT only the numbers seem to be read
into the DataTable not the letter. I tried single quotes and double quotes
around the character. I even changed the data in another (a number) to a
letter and it did the same thing.

This is the code I use fill the DataTable with the CSV file.

OleDbConnection con = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
";Extended Properties='text;HDR=Yes;Format=Delimited';");
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + sTable +
".csv", con);
DataTable dt = new DataTable("ImportedCSV");
da.Fill(dt);

I then loop over each row to to check the date fields for invalid dates and
other errors prior to performing a SqlBulkCopy this is how I know it is not
reading into the DataTable. I just changed the dt.Rows["colName"] to show
me what is in that column and everytime it should have "C" it has "" whereas
"1" shows "1".

Any ideas? Any Solutions?

From the symptoms you describe if may be that the datatable does not
like a 'C' in that column - perhaps it is expecting all digits? How
is that column defined in the datatable? What are the valid
characters for the type the column is defined as?

What does the 'C' in that column mean? Is there a different way of
coding for that same value to be input?

rossum

 

[Samuel R. Neff]

I'd highly recommend this CSV reader. Has it's own API and also
implements IDataReader.

http://www.codeproject.com/cs/database/CsvReader.asp

HTH,

Sam

 

[Guest]

Well I figured it out! All I needed was a schema.ini file in the same folder
as the CSV files. The schema.ini file set the datatype of each column to
'text'. Now all the data is imported into the DataTable and eventually into
the SQL server.

Thanks to all who assisted me.