Import and Export Excel Files into SQL with C# ?

[dong]

I find the Import and Export examples in SQL Excel Files with C#2005, share with me.

[abhijit]

What are SQL Excel files?

Please share with us.

[dong]

This is Import and Export excel 2007 into SQL 2005.

[abhijit]

SQL Server 2005 has BULK INSERT.

This can work with csv files or text files, but not with native excel format. Is this something you can use?

Alternatively, you can use ADO.NET to write C# code. I have done something similar with oracle. Instead of ODP.NET, use SqlData libraries to achieve the same results.

[dong]

I also have one for example writing Excel2007 file import, but it fails "Not a legal OleAut date", you see my code below:

public static void ImportToSql(string excelfilepath)
        {
            string ssqltable = "TABHD";
            string myexceldataquery = "SELECT * FROM [dbo_TABHD]";
            try
            {
                string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
                string ssqlconnectionstring = @"server = itfriend;Database=HD;integrated security = true";
 
                //execute a query to erase any previous data from our destination table
                string sclearsql = "delete " + ssqltable;
                INSERTING IT.INSTEAD I WANT TO UPDATE TABLE DATA"
                SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);                
 
                OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
                OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
                oledbconn.Open();
                OleDbDataReader dr = oledbcmd.ExecuteReader();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
                bulkcopy.DestinationTableName = ssqltable;
 
                bulkcopy.WriteToServer(dr); // Eror in here: "Not a legal OleAut date"
 
                Console.WriteLine(".xlsx file imported succssessfully into database.", bulkcopy.NotifyAfter);
                oledbconn.Close();
            }
            catch (Exception ex)
            {
                //handle exception
                MessageBox.Show(ex.Message.ToString(), "Warning !");
            }
        }

it runs fine with excel2003 file, you can help me fix it?
connection strings excel
https://connectionstrings.com/excel-2007/

[abhijit]

Could you please post the following?
1) Structure of your table - CREATE TABLE SQL script.
2) Excel file.

I believe you're hitting an issue with the data. You'll need to sanitize the data. We use BULK writing to upload to staging tables and then we run a stored procedure on the staging tables to sanitize the data, before the data gets loaded to actual tables used by the application. I do not know if this is a valid scenario for you.

[dong]

Could you please post the following?
1) Structure of your table - CREATE TABLE SQL script.
2) Excel file.

I believe you're hitting an issue with the data. You'll need to sanitize the data. We use BULK writing to upload to staging tables and then we run a stored procedure on the staging tables to sanitize the data, before the data gets loaded to actual tables used by the application. I do not know if this is a valid scenario for you.

My file here: http://www.megafileupload.com/en/fil...Excel-rar.html

[abhijit]

Where is the create table script?

[abhijit]

UPDATE:
i used the following sql statement to create the destination table.

CREATE TABLE TABHD(
IDHD    int    NOT NULL PRIMARY KEY,
MAKHACH    int    ,
MAKYHIEUTT    nvarchar(10)    ,
MANHOM_HD    nvarchar(10)    ,
MATRAM    nvarchar(50)    ,
SOHD    nvarchar(10)    ,
NGUOIKY    nvarchar(200)    ,
NGAYKY    datetime    ,
NGAYKT    datetime    ,
NGAYQD    datetime    ,
NGUOIGS    nvarchar(100)    ,
NGUOIGS_DT    datetime    ,
GIATRIHD    int    ,
NOIDUNG    nvarchar(200)    ,
GIATRIQT    int    ,
LOINHUANTONG    nvarchar(10)    ,
GHICHU    nvarchar(10)    ) 

I also modified your code to get a datatable instead of a datareader. Here is my c# code. This works as designed with the same file you've given me.

        public static void ImportToSql(string excelfilepath)
        {
            string ssqltable = "TABHD";
            string myexceldataquery = "SELECT * FROM [dbo_TABHD]";
            try
            {
                string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
                var connection = new OleDbConnection(sexcelconnectionstring);
                connection.Open();
                Console.WriteLine(connection.State);
                var cmd = new OleDbCommand(myexceldataquery, connection);
                var oleDbDataAdapter = new OleDbDataAdapter(cmd);
                var dateTable = new DataTable("TABHD");
                oleDbDataAdapter.Fill(dateTable);

                Console.WriteLine(connection.State);
                string ssqlconnectionstring = @"Server=LT3013mod\sqlexpress;Database=Sandbox;integrated security = true";

                ////execute a query to erase any previous data from our destination table
                //string sclearsql = "delete " + ssqltable;
                ////INSERTING IT.INSTEAD I WANT TO UPDATE TABLE DATA"
                SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);                
                sqlconn.Open();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconn) { DestinationTableName = ssqltable, BulkCopyTimeout = 100};
                bulkcopy.WriteToServer(dateTable); // Eror in here: "Not a legal OleAut date"
                sqlconn.Close();
                Console.WriteLine(".xlsx file imported succssessfully into database.", bulkcopy.NotifyAfter);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        } 

[leo_213]

Hello,

This article gives an example to export Excel to datatable in C#, hope it helps
http://janewdaisy.wordpress.com/2011...-to-datatable/

Regards,
leo