Mike D
7/3/2007 3:40:00 PM
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@discussions.microsoft.com> wrote in message
> news:EF67840C-535D-4719-84D0-FF50D5EC004E@microsoft.com...
> > 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@discussions.microsoft.com> wrote in message
> >> news:12719B89-307C-4FC7-8330-AF77BDF0231D@microsoft.com...
> >> >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
> >>
> >>
> >>
>
>
>