none
When importing from CSV to SQL Server using C# SqlBulkCopy data is imported only in one column RRS feed

  • Question

  • Hi All,

    When importing from CSV to SQL Server using C# SqlBulkCopy all data is imported only in one column other columns are empty, however the ColumnMappings is all done. see my code below: Please Help me Good people its very urgent your response will be appreciated.

    public void BulkClientsInsertFromCSV()
            {

                System.Data.SqlClient.SqlConnection sqlConnection1 =
                new System.Data.SqlClient.SqlConnection("Data Source=VTSRMDMDBS01.SR.TEST.JSE.CO.ZA;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
                string CSVpath = @"C:\Users\nhlanhlam\Documents\CSV"; // CSV file Path
                string CSVFileConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};;Extended Properties=\"text;HDR=Yes;FMT=Delimited\";", CSVpath);
               
                var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
                string File_Name = string.Empty;

                foreach (var file in AllFiles)
                {
                    try
                    {
                         //DataTable dt = new DataTable("dbo.CRM_Clients");
                       
                        using (OleDbConnection con = new OleDbConnection(CSVFileConnectionString))
                        {
                            con.Open();
                            Console.WriteLine("Connection to CSV file has been Opened!!");
                            var csvQuery = string.Format("select * from [{0}]", file.Name);
                            using (OleDbDataAdapter da = new OleDbDataAdapter(csvQuery, con))
                            {
                                da.Fill(dt);
                            }
                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection1))
                            {
                                sqlConnection1.Open();
                                bulkCopy.ColumnMappings.Add(0, "ClientName");
                                bulkCopy.ColumnMappings.Add(1, "JSEClientType");
                                bulkCopy.ColumnMappings.Add(2, "ClientRole");
                                bulkCopy.ColumnMappings.Add(3, "AlphaCode");
                                bulkCopy.ColumnMappings.Add(4, "ParentClient");
                                bulkCopy.ColumnMappings.Add(5, "PhoneNo");
                                bulkCopy.ColumnMappings.Add(6, "FaxNo");
                                bulkCopy.ColumnMappings.Add(7, "Email");
                                bulkCopy.ColumnMappings.Add(8, "Website");
                                bulkCopy.ColumnMappings.Add(9, "Exchange");
                                bulkCopy.ColumnMappings.Add(10, "ResidentialStatus");
                                bulkCopy.ColumnMappings.Add(11, "VatNumber");
                                bulkCopy.ColumnMappings.Add(12, "TaxNumber");
                                bulkCopy.ColumnMappings.Add(13, "EffDate");
                                bulkCopy.ColumnMappings.Add(14, "EffTo");
                                bulkCopy.ColumnMappings.Add(15, "Owner");
                                bulkCopy.ColumnMappings.Add(16, "CreatedOn");
                                bulkCopy.ColumnMappings.Add(17, "StatusReason");
                                bulkCopy.ColumnMappings.Add(18, "AdressStreet1");
                                bulkCopy.ColumnMappings.Add(19, "AdressStreet2");
                                bulkCopy.ColumnMappings.Add(20, "AdressStreet3");
                                bulkCopy.ColumnMappings.Add(21, "City");
                                bulkCopy.ColumnMappings.Add(22, "Province");
                                bulkCopy.ColumnMappings.Add(23, "PostalCode");
                                bulkCopy.ColumnMappings.Add(24, "Country");

                                
                                AddColumnsToTable();//calling the method to add Columns to the Table see below


                                bulkCopy.DestinationTableName = "dbo.CRM_Clients";
                                Console.WriteLine("Mappings Of fields has been completed!!");
                                bulkCopy.BatchSize = 0;
                                bulkCopy.WriteToServer(dt);
                                Console.WriteLine("Database has been loaded with Clients Information Succesfully");
                                bulkCopy.Close();
                                Console.WriteLine("Press Any Key to Continue.....");
                                Console.ReadKey();

                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ex.ToString();
                    }
                }
            }

            //This method Adds all tables columns to the Table itself.
            public void AddColumnsToTable()
            {
                dt.Columns.Add("ClientName", typeof(string));
                dt.Columns.Add("JSEClientType", typeof(string));
                dt.Columns.Add("ClientRole", typeof(string));
                dt.Columns.Add("AlphaCode", typeof(string));
                dt.Columns.Add("ParentClient", typeof(string));
                dt.Columns.Add("PhoneNo", typeof(string));
                dt.Columns.Add("FaxNo", typeof(string));
                dt.Columns.Add("Email", typeof(string));
                dt.Columns.Add("Website", typeof(string));
                dt.Columns.Add("Exchange", typeof(string));
                dt.Columns.Add("ResidentialStatus", typeof(string));
                dt.Columns.Add("VatNumber", typeof(string));
                dt.Columns.Add("TaxNumber", typeof(string));
                dt.Columns.Add("EffDate", typeof(string));
                dt.Columns.Add("EffTo", typeof(string));
                dt.Columns.Add("Owner", typeof(string));
                dt.Columns.Add("CreatedOn", typeof(string));
                dt.Columns.Add("StatusReason", typeof(string));
                dt.Columns.Add("AdressStreet1", typeof(string));
                dt.Columns.Add("AdressStreet2", typeof(string));
                dt.Columns.Add("AdressStreet3", typeof(string));
                dt.Columns.Add("City", typeof(string));
                dt.Columns.Add("Province", typeof(string));
                dt.Columns.Add("PostalCode", typeof(string));
                dt.Columns.Add("Country", typeof(string));
                DataRow newrow = dt.NewRow();
            }

     

    Thursday, December 6, 2018 6:12 AM

All replies

  • Maybe you should specify the delimiter that is used to separate the columns. For example, if it is ‘;’ in your CSV file, then use this:

       …HDR=Yes;FMT=Delimited(;)\"", CSVpath);


    Thursday, December 6, 2018 8:22 AM
  • Hi Nhlanhlanzima1233,

    Thank you for posting here.

    For your question, you want to import from CSV to SQL Server.

    I used a simpler method, please pay attention to where I am bold.

    Please try this  code below.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace test1
    {
        class Program
        {
            static void Main(string[] args)
            {
                Program program = new Program();
                program.BulkClientsInsertFromCSV();
                Console.ReadKey();
               
            }
            public void BulkClientsInsertFromCSV()
            {
    
                string str = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                string CSVpath = @"C:\xxx"; // CSV file Path
               var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
                foreach (var file in AllFiles)
                {
                    try
                    {
                        using (SqlConnection con = new SqlConnection(str))
                        {
                            con.Open();
                           var csvQuery = string.Format("BULK INSERT [dbo].[client] from [{0}]  WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')", CSVpath+@"\"+file.Name);
                            SqlCommand cmd = new SqlCommand(csvQuery, con);
                            cmd.ExecuteNonQuery();
                            cmd.Clone();
                        }
                    }
                    catch (Exception ex)
                    {
                        ex.ToString();
                    }
                }
            }
        }
    }

    CSV:

    Database:

    Result:

    Best regards,

    Jack J Jun.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, December 10, 2018 4:07 AM
    Moderator