I am using this code to insert a csv file to my database:
private void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{
using (SqlConnection dbConnection = new SqlConnection(ConnectionString))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "tablename";
foreach (var column in csvFileData.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(csvFileData);
}
}
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}
}
My database has temporary data in it. To test this code I copied fields with headers from SQL server and pasted it into an excel file. I then saved the excel file to csv and ran the code. It adds the data from the csv to the database perfectly!
I then tried running a csv file with similar values to my original csv file and its giving me a 'String to DateTime' Exception. So I know something is up with the Dates and I know that the excel columns are in value of 'Date'.
Im really scratching my head with this one. Any good way to parse columns with dates?
I'm noticing a few issues with your code that could cause you some trouble.
DataColumn
instance, the default column type will be System.String
. This is probably causing your date issues.datetime
and you are attempting to bulk insert a System.String
column you are going to run into issues.My suggestions would be the following:
DataColumn
constructor where you specify the column data type: new DataColumn("Name", typeof(DateTime))
The operation you are doing is a very basic ETL. It appears you have the Extract and Load portion working, the thing you are missing is the Transform component.