Efficient way of uploading xls records to DB

Question

Hi, 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.

Answer 1

Hi

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

Answer 2

Do you have permissions for bulk inserting?

Answer 3

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.

Answer 4

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. 
        }
    }