?
Solved

VB.NET reading excel file and update database via query

Posted on 2009-02-24
13
Medium Priority
?
5,931 Views
Last Modified: 2012-05-06
I am writing a small application that would read an excel file and then update a SQL database.  The excel file has only 2 columns A and B.  A has the Item_ID and B has the Cost.  What I wanted to do is read this excel file and then update the database table if it matches the Item_ID.

Example:
ColumnA              ColumnB
ABC                       15.00
DEF                       20.00
GHI                       18.00

Below I started out reading the excel file, but I am running into an error that the "Microsoft Jet Database Engine could not find the object.  Make sure the object exists and that you spell its name and the path name correctly.  Here's the query that I want to update with.

UPDATE PARTS
SET PRICE = strPrice
WHERE ITEM_ID = strItem


Being a novice at VB.NET, I am not sure what I need to do.
Try
            Dim strConn As String
            Dim strSQL As String
 
            strConn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(0);Extended Properties='Excel 8.0;HDR=Yes'", "C:\Output.xls")
 
            strSQL = "SELECT * FROM [PRICE]"
 
 
            Dim cnn As New OleDb.OleDbConnection(strConn)
            Dim cmd As New OleDb.OleDbCommand(strSQL, cnn)
 
            Dim oleReader As OleDb.OleDbDataReader
            Dim dtbExcelData As New Data.DataTable
 
            Call cnn.Open()
            oleReader = cmd.ExecuteReader
 
            dtbExcelData.Load(oleReader)
 
            Call oleReader.Close()
            Call cnn.Close()
 
            oExcel = CreateObject("Excel.Application")
            Dim wbk As Object
 
            wbk = oExcel.Workbooks.Open(FileName:=txbFile.Text, UpdateLinks:=False, ReadOnly:=False)
 
            Dim i As Integer
 
            numrow = 2
 
            For i = 0 To dtbExcelData.Rows.Count - 1
                With wbk.ActiveSheet
                    If .Range("A" & numrow).Value <> "" Then
                        strItem = .Range("A" & numrow).value
                        numrow = numrow + 1
                    Else
                        numrow = numrow + 1
                    End If
                End With
            Next
 
            wbk = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
 
        Catch ex As Exception
            MsgBox("Error with excel file: " & ex.Message)
        End Try

Open in new window

0
Comment
Question by:holemania
  • 7
  • 4
  • 2
13 Comments
 
LVL 4

Expert Comment

by:ThatDeadDude
ID: 23726446
I think that as it stands the issue is with your connection on line 5.  It appears that you are opening an Excel file as if it were a database, and using the Jet provider.  Is there a reason for this?
0
 

Author Comment

by:holemania
ID: 23734942
There's no reason for this.  Is there another way of opening the excel file and reading those 2 columns?

Basically I just want to be able to read the excel file, count/read number of rows with data and than loop around my update query to update the SQL database with the pricing.
0
 
LVL 4

Expert Comment

by:ThatDeadDude
ID: 23737588
My issue was with the initial connection at the top of your code.  It's unnecessary given that you're creating a reference to the Excel application in the CreateObject line.  Thus, I think the connection string at the top of the page is where the problem is occurring.  I've not used .NET, but given that a lot of the syntax is similar to old-school VB I'll try give you something of a correction.  You may have to fix some syntax errors, and you'll have to play with the connection string to get the connection to your SQL Server working properly.
Try
            Dim strConn As String
            Dim strSQL As String
 
            strConn = "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=dBName; User Id=myUserName; Password=myPassword"
 
            Dim cnn As New OleDb.OleDbConnection(strConn)
            Dim cmd As OleDb.OleDbCommand
 
            oExcel = CreateObject("Excel.Application")
            Dim wbk As Object
 
            wbk = oExcel.Workbooks.Open(FileName:=txbFile.Text, UpdateLinks:=False, ReadOnly:=False)
 
            Dim i As Integer
 
            numrow = 2
 
            For i = 0 To wbk.ActiveSheet.UsedRange.Rows.Count - 1
                With wbk.ActiveSheet
                    If .Cells(numrow, 1).Value <> "" Then
                        strItem = .Range("A" & numrow).value
                        strSQL = "UPDATE PARTS SET PRICE = " & .Cells(numrow, 2).Value & " WHERE ITEM_ID = " & .Cells(numrow, 1).Value
                        cmd = new OleDb.OleDbCommand(strSQL, cnn)
                        cmd.ExecuteNonQuery
                        cmd = Nothing
                        numrow = numrow + 1
                    Else
                        numrow = numrow + 1
                    End If
                End With
            Next
 
            wbk = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
 
        Catch ex As Exception
            MsgBox("Error with excel file: " & ex.Message)
