Data from CSV file can import save in database. but how we can add if have all the column in database table but not have in one column in csvfile but still save other data column from the csv file in that databse. For example, in database we have column [datatime],[milliseconds],[pressure] table. In csv file we have data table [datetime], [pressure]. how we can import CSV data to SQL server.
because when tried I get this error: System.InvalidOperationException: 'The given ColumnName 'Milliseconds' does not match up with any column in data source.'
CODE
private void btnselect_Click(object sender, EventArgs e)
{
//open serach file to recognise by the
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = ".csv";
ofd.Filter = "Comma Separated(*.csv)|*.csv";
ofd.ShowDialog();
textfilename.Text = ofd.FileName;
}
private 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)
{
while (! csvReader.EndOfData)//eth
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}
}
}
catch (Exception ex)
{
return null;
}
return csvData;
}
private void btnImport_Click(object sender, EventArgs e)
{
{
Cursor = Cursors.WaitCursor;
DataTable dt = GetDataTabletFromCSVFile(csv_file_path);
if (dt == null) return;
SaveImportDataToDatabase(dt);
MessageBox.Show("Data Import success!");
textfilename.Text = string.Empty;
Cursor = Cursors.Default;
}
}
private void SaveImportDataToDatabase(DataTable SS)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=ytl//bku; Initial Catalog=databse; User Id=***; Password=*****"))
{
conn.Open();
using (SqlBulkCopy sqlbc = new SqlBulkCopy(conn))
{
sqlbc.DestinationTableName = "SS";
sqlbc.ColumnMappings.Add("DateTime", "DateTime");
sqlbc.ColumnMappings.Add("Milliseconds", "Milliseconds");
sqlbc.ColumnMappings.Add("Pressure", "Pressure");
sqlbc.WriteToServer(SS);
MessageBox.Show("Bulk data stored ");
Jignesh KumarPosted Jan 5, 2023, 9:48 AM
Can you please elaberate more ? which type of validation you want for your data. Any example then member can help on that area
You can validate row wise as below,
Aravind GovindarajPosted Jan 5, 2023, 9:31 AM
Validation is the one which is used to cross-check SQL Columns vs CSV columns. Please follow the steps
1. Is both columns are sync? If not pass the default or null value
2. If CSV column has appropriate data type, consider in age column may be the csv contains name, so validate before insert. if any mismatch, follow the use case, do we need to ignore the row and proceed next row or requied as hard stop.
hope this helps you
dhivyah jaiyasangerPosted Jan 5, 2023, 8:41 AM
how to validate the data ?
Jignesh KumarPosted Dec 29, 2022, 11:27 AM
Hello,
You should validate your data before insert into table and need to check columnmapping for source(.csv) and sql table. If both are not mactching then you need to validate it.
In above case still you want to insert then create "Milliseconds" column in csv file and put 0 as milliseconds as default value. then it should work for you
Aravind GovindarajPosted Dec 29, 2022, 9:36 AM
It seems that your CSV column vs DB data model is not synced. While doing the bulk copy, we need to make sure both schemas are in sync. In your case, feel define the "Milliseconds" column in CSV however make it empty if no record exists.