2 Replies - 6547 Views - Last Post: 29 March 2012 - 02:26 AM Rate Topic: -----

#1 minckle  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 04-September 09

Importing an excel file into SQL Server

Posted 11 September 2009 - 04:25 AM

Im having strange problems when importing data from an excel file into a sql table

Everything seems to imports ok until i look at the data in more detail, this is when I notice its imported the data but in the wrong order.

for some reason when I look at the db table all records are in the wrong order, and strangely enough it seems to be a different order every time I import things.

Is there a way to force the routine to import the data in the same order as the it is in the excel file?, because at the minute it seems to have a mind of its own

any help / advice will be appreciated as always

thanks

My code is below:

    Public Function ReadExcelFile(ByVal sFileName As String) As Boolean

        Dim oSQL As New clsSQLHolder
        Dim excelCon As String
        Dim excelstrCon As OleDb.OleDbConnection
        Dim reader As OleDb.OleDbDataReader
        Dim cmd As New OleDb.OleDbCommand

        Try
            Try
                excelCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

                excelstrCon = New OleDb.OleDbConnection(excelCon)
                excelstrCon.Open()

                'Create objects and get data
                cmd = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", excelstrCon)
                reader = cmd.ExecuteReader

                'open the destination data
                Dim dbCon As SqlConnection = New SqlConnection(sConnectionString)

                dbCon.Open()
                Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(dbCon)

                bulkCopy.DestinationTableName = "tblFocusTemp"
                bulkCopy.WriteToServer(reader)

            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            End Try
        Finally
            reader.Close()
            excelstrCon.Close()
        End Try

    End Function

This post has been edited by AdamSpeight2008: 29 March 2012 - 04:48 AM
Reason for edit:: Please use the CODE tags


Is This A Good Question/Topic? 0
  • +

Replies To: Importing an excel file into SQL Server

#2 Unorthodox  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 02-October 09

Re: Importing an excel file into SQL Server

Posted 03 October 2009 - 12:18 AM

If the data in your Excel file is sorted in any way, then add ORDER BY to your select statement. The other alternative is to insert using loops, where you insert one row at a time, which will maintain the same ordering that you have in your Excel file.
Was This Post Helpful? 0
  • +
  • -

#3 hyukjoo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-March 12

Re: Importing an excel file into SQL Server

Posted 29 March 2012 - 02:26 AM

I have exactly same issue with this.
But I think it seems not releated with ordering of Excel file, because I didn't order Excel, but sql table always shows incorrect ordering.
BTW, it doesn't occur all machines, all excels, but specific excel file on the specific machine.


@minckle,
Did you solve the problem?
If then could you please share what solution is?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1