End Try

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 21

Expert Comment

by:silemone
ID: 23738650
are you using excel 2007?  or 2003?  if 2007, then you're using wrong connect string...
0
 

Author Comment

by:holemania
ID: 23744741
I am using excel 2003.
0
 

Author Comment

by:holemania
ID: 23745534
Thinking this over, I think my approach is probably not the best way of updating a SQL database via reading it off an excel spreadsheet.  Would it be more efficient to read the excel file into a dataset then create another procedure where it would read from the dataset and then update the SQL database?  Anyone might have sample code of reading excel into a dataset and then update a table in SQL Server base on the Item_ID?

Example:
ColumnA              ColumnB
ABC                       15.00
DEF                       20.00
GHI                       18.00

Once that is read into a dataset, create another function to update a table called PARTS in SQL if the item_id matches?
UPDATE PARTS
SET PRICE = strPrice
WHERE ITEM_ID = strItem

0
 
LVL 21

Expert Comment

by:silemone
ID: 23750934
I'm doing something similar and I do think it probably would be more efficient to do one procedure...and once that procedure has been completed complete the next...maybe not super quick, but I wrote my code where it reads and then updates database and its locking the computer up and using up to 45% of computer resources...
0
 

Author Comment

by:holemania
ID: 23755973
Are you reading off an excel and than updating the database or of a dataset?  Originally my request was to read of an excel file looking for the item id, and compare it to the databse.  If it matches than only update that item with the updated pricing.

Is it possible you post your code?
0
 
LVL 21

Expert Comment

by:silemone
ID: 23756396
ok...

Here's what's goin on here...I open an excel file that has 3 different data collections...I find first empty line...count it...it there is another, that means end of file..otherwise i reset counter back to 0 because it means we have found our second table (separated by empty line) -- this code  while (flag != 2){....} checks to see if to empty lines...i called it flag...should have more meaningful name like Emptylines...

I skip header files also as seen  
                              if (RecordType == "RECORD_TYPE")
                              {
                                   index++;
                                   rowIndex = index;
                                   continue;
                              }

Everything else is just Microsoft...Excel library

Oops...i do have a Find directory for file...which locates directory for the file....i.e.  user can't just type any filename/filepath to the EXCEL file...they have to use browse function and it opens a "Find File" Dialog that only looks for .xls, .xlsx. and all files

my beginning simple interface looks like

EnterFileNameLabel:     filePathNameTextBox<--txtFilePath        BrowseButton <------btnBrowse

                                         ImportButton  <--- btnImport

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;
 
 
namespace WindowsApplication1
{
     public partial class Form1 : Form
     {
          Microsoft.Office.Interop.Excel.ApplicationClass excel;
          Microsoft.Office.Interop.Excel.Workbook excelWorkBook;
          Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet;
          string excelFile = @"C:\DESTINY.xls";
 
 
          public Form1()
          {
               InitializeComponent();
               try
               {
 
               }
               catch (Exception ex)
               {
                    MessageBox.Show(ex.StackTrace.ToString());
               }
          }
 
 
 
 
 
