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[i]["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?
--
Regards,
Mike D
Coding in C# since Feb 2007