Re: Update DataTable (from csv import) data
- From: Mike D <MikeD@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 3 Jul 2007 08:40:03 -0700
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?
--
Regards,
Mike D
"AMDRIT" wrote:
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" <MikeD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EF67840C-535D-4719-84D0-FF50D5EC004E@xxxxxxxxxxxxxxxx
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" wrote:
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" <MikeD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:12719B89-307C-4FC7-8330-AF77BDF0231D@xxxxxxxxxxxxxxxx
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?
--
Regards,
Mike D
- References:
- Re: Update DataTable (from csv import) data
- From: AMDRIT
- Re: Update DataTable (from csv import) data
- From: AMDRIT
- Re: Update DataTable (from csv import) data
- Prev by Date: Re: Update DataTable (from csv import) data
- Next by Date: Re: @@identity
- Previous by thread: Re: Update DataTable (from csv import) data
- Next by thread: Re: Update DataTable (from csv import) data
- Index(es):
Relevant Pages
|