          public void loadExcelWorkSheets()
          {
               excelFile = txtFilePath.Text.ToString();
               excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
               excelWorkBook = excel.Workbooks.Open(excelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
               excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.ActiveSheet;
          }
 
 
          private void LoadExcelData()
          {
 
               //Variables Declaration
               OleDbConnection objOleConn = null;
               OleDbCommand objOleComm = null;
               OleDbDataAdapter SQLAdapter = null;               
               string strConnectionString = String.Empty;
               string strDB = String.Empty;
               
              
               //Declaration of Variables
               strDB = @"C:\Documents and Settings\sospina\My Documents\JimmyProjectDB.accdb";
               loadExcelWorkSheets();
               int index = 1;
               object rowIndex = 1;
               string RecordType = String.Empty;
               int flag = 0;
               try
               {
 
                    if ((strDB.Trim().EndsWith(".accdb")))
                    {
                         strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;", strDB);
                    }
                    else
                         if ((strDB.Trim().EndsWith(".mdb")))
                         {
                              strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;", strDB);
                         }
                    objOleConn = new System.Data.OleDb.OleDbConnection(strConnectionString);
                    objOleConn.Open();
                    //Delete all Data from first Table before populating
                    SQLAdapter = new OleDbDataAdapter();
                    string sqlStr = "Delete From GroupBookingData";
                    objOleComm = new OleDbCommand(sqlStr, objOleConn);
                    SQLAdapter.DeleteCommand = objOleComm;
                    objOleComm.ExecuteNonQuery();
 
                    //Delete all Data from second Table before populating
                    SQLAdapter = new OleDbDataAdapter();
                    sqlStr = "Delete From PassengerData";
                    objOleComm = new OleDbCommand(sqlStr, objOleConn);
                    SQLAdapter.DeleteCommand = objOleComm;
                    objOleComm.ExecuteNonQuery();
 
                    //Delete all Data from third Table before populating
                    SQLAdapter = new OleDbDataAdapter();
                    sqlStr = "Delete From MealsRequestsCreditsData";
                    objOleComm = new OleDbCommand(sqlStr, objOleConn);
                    SQLAdapter.DeleteCommand = objOleComm;
                    objOleComm.ExecuteNonQuery();
                    objOleConn.Close();                    
                    
                    RecordType = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 1]).Value2.ToString();
 
 
 
                    while (flag != 2)
                    {
                         if (((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 1]).Value2 != null)
                         {
                              flag = 0;
                              // Read the Cells to get the required value.                          
                              RecordType = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 1]).Value2.ToString();
                              if (RecordType == "RECORD_TYPE")
                              {
                                   index++;
                                   rowIndex = index;
                                   continue;
                              }
                              if (RecordType == "01")
                              {
                                   string BookingID = convertLastDigits(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 2]).Value2.ToString());
                                   string SailingDate = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 3]).Value2.ToString();
                                   Int32 CruiseLength = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 4]).Value2.ToString());
                                   string CabinCategory = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 5]).Value2.ToString();
                                   string Cabin = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 6]).Value2.ToString();
                                   Int32 PAX = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 7]).Value2.ToString());
                                   string ArrivalGate = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 8]).Value2.ToString();
                                   string DepartureGate = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 9]).Value2.ToString();
                                   string Prebooked = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 10]).Value2.ToString();
 
 
 
 
                                   SQLAdapter = new OleDbDataAdapter();
                                   objOleComm.Connection.Open();
 
                                   //sqlStr = "Insert Into GroupBookingData(RecordType, BookingID)Values('1', @BookingID)";
 
 
                                   /*sqlStr = "Insert Into GroupBookingData"
                                   + "([RecordType],[BookingID], [SailingDate], [CruiseLength], [CabinCategory], [CabinNumber], [Pax], [Arrival], [Departure], [Prebooked])"
                                   + "Values(@RecordType,@BookingID, '12/2/2009', @CruiseLength, @CabinCategory, @CabinNumber, @Pax, @Arrival, @Departure, @Prebooked)";
                                   */
                                   sqlStr = "Insert Into GroupBookingData"
                                   + "([RecordType],[BookingID], [SailingDate], [CruiseLength], [CabinCategory], [CabinNumber],[Pax], [Arrival], [Departure], [Prebooked])"
                                   + "Values(@RecordType,@BookingID, '12/12/2009', @CruiseLength, @CabinCategory, @CabinNumber, @Pax, @Arrival, @Departure, @Prebooked) ";
                                   objOleComm = new OleDbCommand(sqlStr, objOleConn);
                                   objOleComm.Parameters.AddWithValue("@RecordType", Convert.ToInt32(RecordType));
                                   objOleComm.Parameters.AddWithValue("@BookingID", BookingID);
                                   //objOleComm.Parameters.AddWithValue("@SailingDate", SailingDate);
                                   objOleComm.Parameters.AddWithValue("@CruiseLength", CruiseLength);
                                   objOleComm.Parameters.AddWithValue("@CabinCategory", CabinCategory);
                                   objOleComm.Parameters.AddWithValue("@CabinNumber", Cabin);
                                   objOleComm.Parameters.AddWithValue("@Pax", PAX);
                                   objOleComm.Parameters.AddWithValue("@Arrival", ArrivalGate);
                                   objOleComm.Parameters.AddWithValue("@Departure", DepartureGate);
                                   objOleComm.Parameters.AddWithValue("@Prebooked", convertPreBoooked(Prebooked));                                   
                                   SQLAdapter.SelectCommand = objOleComm;
                                   objOleComm.ExecuteNonQuery();
                                   objOleComm.Connection.Close();
                                   index++;
                                   rowIndex = index;
                                   continue;
                              }
                              else
                                   if (RecordType == "02")
                                   {
 
                                        string BookingID = convertLastDigits(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 2]).Value2.ToString());
                                        string LastName = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 3]).Value2.ToString();
                                        string FirstName = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 4]).Value2.ToString();
                                        string Greeting = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 5]).Value2.ToString();
                                        string Sex = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 6]).Value2.ToString();
                                        string CruiseType = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 7]).Value2.ToString();
                                        string Age = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 8]).Value2.ToString();
                                        string DiningRequest = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 9]).Value2.ToString();
                                        string AllInclusive = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 10]).Value2.ToString();
                                        Int32 OnboardCredit = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 11]).Value2.ToString());
 
                                        SQLAdapter = new OleDbDataAdapter();
                                        objOleComm.Connection.Open();
 
                                        //sqlStr = "Insert Into PassengerData(RecordType)Values('2')";
 
 
                                        sqlStr = "Insert Into GroupBookingData"
                                        +       "([RecordType],[BookingID],  [FirstName], [Greeting], [Sex], [Type], [DiningRequest], [AllInclusive], [OnboardCredit])"
                                        + "Values(@RecordType,@BookingID, @FirstName, @Greeting, @Sex, @Type, @DiningRequest, @AllInclusive, @OnboardCredit)";
                                        objOleComm = new OleDbCommand(sqlStr, objOleConn);
                                        objOleComm.Parameters.AddWithValue("@RecordType", Convert.ToInt32(RecordType));
                                        objOleComm.Parameters.AddWithValue("@BookingID", BookingID);
                                        //objOleComm.Parameters.AddWithValue("@LastName", LastName);
                                        objOleComm.Parameters.AddWithValue("@FirstName", FirstName);
                                        objOleComm.Parameters.AddWithValue("@Greeting", Greeting);
                                        objOleComm.Parameters.AddWithValue("@Sex", Sex);
                                        objOleComm.Parameters.AddWithValue("@Type", CruiseType);
                                        objOleComm.Parameters.AddWithValue("@Arrival", Age);
                                        objOleComm.Parameters.AddWithValue("@DiningRequest", DiningRequest);
                                        objOleComm.Parameters.AddWithValue("@AllInclusive", AllInclusive);
                                        objOleComm.Parameters.AddWithValue("@OnboardCredit", OnboardCredit);
                                        SQLAdapter.SelectCommand = objOleComm;
                                        objOleComm.ExecuteNonQuery();
                                        objOleComm.Connection.Close();
                                        index++;
                                        rowIndex = index;
                                        continue;
                                   }
                                   else
                                        if (RecordType == "03")
                                        {
                                             string BookingID = convertLastDigits(((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 2]).Value2.ToString());
                                             string Comments = ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[rowIndex, 3]).Value2.ToString();
 
                                             SQLAdapter = new OleDbDataAdapter();
                                             objOleComm.Connection.Open();
 
                                             //sqlStr = "Insert Into MealsRequestsCreditsData(RecordType)Values('3')";
                                             sqlStr = "Insert Into GroupBookingData"
                                             +       "([RecordType],[BookingID], [Comments])"
                                             + "Values(@RecordType,@BookingID, @Comments)";
                                             objOleComm = new OleDbCommand(sqlStr, objOleConn);
                                             objOleComm.Parameters.AddWithValue("@RecordType", Convert.ToInt32(RecordType));
                                             objOleComm.Parameters.AddWithValue("@BookingID", BookingID);
                                             objOleComm.Parameters.AddWithValue("@Comments", Comments);                                                                         
                                             SQLAdapter.SelectCommand = objOleComm;
                                             objOleComm.ExecuteNonQuery();
                                             objOleComm.Connection.Close();
                                             index++;
                                             rowIndex = index;
                                             continue;
                                        }
 
 
                              //Console.WriteLine("Name : {0},{1} ", RecordType, BookingID);
                              index++;
                              rowIndex = index;
                         }
                         else
                         {
                              flag++;
                              index++;
                              rowIndex = index;
                              continue;
                         }
 
                    }
                    
               }
               catch (Exception ex)
               {
                    // Log the exception and quit...
 
                    MessageBox.Show(ex.StackTrace.ToString());
 
               }
               finally
               {
                    if (objOleConn.State == ConnectionState.Open)                    
                         objOleConn.Close();                    
                    objOleConn.Dispose();
                    objOleComm.Dispose();
                    SQLAdapter.Dispose();                   
                    excel = null;
                    excelWorkBook = null;
                    excelWorkSheet = null;                                      
 
               }
          }
         
 
          private void btnImport_Click(object sender, EventArgs e)
          {
               if (txtFilePath.Text == String.Empty)
               {
                    lblError.Text = "You must enter a file location.";
               }
               else
               {
                    
                    LoadExcelData();
               }
          }
 
          private void btnBrowse_Click(object sender, EventArgs e)         
          {
              OpenFileDialog openFileDialog1 = new OpenFileDialog();              
              openFileDialog1.InitialDirectory = "c:\\" ;
              openFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel 2007 (*.xlsx)|*.xlsx|All files (*.*)|*.*" ;              
              openFileDialog1.FilterIndex = 1 ;
              openFileDialog1.Title = "Find File";
             
              openFileDialog1.RestoreDirectory = true ;
 
              if(openFileDialog1.ShowDialog() == DialogResult.OK)
              {
                  txtFilePath.Text = openFileDialog1.FileName;                  
                  
                   lblError.Text = String.Empty;
              }
          }
 
 
          //DB only wants 9 characters...getting file with 10, so converting last digit to alphabet
          public string convertLastDigits(string x)
          {
               string prefix = x.Substring(0, x.Length-2);
               
               x = x.Trim().Substring(x.Length - 2, 2);
 
               switch (x)
               {
                    case "01": x = "a"; break;
                    case "02": x = "b"; break;
                    case "03": x = "c"; break;
                    case "04": x = "d"; break;
                    case "05": x = "e"; break;
                    case "06": x = "f"; break;
                    case "07": x = "g"; break;
                    case "08": x = "h"; break;
                    case "09": x = "i"; break;
                    case "10": x = "j"; break;
                    case "11": x = "l"; break;
                    case "12": x = "m"; break;
                    case "13": x = "n"; break;
                    case "14": x = "o"; break;
                    default: x = "p"; break;
               }
               return prefix + x;
              
          }
 
          public string recoverLastDigits(string x)
          {
               string prefix = x.Substring(0, x.Length - 1);
               x = x.Trim().Substring(x.Length - 1, 1);
 
               switch (x)
               {
                    case "a": x = "01"; break;
                    case "b": x = "02" ; break;
                    case "c": x = "03" ; break;
                    case "d": x = "04" ; break;
                    case "e": x = "05" ; break;
                    case "f": x = "06" ; break;
                    case "g": x = "07" ; break;
                    case "h": x = "08" ; break;
                    case "i": x = "09" ; break;
                    case "j": x = "10" ; break;
                    case "l": x = "11" ; break;
                    case "m": x = "12" ; break;
                    case "n": x = "13" ; break;
                    case "o": x = "14" ; break;
                    default: x = "15"; break;
 
 
               }
               return prefix + x;
          }
          
 
          //using yes/no field in access database...file comes with Y and N so converting to digits
          public int convertPreBoooked(string x)
          {
               if (x.Trim().ToUpper() == "Y")
                    return -1;
               else
                    return 0;
          }
     }
}

