Import Excel process clearing certain fields

[lleemon]

Situation: Have an app that calls an external vendor to get an excel (.xls) document. I download it and then read it into a dataset to loop through and import into SQL Server database.

Issue: The .xls file has about 800 records but it's only importing about 480 records. I step through each row and find out that some rows have null values.
After spending some time I believe I figured out why this is happening. My first field is a SKU value. The first x rows have all numeric values. Then it gets to a SKU like '1234-SE' and this row doesn't have data. So it's like it takes on the field type of the first data type.

Question: Is there a way to order the data before I populate the dataset? I tried to do an order by but didn't work.

My current code that is not working as I want. **NOTE: I have tried 'ORDER BY SKU ASC' and didn't work as well.

Sub FillDataSet(ByVal sFile As String, ByVal sTab As String)

        Dim containsHDR As String = "Yes"  'Change to "No" if it doesn't
        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & _
                        sFile & "';Extended Properties='Excel 12.0;HDR=" & containsHDR & ";IMEX=1'"

        ''You must use the $ after the object you reference in the spreadsheet
        Dim myData As New OleDbDataAdapter("SELECT * FROM [" & sTab & "$] ORDER BY SKU DESC", strConn)
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataSet)
    End Sub

[lleemon]

Since the first row of data has a sku of '1234' I need to put a new before this that has a sku of something other then numeric values such as '1234-ABC'.

Is there a way to insert a row?

[Grimfort]

Have a quick read of this, note the part about Excel scanning the first 8 records to decide the data type and a possible work around.

[kareninstructor]

Have you tried IMEX=0 ?

[kareninstructor]

I have a demo VS2008 project that demos adding rows, second link down on the following page. Look at AddFormDemo.vb, cmdAddNewRow_Click event which creates a sheet if it does not exists then adds a row of data from TextBox controls.

http://kevininstructor.home.comcast....ctsDotNet.html

[lleemon]

IMEX=0 didn't work. I will checkout the form.