What would be the most efficient way to upload records from an excel file to a table in the database. I am not allowed to use DTS/ SSIS. So I would like to know if there is a better alternative than reading records sequentially from the file and firing commands.
Thanks.
You could use the bcp utility. Save the Excel file as text and bcp it in. You don't usually need bulk insert privileges to do that.
Do you have permissions for bulk inserting?
This page has code that does the opposite - extract data from SQL Server and insert it into Excel. All you need to do is swap the connection strings.
Like this:
private System.Data.OleDb.OleDbDataAdapter da ;
private System.Data.DataSet ds;
string sqlSelect="SELECT ProductId, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, GETDATE() as TimeExtracted from Products order by UnitPrice";
string sqlInsert="INSERT INTO Foo (ProductId, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, TimeExtracted) VALUES (@ProductId, @ProductName, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @TimeExtracted)";
string ExtractedTableName= "ExtractedData";
private void ReadFromSource()
{
System.Console.WriteLine("Reading from Source...");
string ConnStringSource=
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + ExcelFilename + ";" +
"Extended Properties=\"Excel 8.0;HDR=yes;\""; // FIRSTROWHASNAMES=1;READONLY=false\"
using (var conn= new System.Data.OleDb.OleDbConnection(ConnStringSource))
{
da= new System.Data.OleDb.OleDbDataAdapter();
da.SelectCommand= new System.Data.OleDb.OleDbCommand(sqlSelect);
da.SelectCommand.Connection= conn;
// this tells the DA to mark all rows as newly inserted.
// upon calling da.Update() (later), all those rows will
// be inserted into the DB.
da.AcceptChangesDuringFill= false;
ds= new System.Data.DataSet();
da.Fill(ds, ExtractedTableName);
}
}
private void InsertIntoDestination()
{
System.Console.WriteLine("Inserting data into Destination...");
string ConnStringDest= "Provider=sqloledb;Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;";
using (var conn= new System.Data.OleDb.OleDbConnection(ConnStringDest))
{
System.Data.OleDb.OleDbCommand cmd= new System.Data.OleDb.OleDbCommand(sqlInsert);
cmd.Parameters.Add("@ProductId", System.Data.OleDb.OleDbType.Integer, 4, "ProductId");
cmd.Parameters.Add("@ProductName", System.Data.OleDb.OleDbType.VarWChar, 40, "ProductName");
cmd.Parameters.Add("@QuantityPerUnit", System.Data.OleDb.OleDbType.VarWChar, 20, "QuantityPerUnit");
cmd.Parameters.Add("@UnitPrice", System.Data.OleDb.OleDbType.Currency, 8, "UnitPrice");
cmd.Parameters.Add("@UnitsInStock", System.Data.OleDb.OleDbType.SmallInt, 2, "UnitsInStock");
cmd.Parameters.Add("@TimeExtracted", System.Data.OleDb.OleDbType.Date, 8, "TimeExtracted");
da.InsertCommand= cmd;
da.InsertCommand.Connection= conn;
da.Update(ds, ExtractedTableName);
// in the event you want to update a datasource via a different DataAdapter --
// for example you want to fill from a System.Data.SqlClient.DataAdapter and
// then Update via a System.Data.Oledb.OledbDataAdapter -- then you could define
// two distinct DataAdapters. Fill the DataSet with the first DA, then Update
// with the second DA.
}
}
I suggest you connect to the excel file using ODBC/DSN through an ADODB connection. I have found that this is pretty efficient.
You first create an ODBC Data source name thro: Control Panel>Administrative Tools> Data sources (ODBC). Select the 'System' tab and click 'Add'. Select 'Microsoft Excel driver' from the list of drivers that appears. Give your DSN a name say 'MYDB', then navigate the xlS file and double click to select.
This can be done programatically, its just that we created a dll to do it like 5 years ago and Im still trying to locate its source code. I will post its code as soon as I get it.
Then from your program you can connect to your DSN as follows:
'declare the connection
Global MyConn As New ADODB.Connection
'open the connection
MyConn.Open "DSN=MYDB;pwd=;"
You can then manipulate the connection through ADODB recordsets in the normal way.
I hope this helps
