Click here to Skip to main content
15,890,123 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Window application for import csv data to sql server.

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 database. 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.'

What I have tried:

C#
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 ");
Posted
Updated 28-Dec-22 23:06pm
v2
Comments
Karthik_Mahalingam 29-Dec-22 5:54am    
place a breakpoint at the line and visualize the dt object and check if "Milliseconds" column is present
DataTable dt = GetDataTabletFromCSVFile(csv_file_path);
check for spell

1 solution

You will have to add a column "Milliseconds" to the DataTable containing the CSV data, and provide it with a sensible default value.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900