none
Import data from Excel (custom sheet name)

    Question

  • Hi! I need to setup daily import of data from excel file into SQL database - preferrably through Visual Studio project (Visual Basic 2008). The problem is that I wouldn't know the name of the excel sheet. The excel file is created daily from different software and depending on amount of data on certain day - could have the sheet named as "Rows 1 to 5000" or "Rows 1 to 15582" and so on - therefore can't use 'Sheet1'. What is the easiest way to import data into SQL table? I'm still learning SQL and Visual Basic 2008 (have a programming background). I apperciate your help!
    Friday, July 24, 2009 1:11 PM

Answers

  • The code iterates through each Worksheet in the Workbook (retrieved by GetSchema) and checks to see if there is a $ character at the end of the Worksheet name. When using data access Excel Worksheets are identified that way. Sometimes you will find two instances of the same Worksheet: One with a $ character at the end and one without. You just want to grab the one with the $ character.

    Another way to check that is with the following code:

                If Right(TableRow.Item("TABLE_NAME").ToString, 1) = "$" Then
    

    The Console.Writeline statement just dumps each Worksheet name to the Output window. You probably want to replace that code and add each Worksheet name to a Collection or Array. Then you can run your export code for each Worksheet name in the Collection or Array.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Alla2552 Friday, July 31, 2009 7:56 PM
    • Unmarked as answer by Alla2552 Monday, October 05, 2009 6:44 PM
    • Marked as answer by Alla2552 Monday, October 05, 2009 7:50 PM
    Friday, July 31, 2009 7:09 PM

