Import data from Excel To Datatble and Data table to SQL using asp.net

 public DataSet ExcelRead(string ExcelFilePath, string sheetName)
    {
        DataSet dataSet = new DataSet();
        try
        {
            string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""", ExcelFilePath);
            string query = String.Format("SELECT * FROM [{0}$]", sheetName);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
         
            dataAdapter.AcceptChangesDuringFill = true;
            dataAdapter.Fill(dataSet);
         
        }
        catch(Exception ex)
        {
            ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Message", "alert('please Enter Sheet Name Properly..')", true);
         
        }
        return dataSet;
    }
protected void UploadButton_Click(object sender, EventArgs e)
    {
DataSet ds5 = ExcelRead(fileName, txtSheetName.Text);
  DataTable dtExcel = ds5.Tables[0];

for (int i = 0; i < dtExcel.Rows.Count; i++)
                    {
                        try
                        {
                            count = count + 1;
                            if (BankName == "VIJAYA BANK")
                            {
                                val = P.ExcuteQuery("insert into Vijaya_Bank values('" + dtExcel.Rows[i][0] + "','" + dtExcel.Rows[i][1] + "','" + dtExcel.Rows[i][2] + "','" + dtExcel.Rows[i][3] + "','" + dtExcel.Rows[i][4] + "','" + dtExcel.Rows[i][5] + "','" + ddlBankName.SelectedItem + "','" + ddlAccountNumber.SelectedItem + "')");

                            }
}

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,