- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- using System.IO;
- using System.Data.SqlClient;
- using System.Data;
- using System.Configuration;
- using System.Diagnostics;
- using System.Timers;
- using System.Data.OleDb;
- namespace ConsoleApplication10
- {
- class Program
- {
- static void Main(string[] args)
- {
- string server = "NAHLA-PC";
- string database = "master";
- string SQLServerConnectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, database);
- // string SQLServerConnectionString = String.Format("Data Source = NAHLA - PC; Initial Catalog = master; Integrated Security = True");
- string CSVpath = @"C:\mock2\FINDATA.LOG"; // 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("*.REQ");
- string File_Name = string.Empty;
- foreach (var file in AllFiles)
- {
- try
- {
- DataTable dt = new DataTable();
- ///////////////////////////////////////////////////////////////////////////////
- dt.Columns.AddRange(new DataColumn[10] { new DataColumn("id", typeof(int)),
- new DataColumn("Name", typeof(string)),
- new DataColumn("Account", typeof(string)),
- new DataColumn("Email_id", typeof(string)),
- new DataColumn("Email_type", typeof(string)),
- new DataColumn("Currency", typeof(string)),
- new DataColumn("Amount", typeof(string)),
- new DataColumn("Date", typeof(string)),
- new DataColumn("TXN_id",typeof(string)),
- new DataColumn("System_ip",typeof(string)) });
- //////////////////////////////////////////////////////////////////////////////
- using (OleDbConnection con = new OleDbConnection(CSVFileConnectionString))
- {
- con.Open();
- var csvQuery = string.Format("select * from [{0}]", file.Name);
- using (OleDbDataAdapter da = new OleDbDataAdapter(csvQuery, con))
- {
- da.Fill(dt);
- }
- }
- using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLServerConnectionString))
- {
- bulkCopy.ColumnMappings.Add(0, "id");
- bulkCopy.ColumnMappings.Add(1, "Name");
- bulkCopy.ColumnMappings.Add(2, "Account");
- bulkCopy.ColumnMappings.Add(3, "Email_id");
- bulkCopy.ColumnMappings.Add(4, "Email_type");
- bulkCopy.ColumnMappings.Add(5, "Currency");
- bulkCopy.ColumnMappings.Add(6, "Amount");
- bulkCopy.ColumnMappings.Add(7, "Date");
- bulkCopy.ColumnMappings.Add(8, "TXN_id");
- bulkCopy.ColumnMappings.Add(9, "System_ip");
- bulkCopy.DestinationTableName = "FCBCustomers";
- bulkCopy.BatchSize = 0;
- bulkCopy.WriteToServer(dt);
- bulkCopy.Close();
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- }
- }
- }
Loading
Mitesh GadhiyaPosted Jul 23, 2017, 3:19 AM