Loop over DataTable to edit 'bad' columns

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

Guest

I have a DataTable (over 32,000 rows) with 19 columns of which I need to fix
some data in two of them in order to import the data into a SQL table. I
have a for loop but I can't figure out the if statement to find the 'bad'
columns so I edit them. This is what I have but it isn't working.

for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
if (dtTmpTable.Rows["Create Date"] == "00/00/0000") <-- not working
{
dtTmpTable.Rows["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();
 
The data comes from a SAP output file (which I have no control) and I
imported into a DataTable to make the changes then insert the data into the
SQL table. The two columns should have valid dates but I have '00/00/0000'
in the two columns interspersed with the valid dates. I tried the Select()
method but I can't get that to update the right rows.

--
Regards,

Mike D
Coding in C# since Feb 2007


Eliyahu Goldin said:
What do you expect in "Create Date" as a bad value?

--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net


Mike D said:
I have a DataTable (over 32,000 rows) with 19 columns of which I need to
fix
some data in two of them in order to import the data into a SQL table. I
have a for loop but I can't figure out the if statement to find the 'bad'
columns so I edit them. This is what I have but it isn't working.

for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
if (dtTmpTable.Rows["Create Date"] == "00/00/0000") <-- not working
{
dtTmpTable.Rows["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();

--
Regards,

Mike D
Coding in C# since Feb 2007
Click to expand...
Click to expand...
 
I believe all the columns are of datatype string as I did a Select From
Openrowset from a csv file. The code I am using completes without error but
it doesn't find any rows with the string "00/00/0000" either. If I do a
Select() from a DataRow then I get around 12,000 rows for one column and
around 6,000 rows for the other column. But when I try to update the
Datatable I update the first 12,000/6,000 rows of the DataTable and not the
rows that need to be updated. I don't know how to convert the edited DataRow
results back to the DataTable.
--
Regards,

Mike D
Coding in C# since Feb 2007

Peter Bromberg said:
You need to find out what the Datatype is of the "CreateDate" column. If it
is datetime, then you need to cast it to

(Datetime) dtTmpTable.Rows["Create Date"]

otherwise, you are comparing a string to an object, which will fail.

-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com



Mike D said:
I have a DataTable (over 32,000 rows) with 19 columns of which I need to fix
some data in two of them in order to import the data into a SQL table. I
have a for loop but I can't figure out the if statement to find the 'bad'
columns so I edit them. This is what I have but it isn't working.

for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
if (dtTmpTable.Rows["Create Date"] == "00/00/0000") <-- not working
{
dtTmpTable.Rows["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();

--
Regards,

Mike D
Coding in C# since Feb 2007
Click to expand...
Click to expand...
 
I figured out how to do it.

DateTime dtTemp;
for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
try
{
dtTemp = DateTime.Parse(dtTmpTable.Rows["Create
Date"].ToString());
if (dtTemp < DateTime.Parse("1990-01-01") || dtTemp >
DateTime.Parse("2099-12-31"))
{
dtTmpTable.Rows["Create Date"] = DBNull.Value;
}
}
catch
{
dtTmpTable.Rows["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();

Now I have to do is figure out how to take the DataTable and insert it into
the SQL table.
--
Regards,

Mike D
Coding in C# since Feb 2007

Mike D said:
I believe all the columns are of datatype string as I did a Select From
Openrowset from a csv file. The code I am using completes without error but
it doesn't find any rows with the string "00/00/0000" either. If I do a
Select() from a DataRow then I get around 12,000 rows for one column and
around 6,000 rows for the other column. But when I try to update the
Datatable I update the first 12,000/6,000 rows of the DataTable and not the
rows that need to be updated. I don't know how to convert the edited DataRow
results back to the DataTable.
--
Regards,

Mike D
Coding in C# since Feb 2007

Peter Bromberg said:
You need to find out what the Datatype is of the "CreateDate" column. If it
is datetime, then you need to cast it to

(Datetime) dtTmpTable.Rows["Create Date"]

otherwise, you are comparing a string to an object, which will fail.

-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com



Mike D said:
I have a DataTable (over 32,000 rows) with 19 columns of which I need to fix
some data in two of them in order to import the data into a SQL table. I
have a for loop but I can't figure out the if statement to find the 'bad'
columns so I edit them. This is what I have but it isn't working.

for (int i = 0; i < dtTmpTable.Rows.Count; i++)
{
if (dtTmpTable.Rows["Create Date"] == "00/00/0000") <-- not working
{
dtTmpTable.Rows["Create Date"] = DBNull.Value;
}
}
dtTmpTable.AcceptChanges();

--
Regards,

Mike D
Coding in C# since Feb 2007
Click to expand...
Click to expand...
Click to expand...
 
Back
Top