Update DataTable (from csv import) data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have finally been able to import a csv file into a DataTable. The data has
invalid dates which won't insert into the SQL table I have. So I have been
trying to figure out how to DBNull the invalid dates while still in the
DataTable but I don't understand C# (or .NET) enough to know what or how to
do it. There are two date fields which have '00/00/0000' and may have
'fat-fingered' dates too, so I basically need to loop over the DataTable
row-by-row and perform a check of the data in the two fields (I have an
isDate(object obj) method). If the data is invalid then DBNull the value.
So far I have created a SqlDataAdpater and a DataTable and now I hit the
brick wall as to what to do next. The DataTable has 19 columns and is
usually over 30,000 rows. Any ideas, help, or guidance?
 
You will have to allow the date field to allow null values or set the date
to some constant arbitrary value (i.e. date.minvalue). When setting the
dates in the datatable to null all you need to do is either not set the
value or datarow.setnull("datefieldname") if you are going to allow nulls or
set the value to some arbitrary value datarow("datefieldname") =
date.minvalue. Your data adapter will know what to do if you accomodate as
I have suggested.
 
First - Thanks for the reply. Second - How would I loop through the
DataTable to find the rows with the invalid dates? Would I use foreach or a
for loop (if so how would I implement it on a DataTable - row.count)?
BTW - I am new to C# and .NET so pardon nood questions. I used to be a
ColdFusion developer for 7 years.
--
Regards,

Mike D


AMDRIT said:
You will have to allow the date field to allow null values or set the date
to some constant arbitrary value (i.e. date.minvalue). When setting the
dates in the datatable to null all you need to do is either not set the
value or datarow.setnull("datefieldname") if you are going to allow nulls or
set the value to some arbitrary value datarow("datefieldname") =
date.minvalue. Your data adapter will know what to do if you accomodate as
I have suggested.
Click to expand...
 
I guess my first question is how did you import the CSV file? I assumed you
used some sort of reader and were creating the datarows as you read each
line, that would be the best place to put your date correction code.

In any event, and assuming that your date field in the datatable is defined
as a string and not as a date.

//assuming you have a datatable object named objTable
foreach datarow dr in objTable.rows
{
if (dr("datefieldname")=="00/00/0000")
{
dr.setNull("datefieldname")
//or
dr("dateFieldName") = date.minvalue // "12:00 AM" --FYI
}
}

if the datefield is a date, then "00/00/0000" should have caused an
exception along the way because it is an invalid date, or the value should
already == date.minvalue or isdbnull(dr("datefieldname"))


Mike D said:
First - Thanks for the reply. Second - How would I loop through the
DataTable to find the rows with the invalid dates? Would I use foreach or
a
for loop (if so how would I implement it on a DataTable - row.count)?
BTW - I am new to C# and .NET so pardon nood questions. I used to be a
ColdFusion developer for 7 years.
Click to expand...
 
Thanks - I think that will help a lot.

Here is what I used to import the csv file:
string sSqlSelect = "SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft
Text Driver (*.txt; *.csv)};DBQ=C:\;', 'SELECT * from Order_Table.csv')";
SqlDataAdapter daTmpTable = new SqlDataAdapter(sSqlSelect, SqlConn);
DataTable dtTmpTable = new DataTable();
daTmpTable.Fill(dtTmpTable);

So I add your help:
foreach datarow dr in dtTmpTable.rows
{
if (dr("Create Date")=="00/00/0000")
{
dr.setNull("Create Date")
}
if (dr("Close Date")=="00/00/0000")
{
dr.setNull("Create Date")
}

}

Is it possible to bulkcopy the DataTable to a SQL table, or do I need to
loop over the DataTable and insert each row?
 
AMDRIT said:
You will have to allow the date field to allow null values or set the date
to some constant arbitrary value (i.e. date.minvalue). When setting the
dates in the datatable to null all you need to do is either not set the
value or datarow.setnull("datefieldname") if you are going to allow nulls or
set the value to some arbitrary value datarow("datefieldname") =
date.minvalue. Your data adapter will know what to do if you accomodate as
I have suggested.




Mike D said:
I have finally been able to import a csv file into a DataTable. The data
has
invalid dates which won't insert into the SQL table I have. So I have
been
trying to figure out how to DBNull the invalid dates while still in the
DataTable but I don't understand C# (or .NET) enough to know what or how
to
do it. There are two date fields which have '00/00/0000' and may have
'fat-fingered' dates too, so I basically need to loop over the DataTable
row-by-row and perform a check of the data in the two fields (I have an
isDate(object obj) method). If the data is invalid then DBNull the value.
So far I have created a SqlDataAdpater and a DataTable and now I hit the
brick wall as to what to do next. The DataTable has 19 columns and is
usually over 30,000 rows. Any ideas, help, or guidance?
Click to expand...
Actually Date.MinValue is not a value that SQL Server will accept ...
SQL Server's min date is something like 1st january 1753
Click to expand...
 
I am getting the following error: 'dr' is a 'variable' but is used like a
'method'.
Am I missing something? I know I can't use the Equals method because I need
to identify the column. How do I select the column to compare?
 
Back
Top