Importing an excel file into SQL Server

[minckle]

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

 

[Tom]

Can you clarify the problem in more detail, are the excel fields being imported into the wrong column fields in your table or are just the full records being displayed in a different order in your database table?

Records being in a different order in your database table may be as simple as its being sorted on an expected column.

 

[minckle]

hi
thanks for this

the records are being imported in the wrong order.

my problem is that there is no obviously column in the excel file to add a sort to

Im currently trying to work out how to add a new column to my excel file, file it with a record number, then i can use this column to add a sort and all should be ok.

If you have any advice on doing this please feel free to chip in
thanks again

 

[Tom]

I have a couple of suggestions, first create a table in your database that matches the columns in your excel and include an aditional column that is an identity seed. If the identity see is set as primary key, you should then see the results automatically sorted by the order that they were inserted. However I dislike using identity seeds as primary keys since there not actually the column or columns that make the record unique for the table. I usually add a identity seed just as a row counter and when needed to see in proper order, just include that in the Order By section of your query.

I do alot of data imports myself including excel file(s) imports. Although it is fast to use bulk copy to send the data directly from the file(s) to the database, it doesn't allow for prior validation, error checking, manipulation, formatting, additional fields of info etc.

I usually import the excel file(s) into an vb dataset/datatable first, do all my error checking, formatting, addition of extra info etc and after that use sqlBulkCopy to send all the info from the dataset to the database. This also gives extra advantages of being able to use transactions, increase the connection pooling for the import, set the batch size according to the amount of records, handles filling out proper values in multiple tables with foreign key relations, has detailed error handling etc.