SQL Server Performance

Importing excel data to sql table

Discussion in 'SQL Server 2005 Integration Services' started by sqldev, Jul 18, 2007.

  1. sqldev New Member

    Hi,

    We would like to transfer data from Excel sheet to a table. In the source excel, a column contains integer data for the first 10 rows and it contains some alpha numeric values and numeric values again. When we try to import this data into our table, import process considers this column as an integer data type and rest of the values are stored as NULL data in the table. In the table, field type is set to nvarchar.

    We formatted the excel's data range to Text. We used both SSIS and Openrowset SQL statement to import data into the table. We are not allowed to change the excel column's datatype manually to our desired one, it determines automatically and not flexible enough to accommodate other datatype values.

    I have provided below the code snippet to create test table and openrowset sql statement to do data transfer.

    CREATE TABLE [dbo].[Test](
    [eno] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [ename] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [sal] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [sdate] [datetime] NULL
    ) ON [PRIMARY]


    select * into testTable FROM
    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:Test.xls;HDR=YES',
    'SELECT * FROM [Sheet1$]')

    In the excel sheet, give the following data,

    enoenamesalsdate
    abctest1139639
    atest2239640
    ctest3a39641
    24b39642


    Please help me to overcome this issue, thank you.

    Regards,
    Deva
  2. Madhivanan Moderator

    In the EXCEL file prefix single quote before the number

    Madhivanan

    Failing to plan is Planning to fail
  3. sqldev New Member

    Thanks a lot Madhi <img src='/community/emoticons/emotion-1.gif' alt=':)' />, do we have any other alternative method?<br /><br />Regards,<br />Deva
  4. Madhivanan Moderator

    I dont think so [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page