Importing excel data to sql table | SQL Server Performance Forums

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
select * into testTable FROM
‘Excel 8.0;Database=C:Test.xls;HDR=YES’,
‘SELECT * FROM [Sheet1$]’) In the excel sheet, give the following data, enoenamesalsdate
Please help me to overcome this issue, thank you. Regards,
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?<br /><br />Regards,<br />Deva
I dont think so [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail