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 ");