Need to import a DATE column in excel to a SQL tab

问题:

I am using Microsoft Visual Studio 2010 Professional WITH Integration Services. For the management of the database I am using Microsoft SQL SERVER Management Studio 2012.

I have an excel file with several sheets. In some sheets there is a column DATE. I need to import these values to a SQL table.

The problem I'm getting is that the OLE DB Source keeps setting a couple of the excel columns CALLED 'date' to DT_WSTR with a 255 length and not DATE datatype. I've tried to change it to DATE as data type under the EXCEL Source Output in external COLUMNS, and then in the date column... but it keeps to set the excel column as DT_WTSR with 255 (length)...

Below you can see the data of the date column I'm using.

**DATE** 

06/jun/16
13/oct/15
n/a
07/jun/16
20/may/13
n/a
n/a
n/a
n/a
n/a
17 Apr 2018

what I'm getting in the SQL Table:

**DATE**
42527
42290
n/a
42528
41414
n/a
n/a
n/a
n/a
n/a
17 Apr 2018

What is desired?

**DATE**
    06/06/2016
    10/13/2015
    n/a
    06/07/2016
    05/20/2013
    n/a
    n/a
    n/a
    n/a
    n/a
    04/17/2018

IN the TYPEGUESSROWS (using regedit) it is set to 8 (HEX). i'VE TRIED TO change it to 0 or even I gave it a long number and it does not work.

I have also changed the connection string adding several hypothesis for the IMEX=? (I have tried with 1, 2 and 3 where is ?) but to no avail. As you can see there is mixed data types in the column date... I need to keep these values as it appear in LAST example. If I move more rows with dates to the first lines it works, but we cannot change the excel file at all. So how can we keep the values as it presents in the last table I've showed? Thanks!

important: there are much more values n/a than dates... which means even I use a very long TYPEGUESSROWS SSIS will use text... :( it must be a way to get the table I want!

回答1:

Excel is an unreliable data source.

Firstly there is no validation. Anyone can type anything anywhere

Secondly the excel drivers are inconsistent with regards to data types.

I suggest you import into an all varchar (or nvarchar) staging table then use T-SQL to clean it up.

Here is an example:

DECLARE @StagingTable TABLE (ThisIsADate NVARCHAR(100))

INSERT INTO @StagingTable (ThisIsADate)
VALUES
('42527'),
('42290'),
('n/a'),
('42528'),
('41414'),
('n/a'),
('n/a'),
('17 Apr 2018')


SELECT 
CASE 
WHEN ThisIsADate = 'n/a' 
THEN NULL
WHEN TRY_CONVERT(INT,ThisIsADate) IS NOT NULL 
THEN CONVERT(DATETIME,DATEADD(d,CONVERT(INT,ThisIsADate)-1,'1900-01-01'))
ELSE TRY_CONVERT(DATETIME,ThisIsADate,113)
END Converted,
ThisIsADate
FROM @StagingTable

This example attempts to clean up the cases you identified in one inline statement. This gets very complicated and is error prone. A better way is to do put a target conversion field in your staging table and do it iteratively:

DECLARE @StagingTable TABLE (
    ThisIsADate NVARCHAR(100), ConvertedDate DATETIME);

INSERT INTO @StagingTable (ThisIsADate)
VALUES
('42527'),
('42290'),
('n/a'),
('42528'),
('41414'),
('n/a'),
('n/a'),
('17 Apr 2018');


UPDATE @StagingTable
SET ConvertedDate = CONVERT(DATETIME,DATEADD(d,CONVERT(INT,ThisIsADate)-1,'1900-01-01'))
WHERE TRY_CONVERT(INT,ThisIsADate) IS NOT NULL;

UPDATE @StagingTable
SET ConvertedDate = TRY_CONVERT(DATETIME,ThisIsADate,113)
WHERE TRY_CONVERT(DATE,ThisIsADate,113) IS NOT NULL;


SELECT * FROM @StagingTable;

It seems complicated but it is a very reliable way of importing and cleaning excel data. Don't go down the path of fiddling with IMEX and all of those excel driver settings. Even if you find a combination that works and can be deployed to production, it doens't adress the fact that end users can type anything into those fields.

回答2:

While importing from excel you can use Access Mode as SQL Command and write the query as "SELECT cdate(date) FROM [SHEET1$] where date<>'n/a'" Here you can change the "date" to your "Date column name" and "Sheet1" to your sheetName. Then check the preview the data comes in date format.

Please let me know any issue is there further

Thanks

Sasi