Open in new window

0
 
LVL 21

Expert Comment

by:silemone
ID: 23756851
and yes, there are definite flaws in my code like 1, i should used stored queries (SPROCs for MSSQL) but I don't see them being as powerful as SPROCS...but I will eventually inject them...
0
 

Author Comment

by:holemania
ID: 23774445
Thanks silemone.  I will analyze your code and see if that'll work for me.
0
 

Author Comment

by:holemania
ID: 23797140
After doing some research I found that if all I wanted to do is dump data from Excel into SQL database, it be more efficient to just use SQLBulkCopy to accomplish this.

Below is what I am trying to work with, but I am getting an error at excelstrCon.Open() in regard to ISAM not being installed.  Looking into why at the moment.
 	'Connection string
        Dim excelCon As String
        Dim conString As String = GetConnectionString()
        Dim file As String = "C:\Parts.xls"
        Dim wrkSht As String = "PART"
 
        'Dim excelCon As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; ", _
        '       "Data Source={0};Extended Properties={1};", file, """Excel 8.0;HDR=YES;IMEX=1""")
 
        excelCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=8.0;"
 
        Dim excelstrCon As New OleDb.OleDbConnection(excelCon)
        excelstrCon.Open()
 
        'Create objects and get data
        Dim cmd As New OleDb.OleDbCommand("SELECT [COLUMN2], [COLUMN23] FROM " & wrkSht, excelstrCon)
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
 
        'open the destination data
        Dim dbCon As SqlConnection = New SqlConnection(conString)
        dbCon.Open()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(dbCon)
 
        bulkCopy.DestinationTableName = "PART_COST"
        bulkCopy.WriteToServer(reader)
        reader.Close()

Open in new window

0
 

Accepted Solution

by:
holemania earned 0 total points
ID: 23806485
The code below works well and very fast.  Transfer about 1500 records in seconds.  Basically dump selected field and dump into a table.
'Connection string
        Dim excelCon As String
        Dim conString As String = GetConnectionString()
        Dim file As String = "C:\Parts.xls"
 
        excelCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
 
        Dim excelstrCon As New OleDb.OleDbConnection(excelCon)
        excelstrCon.Open()
 
        'Create objects and get data
        Dim cmd As New OleDb.OleDbCommand("SELECT [PART ID], [TOTAL COST] FROM [PART$]", excelstrCon)
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
 
        'open the destination data
        Dim dbCon As SqlConnection = New SqlConnection(conString)
        dbCon.Open()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(dbCon)
 
        bulkCopy.DestinationTableName = "PART_COST"
        bulkCopy.WriteToServer(reader)
        reader.Close()

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question