Import csv files into SQL server with date validation

[Guest]

I am very new to C# with little experience with stored procedures (former
Coldfusion developer). My problem is that I have two csv files (output from
SAP) that I need to import into two tables (after deleting current data) on a
reoccuring basis (about every 30 minutes). I have already figured out how to
delete data in the tables and I can read the csv into a dataset but I am
stuck at how to validate several of the columns which have dates and how to
take the data and import it into the tables. I have no control over the SAP
output and it is putting '00/00/0000' in as NULL for dates in a couple of the
columns and then there is the possibility of 'fat-fingered' entries. I
created a method that checks to see if the date is valid but I am not sure
how I should use it. Oh yeah, I don't have control over the database, so I
am stuck with the data types it is designed with. One csv has 24 and the
other has 29 columns consisting of int, bit, float, nvarchar(###),
nvarchar(MAX), datetime, and money.

 

[Paul Clement]

¤ I am very new to C# with little experience with stored procedures (former
¤ Coldfusion developer). My problem is that I have two csv files (output from
¤ SAP) that I need to import into two tables (after deleting current data) on a
¤ reoccuring basis (about every 30 minutes). I have already figured out how to
¤ delete data in the tables and I can read the csv into a dataset but I am
¤ stuck at how to validate several of the columns which have dates and how to
¤ take the data and import it into the tables. I have no control over the SAP
¤ output and it is putting '00/00/0000' in as NULL for dates in a couple of the
¤ columns and then there is the possibility of 'fat-fingered' entries. I
¤ created a method that checks to see if the date is valid but I am not sure
¤ how I should use it. Oh yeah, I don't have control over the database, so I
¤ am stuck with the data types it is designed with. One csv has 24 and the
¤ other has 29 columns consisting of int, bit, float, nvarchar(###),
¤ nvarchar(MAX), datetime, and money.

I would think that once you get the data from the CSV file into a DataSet/DataTable you can cycle
through all of the rows performing your data validation, save the changes and then use the
SQLBulkCopy class to export to SQL Server.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx


Paul
~~~~
Microsoft MVP (Visual Basic)