-
Sep 15th, 2014, 07:57 PM
#1
Thread Starter
Fanatic Member
Import and Export Excel Files into SQL with C# ?
I find the Import and Export examples in SQL Excel Files with C#2005, share with me.
-
Sep 18th, 2014, 12:52 PM
#2
Re: Import and Export Excel Files into SQL with C# ?
What are SQL Excel files?
Please share with us.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Sep 19th, 2014, 01:41 AM
#3
Thread Starter
Fanatic Member
Re: Import and Export Excel Files into SQL with C# ?
This is Import and Export excel 2007 into SQL 2005.
-
Sep 19th, 2014, 07:55 AM
#4
Re: Import and Export Excel Files into SQL with C# ?
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.
Last edited by abhijit; Sep 19th, 2014 at 07:57 AM.
Reason: alternate option.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Sep 21st, 2014, 03:49 AM
#5
Thread Starter
Fanatic Member
Re: Import and Export Excel Files into SQL with C# ?
I also have one for example writing Excel2007 file import, but it fails "Not a legal OleAut date", you see my code below:
Code:
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/
-
Sep 23rd, 2014, 10:14 AM
#6
Re: Import and Export Excel Files into SQL with C# ?
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.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Sep 24th, 2014, 02:48 AM
#7
Thread Starter
Fanatic Member
Re: Import and Export Excel Files into SQL with C# ?
Originally Posted by 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.
My file here: http://www.megafileupload.com/en/fil...Excel-rar.html
-
Sep 25th, 2014, 08:57 AM
#8
Re: Import and Export Excel Files into SQL with C# ?
Where is the create table script?
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Sep 25th, 2014, 09:24 AM
#9
Re: Import and Export Excel Files into SQL with C# ?
UPDATE:
i used the following sql statement to create the destination table.
PHP Code:
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.
PHP Code:
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);
}
}
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Oct 8th, 2014, 01:00 AM
#10
New Member
Re: Import and Export Excel Files into SQL with C# ?
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|