Modifying data through the SQL Server Import Wizard

Question:

Hello everyone

The problem is in importing data to SQL Server 2008 R2 from an Excel file.

The task is this, there is an Excel file (.xls), it has two columns, one with a date in text form ('2015-01-01'), the other with a certain number. How can I put it in a table where the column types are (datetime2 (7), decimal (10,0))?

Through the standard SQL Server export import wizard, it does not work, of course there is a snag in the date, I tried to select the "Write a query specifying data to transfer" option myself and write something like:

SELECT CONVERT(datetime2(7), arcdate) arcdate, my_number FROM `Лист1$`

The response is swearing "This SQL statement is not a query." A simple request, without using conversion functions, goes through, like this:

SELECT arcdate + ' привет', my_number FROM `Лист1$`

It doesn't matter what function, cast or convert. As I understand it, it does not support transformations? I don’t want to do it the old fashioned way, load it in the text, then convert it to a date with a request, I think it’s possible to immediately convert it to a date.

I would be grateful for any help.

Answer:

You can try to import data from Excel file by direct query using openrowset instruction.

Via Microsoft.ACE.OLEDB driver:

insert into targetTable(arcdate, my_number)
select
    cast(arcdate as datetime(7)) arcdate,
    cast(my_number as decimal(10,0)) my_number
from openrowset(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=d:\Data\File.xls;HDR=YES',
    ['Sheet name$'])

or via Microsoft.Jet.OLEDB driver:

insert into targetTable(arcdate, my_number)
select
    cast(arcdate as datetime(7)) arcdate,
    cast(my_number as decimal(10,0)) my_number
from openrowset(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=d:\Data\File.xls;HDR=YES',
    ['Sheet name$'])

depending on what you have installed. I think that most likely the import wizard does something similar.

Note that you can write like this:

select *
from openrowset(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=d:\Data\File.xls;HDR=YES',
    ['Sheet name$'])

here the last parameter to openrowset is the name of the sheet in the xls file. Or you can:

select *
from openrowset(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=d:\Data\File.xls;HDR=YES',
    'select * from [Sheet name$]')

here the last parameter of openrowset already an SQL query to the xls file data.

Probably yours

SELECT arcdate + ' привет', my_number FROM `Лист1$`

and

SELECT CONVERT(datetime2(7), arcdate) arcdate, my_number FROM `Лист1$`

go according to the second option, i.e. the request that you specify does not go to SqlServer, but to OLEDB, and the first is normally parsed by the OLEDB driver, and the second is not.

Scroll to Top