Import Data from CSV File to Data table

Using this Function it will read the csv file and it returns the data table

static DataTable ImportCSVoDatatable(string FilePath)
    {
        DataTable dt = new DataTable();
            string[] Lines = File.ReadAllLines(FilePath);
            string[] Fields;
            Fields = Lines[0].Split(new char[] { ',' });
            int Cols = Fields.GetLength(0);
         
            //1st row must be column names; force lower case to ensure matching later on.
            for (int i = 0; i < Cols; i++)
                dt.Columns.Add(Fields[i].ToLower(), typeof(string));
            DataRow Row;
            for (int i = 1; i < Lines.GetLength(0); i++)
            {
                Fields = Lines[i].Split(new char[] { ',' });
                Row = dt.NewRow();
                for (int f = 0; f < Cols; f++)
                    Row[f] = Fields[f];
                dt.Rows.Add(Row);
            }
return dt;
}

CSV File to SQL using Stored procedure

create procedure [dbo].[SP_Export](@FilePath nvarchar(500))
as
begin
DECLARE @filepath1 nvarchar(500)
--SET @filepath1 = ''+@FilePath+''
--select @FilePath1
DECLARE @bulkinsert NVARCHAR(2000)
SET @bulkinsert = 'bulk insert [dbo].[vw_bulk_insert_test] FROM '''+@filepath+''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
--print @bulkinsert
EXEC sp_executesql @bulkinsert
end
--exec SP_Export 'D:\Pass sheets\SYNDOD26.02.14.csv'

1 Use a View
Create a view that has only required columns

1.create view vw_bulk_insert_test

2.as

3.select first_name,last_name from bulk_insert_test

Now use BULK INSERT using this view

1.BULK INSERT vw_bulk_insert_test

2.FROM 'g:\test.txt'

3.WITH

4.(

5.FIELDTERMINATOR =',',

6.ROWTERMINATOR = '\n'

7.)

2 Create Format file

1.8.0

2.2

3.1  SQLCHAR  0  30 ","        2  first_name                SQL_Latin1_General_Cp437_BIN

4.2  SQLCHAR  0  30 ","        3  last_name                 SQL_Latin1_General_Cp437_BIN

Name it as format.txt Now use BULK INSERT using this format file

1.BULK INSERT bulk_insert_test

2.FROM 'g:\test.txt'

3.with (formatfile = 'g:\format.txt')

3 Use OPENROWSET

1.INSERT INTO bulk_insert_test(first_name,last_name)

2.SELECT

3.*

4.FROM

5.OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt)

As you see method 3 doesn't require any extra work if the number of columns are different

Tags: t-sql, sql_server, bulk_insert,