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

[Nhlanhlanzima1233]

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();
}

Continue reading...