SQL Server Performance Forum – Threads Archive

Importing excel data to sql table

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

In the EXCEL file prefix single quote before the number Madhivanan Failing to plan is Planning to fail

Thanks a lot Madhi <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, do we have any other alternative method? Regards, Deva

I dont think so [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>