All replies

  • Are you sure you need a program? SQL has features to import data from many formats, including Excel. They were called DTS before, now they have a weird name, but it amounts to the same.

    If you really need to use a program, look into the Interop.Excel namespace.
    Friday, July 24, 2009 1:17 PM
  • Thank you. I'd like to do the import in Visual Studio (VB)  - can't use any manual wizards. I tried to use ExecuteReader from VS but it takes very long time to connect and execute. After 15 min I had to stop the process. I figured out the part of getting the name of the sheet prior to importing data. Still need help on import part. Thank you!
    Friday, July 24, 2009 5:32 PM
  • Here is a good starting point to learn how to use Interop.Excel. Before you go down that road though, I must insist on DTS's. There is a way to run DTS packages without the manual wizard. The wizard is only here to help you create your DTS package. Once it's created, you can rerun it as often as you want at the click of a button.

    From personal experience, DTS performance has always been one of the highest when it comes to data transfer. They are a little clunky to maintain, (older versions were downright hellish to debug), but they've come a long way.
    Friday, July 24, 2009 5:44 PM
  • Have a look at this article: http://support.microsoft.com/default.aspx/kb/302094

    You can read the data from Excel and then write a small program to write the data read into your database.
    Ashray Lavsi
    If my post answers your Question, don't forget to "Mark it as Answer"
    Friday, July 24, 2009 5:51 PM
  • Forget my first link, Ashray's link is much better.
    Friday, July 24, 2009 5:53 PM
  • I checked both links - thank you. The problem that I have - writing the data from excel into SQL table. The link shows how to read data from excel and populate in VS, but not about inserting the data from excel into existing table in SQL. I also read that Microsoft is retiring DTS.. We are using SQL Server 2008. I appreciate your help!
    Friday, July 24, 2009 5:57 PM
  • Yes DTS is the old name for the feature. I can't for the life of me remember what the new name is, but SQL 2k5 had basically what amounted to DTS. I'll have to look it up.

    To insert data in a database from a .Net application, a commonly used technique is with DataSet and DataAdaptor. Visual Studio has a slew of designer tools to help you create all the classes you need quickly and easily. Add a database to your project, then a DataSet. From the Server Explorer, pull your tables onto the designer of the dataset, and most of the work will be done for you already.
    Friday, July 24, 2009 6:03 PM
  • you can create an oledb connection to your excel file, use a datareader to read the records.  create a connection to your sql database, and while the reader is reading you can insert the records into sql with new command.  this can all be done in your vb application.  you don't need all the other objects such as a dataset, datatable, dataadapter, etc...  a dataadapter uses a datareader to fill a datatable anyway so you can bypass all that and just read and insert.  should be fairly quick as well.  if you do a search you can find a connectoin string sample for excel files.  i also have a tool called connection maker on my website which will connect to your excel file and create the connection string for you.  version 3.0 will connect to excel 2007. 

    also, you can use a tool called spreadsheetgear for .net to open an excel file in memory without the need for excel.  you can use it to read the table (sheet) name/names.  this would be much cleaner than automating excel.  there is a free version in your vb express registration benefits.  it is a very handy tool with many uses.

    you can also read the schema of the excel file using oledb to get the names.
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    Friday, July 24, 2009 7:24 PM
  • Well you Pull data from Excel using the First Link and then this link will give you a short tutorial in writing the data to a database:
    http://www.homeandlearn.co.uk/NET/nets12p1.html


    Although the link is related to writing data into an Access Database the basics remain the same.
    Friday, July 24, 2009 7:27 PM
  • I found it! The new DTS is called SSIS. Take a good look at it before you start programming everything yourself. It's pretty awesome.
    Friday, July 24, 2009 7:35 PM
  • Thank you. Do I have to register on your site in order to download the tool (ver 3)? Are there examples on how to use it? I searched for spreadsheetgear - it looks like it's free only for 30 days, correct? Thanks again
    Friday, July 24, 2009 7:35 PM
  • yes, you will have to register to download it.  no cost for registration.  when you use the tool it will be self explanatory what it does.  ver 3.0 does not have all the features in it yet.  i am still working on everything.  version 1 and 2 have more.

    spreadsheetgear offers a free version through your microsoft benefits when you regsiter vb express.  you can register vb express and when they send the registration email there will be a link to your benefits.  if you don't see spreadsheetgear in your benefits you may need to register again.  this version is completely free and will not expire.  i have been using it for several years.  i do have a full version but the free version i still use in several applications.  i have code samples for it on my site as well.  and in the tools page if you download the time tracker tool, it has full source code and will show examples to use spreadsheetgear to open files, etc...
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    Friday, July 24, 2009 7:57 PM
  • Thank you All for your friendly help, glad to see your active discussion and good suggestions.


    Hi Alla,

    Welcome to MSDN forums!

    Based on Jeff’s idea, here are some tutorial & code sample for you to check "Read Excel and Write SQL Server database", which are helpful to you.

    1) Store each Array element to Excel in consecutive cells via Excel Automation
    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/1a41f104-73ff-4de4-9002-687b5243ba94/ 

    2) Sum those consecutive cells horizontally or vertically via Excel Automation
    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/6cbe0a15-4e21-4bd1-aad9-ea364443d3f4/ 

    3) Retrieve all WorkSheet names in Excel file via Excel Automation and Select/Query specific WorkSheet via Microsoft.Jet.OLEDB.4.0 provider.
    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/ce4bbe9c-7d13-44c5-a26c-4b84912a51f3/  

    4) How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/


    Best regards,
    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 28, 2009 11:04 AM
  • Thank you, Martin. I'm half way there but stil have a problem. Please see below.
    I'm trying to insert data from excel file into SQL using Visual Basic project. The problem is that even if I don't have any Null values in excel file - I still get the "conversion from type DBNull to type String" error and I don't understand why. I specifically left 2 rows in excel without any Nulls and during debug watched every field - had no Null values, yet on the "ExecuteScalar" command I'm getting the conversion error. I tried to insert just 4 fields (all with values) as a test - still getting this error. What am I missing? About zero string - I just added this check to see if it resolve the error and it didn't help. Since EmplID field is a string - it's OK to have empty string in that field vs Null. It seems that something else causes the error.. 
    Just discovered - that it actually inserts 1 line of data and then gives me a conversion error. Please see the code below. Is something wrong with it?

    Protected Function ProcessSiteImportFile(ByVal SiteData As DataSet) As String

    Dim sqlConnectionString As String

            sqlConnectionString = "actual connection string"

            Dim connection As SqlConnection = New SqlConnection(sqlConnectionString)

            Dim newSiteID As String

            Dim command As SqlCommand = New SqlCommand("INSERT INTO dbo.tblName ([Empl ID],[Department ID - Job Dta],[Full/Part Time - Job Dta], [Job Code - Job Dta]) " _

                           & " VALUES (@EmplID,@Dept,@Status1,@JobCode);SELECT SCOPE_IDENTITY()", connection)

     

     

            Try

                For i As Integer = 0 To SiteData.Tables(0).Rows.Count - 1

                    connection.Open()

                    If IsDBNull(SiteData.Tables(0).Rows(i).Item("Empl ID")) Then

                        command.Parameters.AddWithValue("@EmplID", String.Empty)

                    Else

                        command.Parameters.AddWithValue("@EmplID", SiteData.Tables(0).Rows(i).Item("Empl ID"))

                    End If

                    If IsDBNull(SiteData.Tables(0).Rows(i).Item("Department ID - Job Dta")) Then

                        command.Parameters.AddWithValue("@Dept", String.Empty)

                    Else

                        command.Parameters.AddWithValue("@Dept", SiteData.Tables(0).Rows(i).Item("Department ID - Job Dta"))

                    End If

                    If IsDBNull(SiteData.Tables(0).Rows(i).Item("Full/Part Time - Job Dta")) Then

                        command.Parameters.AddWithValue("@Status1", String.Empty)

                    Else

                        command.Parameters.AddWithValue("@Status1", SiteData.Tables(0).Rows(i).Item("Full/Part Time - Job Dta"))

                    End If

                    If IsDBNull(SiteData.Tables(0).Rows(i).Item("Job Code - Job Dta")) Then

                        command.Parameters.AddWithValue("@JobCode", String.Empty)

                    Else

                        command.Parameters.AddWithValue("@JobCode", SiteData.Tables(0).Rows(i).Item("Job Code - Job Dta"))

                    End If

                    newSiteID = command.ExecuteScalar()

                    command.Parameters.Clear()

                    connection.Close()

                Next

                command.Dispose()

                Return ""

            Catch ex As Exception

                Return ex.Message + "<br />" + ex.StackTrace

            Finally

                If connection.State = ConnectionState.Open Then

                    connection.Close()

                End If

            End Try
    End Function

    Tuesday, July 28, 2009 2:08 PM
  • BTW, this is relatively easy to do using the SQLBulkCopy Class.

    Also, you can grab Excel Workbook names using GetOleDbSchemaTable:

            Using DatabaseConnection As New System.Data.OleDb.OleDbConnection()

                Dim SchemaTable As DataTable

                DatabaseConnection.Open()

                'Retrieve schema information about Tables.
                SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                           New Object() {Nothing, Nothing, Nothing})

                Dim RowCount As Int32
                For RowCount = 0 To SchemaTable.Rows.Count - 1
                    Console.WriteLine(SchemaTable.Rows(RowCount)!TABLE_NAME.ToString)
                Next RowCount

                DataGrid1.DataSource = SchemaTable

                DatabaseConnection.Close()

            End Using


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, July 28, 2009 2:50 PM
  • Thank you for the idea. Unfortunately SQLBulkCopy would have involved giving users ddladmin priviledges which our network security won't allow.
    I figured out the resolution - when I put command.ExecuteNonQuery - everything started working fine. My only complain - it takes about 1 minute to read 15K records from excel and insert into SQL. Does anybody knows how to improve the speed? Thanks again for all suggestions.
    Tuesday, July 28, 2009 6:02 PM
  • Yes: use SSIS ;)

    Tuesday, July 28, 2009 6:04 PM
  • I would have if I had the examples on how to program it in VB. I couldn't find anything other than manually run it from within SQL. Seems like there is not enough information on programming it - at least I wasn't able to find it... I know it's possible but don't know how.
    Tuesday, July 28, 2009 6:09 PM
  • Did you check this link? (First google result of "Execute SSIS package programmatically")
    Tuesday, July 28, 2009 6:13 PM
  • Thank you for the idea. Unfortunately SQLBulkCopy would have involved giving users ddladmin priviledges which our network security won't allow.
    I figured out the resolution - when I put command.ExecuteNonQuery - everything started working fine. My only complain - it takes about 1 minute to read 15K records from excel and insert into SQL. Does anybody knows how to improve the speed? Thanks again for all suggestions.

    Are you sure you're not thinking of Bulk Insert or bcp? The SQLBulkCopy Class just dumps from a DataReader or DataSet into an existing SQL Server table. It should be faster than the method you're using.

    You can also use a SQL directly (you might need to specify the column names if they are different) to perform the import directly:

            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                        "Data Source=" & "c:\Test Files\Book20.xls" & ";" & _
                                                                        "Extended Properties=""Excel 8.0;HDR=YES""")
    
            ExcelConnection.Open()
    
            'Existing table
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Orders2] SELECT * FROM [Orders$];", ExcelConnection)
    
            ExcelCommand.ExecuteNonQuery()
            ExcelConnection.Close()
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, July 28, 2009 6:50 PM
  • I'm pretty sure BulkCopy is just a managed wrapper around bcp.

    Tuesday, July 28, 2009 6:52 PM
  • That's what was mentioned in the example of using SQLBulkCopy - that "permissions need to be equivalent to db_ddladmin or db_owner". I assumed that it was accurate statement. I didn't actually tested it because I knew that our network guys won't change the permissions for the user. Let me know if that's not accurate statement.
    Thank you for the example above - I will test it as well.
    Tuesday, July 28, 2009 6:55 PM
  • I'm seeing contradictory information and I don't know which one is correct. Another link indicates that only SELECT and INSERT are required. 

    I suppose it will have to be put to the test.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, July 28, 2009 7:55 PM
  • Hi Paul. Just wanted to thank you for the alternative solution - using SQL directly to insert Excel data. It's a lot easier and slightly faster. Finally had a chance to test it. The prior method that I was using was more complicated but did have advantage of formatting the values prior to inserting it. But it also was "blocking" excel from multiple access. For example - it'll run fine the first time, but the second time it would get locked up. When I check excel file - it'll tell me that it's already in use and available for reading only (even after 1 st run is finished). I do have .close and .dispose in place. Not sure if there is something else I'm missing? Seems that it's not releasing the file completely. Thanks again!
    Wednesday, July 29, 2009 7:01 PM
  • The post in this thread is excellent... btw... I just found out something interesting about .xlsx files (ie. excel files from 2007 excel.) and I though I might bring it up... change the extention from xslx to .zip and open the file and see what happens... :)


    Kenneth
    Wednesday, July 29, 2009 9:00 PM
  • Looks like now I have the same problem using Paul's code from Tuesday, July 28, 2009 6:50 PM - when I run code to import 1 excel file - works fine; now I added code to import 2-nd file - it locks up. I'm not sure if the problem is with the code that is determines sheet name or the code that actually imports the data in. It blocks the file as read only and locks up. I don't actually change anything in excel file and it'd be enough if the settings is read only - yet it doesn't work at all. Is there a way to pass some type of code indicating that I need read access only to the file? We might need to import data more than once per day and looks like the code doesn't release excel files. Below there is a code for getting the name of the sheet in excel. I tried to set everything to "nothing" but may be somebody knows what is missing? Please help!

     

    Dim SecFileName As String = "excel file name"

     

    Dim oExcel As Object = CreateObject("Excel.Application")

     

    Dim oBook As Object = oExcel.Workbooks.Open(SecFileName)

     

    Dim oSheet As Object = oBook.Worksheets(1)

     

    Dim SheetName As String

    SheetName = oSheet.Name

     

    oExcel.Quit()

    oExcel =

    Nothing

    oSheet =

    Nothing

    oBook =

    Nothing

     

    Dim ExcString As String

    ExcString =

    "select * from [" & SheetName & "$]"

    - after this code I used code from message above, supplying the sheet name (Tuesday, July 28, 2009 6:50 PM) 
    Thank you!
    Thursday, July 30, 2009 2:52 PM
  • Why don't you use data access to fetch the Worksheet names instead of automation? I think you might have problems if you try to open a connection to the file while it is open in Excel.

            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=C:\Test Files\Book10.xls;Extended Properties=Excel 8.0;"
            Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Connection.Open()
    
            Dim CollectionName As String = "Tables"
            Dim dt As DataTable = Connection.GetSchema(CollectionName)
    
            Dim TableRow As DataRow
            For Each TableRow In dt.Rows
                If TableRow.Item("TABLE_NAME").ToString.IndexOf("$") = TableRow.Item("TABLE_NAME").ToString.Length - 1 Then
                    Console.WriteLine(TableRow.Item("TABLE_NAME").ToString)
                End If
            Next
    
            Connection.Close()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 31, 2009 4:02 PM
  • Thank you, Paul. I'm getting an error on DataTable line - saying that it's "ambiguous, imported from the namespaces or types 'Microsoft.Office.Interop.Excel". What should I do?
    Friday, July 31, 2009 4:10 PM
  • Could you post the code you have and identify the line where the exception (error) is generated?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 31, 2009 4:57 PM
  • It's pretty much all your code now. I checked that I don't have references to DataTable anywhere else in the project. Thanks!
    Private Sub btnPullData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPullDataADP.Click
            
            Me.Cursor = Cursors.WaitCursor
    
            Me.TblChangesADPTableAdapter.QryClearChangesTable()
            Dim SecFileName As String = "\excel file path"
            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=C:\Test Files\Book10.xls;Extended Properties=Excel 8.0;"
            Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            Connection.Open()
    
            Dim CollectionName As String = "Tables"
            Dim dt As DataTable = Connection.GetSchema(CollectionName)
    -	ERROR POINTS to DataTable
            Dim TableRow As DataRow
            For Each TableRow In dt.Rows
                If TableRow.Item("TABLE_NAME&quot;).ToString.IndexOf("$") = TableRow.Item("TABLE_NAME").ToString.Length - 1 Then
                    Console.WriteLine(TableRow.Item("TABLE_NAME").ToString)
                End If
            Next
    
            Connection.Close()
    
            Dim ExcelConnection1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                            "Data Source=" & SecFileName & ";" & _
                                                                            "Extended Properties=""Excel 8.0;HDR=YES""")
            Try
    
                ExcelConnection1.Open()
                Dim ExcelCommand1 As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=…;Database=name;Trusted_Connection=yes].[tblChangesADP] SELECT * FROM [" + SheetName + "$]" + ";", ExcelConnection1)
                ExcelCommand1.ExecuteNonQuery()
    
            Catch ex As Exception
                Console.WriteLine(ex.Message)
                MessageBox.Show(ex.ToString)
            Finally
                ExcelConnection1.Close()
                ExcelConnection1.Dispose()
                ExcelConnection1 = Nothing
                MsgBox("Changes File imported successfully!")
            End Try
    End Sub
    
    Friday, July 31, 2009 5:30 PM
  • Just noticed - it also showing error on $ sign in the line, saying that $ character is invalid...

    IndexOf("

     

    $")

    Friday, July 31, 2009 5:33 PM
  • Somewhere in the same scope there is a DataTable class. I have a feeling it's in Microsoft.Office.Interop.Excel. Try fully qualifying the DataTable reference to see if that fixes all the problems:

    Dim dt As System.Data.DataTable = Connection.GetSchema(CollectionName)
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 31, 2009 5:51 PM
  • It removed this error - thanks, but still won't allow to run it - saying that $ in IndexOf("$") is invalid character. Also in this code - what variable would store actual sheet name? Thanks!
    Friday, July 31, 2009 6:02 PM
  • Sorry - I copied your code from the email and it changed the line of code and therefore was showing an error on $. Figured it out and fixed it. Looks like it's working. I hadn't tried to run it multiple times yet.
    I wanted to check with you - what does this line means?
     If TableRow.Item("TABLE_NAME").ToString.IndexOf("$") = TableRow.Item("TABLE_NAME").ToString.Length - 1
    It actually never finds it - just goes through the code once and never gets to Console.WriteLine part. Is it correct?
    Thanks again for all your help! Alla
    Friday, July 31, 2009 6:53 PM
  • The code iterates through each Worksheet in the Workbook (retrieved by GetSchema) and checks to see if there is a $ character at the end of the Worksheet name. When using data access Excel Worksheets are identified that way. Sometimes you will find two instances of the same Worksheet: One with a $ character at the end and one without. You just want to grab the one with the $ character.

    Another way to check that is with the following code:

                If Right(TableRow.Item("TABLE_NAME").ToString, 1) = "$" Then
    

    The Console.Writeline statement just dumps each Worksheet name to the Output window. You probably want to replace that code and add each Worksheet name to a Collection or Array. Then you can run your export code for each Worksheet name in the Collection or Array.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Alla2552 Friday, July 31, 2009 7:56 PM
    • Unmarked as answer by Alla2552 Monday, October 05, 2009 6:44 PM
    • Marked as answer by Alla2552 Monday, October 05, 2009 7:50 PM
    Friday, July 31, 2009 7:09 PM
  • Thank you so much, Paul! It's strange though that the code never finds the worksheet with "$", but I assigned to the variable the TableRow.Item("Table_name").ToString and passed it as a sheet name and it works anyway(?!). I also tried to run import twice in a row - works as well. I appreciate your help!!!
    Friday, July 31, 2009 7:55 PM
  • OK, cool. Just keep an eye out for duplicate Worksheet names though (one with a $ character) because you may need to work around that at some point.

    In any event, it should allow you to remove the Excel automation functionality which at times can be a real PITA.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 31, 2009 8:11 PM
  • I'm hoping that Paul will see this problem re-opened once again. I got a new problem with excel spreadsheet. The vendor had put an update this weekend (excel file is created by the vendor) and certain field in the excel doesn't show correctly during an import. Strangely enough - if I open excel - data looks correct, when import to SQL - shows wrong. The problem is with the date field. Majority of records have Null in that field and suddenly it started to come across as date 1/1/1900 if excel shows Null, or comes as Null - if excel shows actual date. I kept a copy of the excel file prior to upgrade (for testing) - it imports just fine. I can't see anything wrong in the excel file - the field has customized format as mm/dd/yyyy. If I import the same file to Access - it shows correctly. So this problem occurs only with import to SQL. I'm afraid that vendor will just dosmiss it - since it looks correct in excel. Is there anything I can do on import? Any special settings for the date field?
    Monday, October 05, 2009 6:43 PM
  • While you very well may get an answer - from Paul or someone else - you really should start a new thread with a new question because other people search threads for answers to their own issues and multiple questions throw off that searching process.  I know it makes sense in your mind to go with a thread that has some history of your project, but this is not a good enough reason to resurrect this thread. 

    In fact, the better option is to start a new thread and, if you need to, reference this thread as a link in the original post.  Please consider this.
    Monday, October 05, 2009 7:45 PM
  • OK - opening new one and closing this one..
    Monday, October 05, 2009 7:50 PM
  • Hello,

    with this Excel .NET library you can easily extract data from Excel file to DataTable and then use ADO.NET table adapters to send data to SQL database.

    Here is a sample Excel VB.NET code how to export Excel to DataTable :

    Dim ef As New ExcelFile
    Dim dataTable As New DataTable
    
    ' Depending on the format of the input file, you need to change this:
    dataTable.Columns.Add("FirstName", GetType(String))
    dataTable.Columns.Add("LastName", GetType(String))
    
    ' Load Excel file.
    ef.LoadXls("FileName.xls")
    
    ' Select the first worksheet from the file.
    Dim ws As ExcelWorksheet = ef.Worksheets(0)
    
    ' Extract the data from the worksheet to the DataTable.
    ' Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
    ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows(0), ws.Columns(0))

    Wednesday, June 23, 2010 9:04 AM
  • Hi CikaPero. 

    Thanks for offering an alternative approach to this issue.  The intent of this thread was to perform the action through native .NET code, and there are other such 3rd party products out there that do what this product can.  Still, its good to see what's out there.

    I have to say the free version appears to be quite limited (150 rows, 5 worksheets) and the priced version is pretty costly.  Personally, I'll stick with slugging it out in long-code form but hopefully someone else will see it as worth the cost.

    Thursday, June 24, 2010 12:11 AM