.csv files have 57 columns and some of the values are empty. For example, ,Jane,Doe,,35. Let's say the first value (before the comma) is for the automated ID key which increments every time it is imported to the database. The problem is the empty values are not imported thus resulting an error. What I understand here is SQL database cannot read empty values so it moves the values, like this: Jane,Doe,35, thus making Jane the value for ID. Another concern is the .csv files' columns doesn't match the table. For example, table has a column ID while as the .csv files start with a Name column. Is there a way to import it to database starting from a particular column?
Note: This is in one table only.
Question: Should it be more preferable if I make a separate table that matches the columns of .csv files and then join it with the table that has a column ID (on the example)?
My codes so far:
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[54] { new DataColumn("Delay_Code"), and so on... });
string csvData = File.ReadAllText(e.FullPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
string consString = @"Data Source="blahblah";
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "owner.Table";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
For the code above, I have created another table which matches the columns of the .csv files. Originally, the table has three preceding columns before the columns in the .csv files.
What's the best approach for this?
