Forums - C# Corner

Forum guidelines
nahla elhaj

nahla elhaj

  • 1.5k
  • 3
  • 223

i have this code to import multi csv files & it gives error

Jul 22 2017 12:20 PM
  1. <!-- begin snippet: js hide: false console: true babel: false -->  
  2.   
  3. <!-- language: lang-html -->  
  4.   
  5.     using System;  
  6.     using System.Collections.Generic;  
  7.     using System.Linq;  
  8.     using System.Text;  
  9.     using System.Threading.Tasks;  
  10.     using System.IO;  
  11.     using System.Data.SqlClient;  
  12.     using System.Data;  
  13.     using System.Configuration;  
  14.     using System.Diagnostics;  
  15.     using System.Timers;  
  16.     using System.Data.OleDb;  
  17.   
  18.     namespace ConsoleApplication10  
  19.     {  
  20.         class Program  
  21.         {  
  22.             static void Main(string[] args)  
  23.             {  
  24.                string server = "NAHLA-PC";  
  25.                string database = "master";   
  26.               string SQLServerConnectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, database);  
  27.              //   string SQLServerConnectionString = String.Format("Data Source = NAHLA - PC; Initial Catalog = master; Integrated Security = True");  
  28.   
  29.   
  30.                 string CSVpath = @"C:\mock2\FINDATA.LOG"// CSV file Path  
  31.                 string CSVFileConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};;Extended Properties=\"text;HDR=YES;FMT=Delimited(*)\";", CSVpath);  
  32.   
  33.                 var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.REQ");  
  34.                 string File_Name = string.Empty;  
  35.   
  36.                 foreach (var file in AllFiles)  
  37.                 {  
  38.                     try  
  39.                     {  
  40.                         DataTable dt = new DataTable();  
  41.                         ///////////////////////////////////////////////////////////////////////////////  
  42.   
  43.                         dt.Columns.AddRange(new DataColumn[10] { new DataColumn("id"typeof(int)),  
  44.                                 new DataColumn("Name"typeof(string)),  
  45.                                 new DataColumn("Account"typeof(string)),  
  46.                                 new DataColumn("Email_id"typeof(string)),  
  47.                                 new DataColumn("Email_type"typeof(string)),  
  48.                                 new DataColumn("Currency"typeof(string)),  
  49.                                 new DataColumn("Amount"typeof(string)),  
  50.                                 new DataColumn("Date"typeof(string)),  
  51.                                 new DataColumn("TXN_id",typeof(string)),  
  52.                                 new DataColumn("System_ip",typeof(string)) });  
  53.   
  54.                         //////////////////////////////////////////////////////////////////////////////  
  55.                         using (OleDbConnection con = new OleDbConnection(CSVFileConnectionString))  
  56.                         {  
  57.                             con.Open();  
  58.                             var csvQuery = string.Format("select * from [{0}]", file.Name);  
  59.                             using (OleDbDataAdapter da = new OleDbDataAdapter(csvQuery, con))  
  60.                             {  
  61.                                 da.Fill(dt);  
  62.                             }  
  63.                         }  
  64.   
  65.                         using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLServerConnectionString))  
  66.                         {  
  67.                             bulkCopy.ColumnMappings.Add(0, "id");  
  68.                             bulkCopy.ColumnMappings.Add(1, "Name");  
  69.                             bulkCopy.ColumnMappings.Add(2, "Account");  
  70.                             bulkCopy.ColumnMappings.Add(3, "Email_id");  
  71.                             bulkCopy.ColumnMappings.Add(4, "Email_type");  
  72.                             bulkCopy.ColumnMappings.Add(5, "Currency");  
  73.                             bulkCopy.ColumnMappings.Add(6, "Amount");  
  74.                             bulkCopy.ColumnMappings.Add(7, "Date");  
  75.                             bulkCopy.ColumnMappings.Add(8, "TXN_id");  
  76.                             bulkCopy.ColumnMappings.Add(9, "System_ip");  
  77.                             bulkCopy.DestinationTableName = "FCBCustomers";  
  78.                             bulkCopy.BatchSize = 0;  
  79.                             bulkCopy.WriteToServer(dt);  
  80.                             bulkCopy.Close();  
  81.                         }  
  82.   
  83.                     }  
  84.                     catch (Exception ex)  
  85.                     {  
  86.                         throw ex;  
  87.                     }  
  88.                 }  
  89.             }  
  90.         }  
  91.     }  
  92.   
  93.   
  94. <!-- end snippet -->  

Answers (1)