Post reply

Import Excel Worksheet Error

  • November 2, 2006 at 8:30 am

    #367785

    I have come across an interesting situation importing from an excel spreadsheet.  We have a customer that places and excel file in an ftp folder which we upload to the server via dts....

    It is a simple import, however, the last load gave us interesting results.  The person had sorted the data so that the first 5 columns of data were populated, the next two were null values and the last column was populated, looking like this:

    EMPLOYEE #CRAFT CODEStart DatePPE DateREG HOURSPREM. PAY TYPEPREM. PAY HRSWO #
    1010400JNT-0710/28/0610/31/0621086

    There were about 150 records with null values in the [pay type] and [prem pay hrs] fields prior to a record contaning data in these fields.

    When the data was loaded, the uploaded table had null values in the all the records [prem pay hrs] field, when the data clearly did have values.

    I have found that if there is a record that has values in the first 25 records imported, the upload works perfectly.

    Has anyone else come across this?  Is this a known bug with sqlserver 2k?

     

     

     

     

     

     

  • November 2, 2006 at 10:20 pm

    #669686

    Nope, not a bug. It's also not SQL Server's fault. It is the behavior, by design, of the ISAM used to connect to Excel and standard delimited text files. In general, Excel columns aren't typed, so the type is derived based on the first 8 rows in the column. If there is nothing but Nulls in the first 8 rows, it thinks that the entire row is Null.

    There is an excellent article on SQLDTS.com that explains your options on how to handle this.

    http://www.sqldts.com/default.aspx?254

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • November 3, 2006 at 2:07 am

    #669709

    This issue has been on the agenda before.

    I think the simplest way to get rid of the design problem importing Excel files with

    DTS is to save the Excelfile as a text file and import that file instead.

     

  • November 3, 2006 at 6:48 am

    #669763

    Thanks Robert...I figured it was something like that.

    Gosta, I did exactly what you suggested...they have to provide a csv file.

     

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply