0

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)...

enter image description here

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!

Improve this question
3
  • 1
    n/a is not a date, and the other dates have inconsistent formats, so it's picked the data type accordingly. The usual approach here is to load into a staging table (which has all varchar fields) then use T-SQL to clean it up and merge into the target table. Commented Aug 22, 2018 at 11:02
  • are you saying that we need to convert 42527 to the proper date 06/06/2016 using T-SQL? I am using a staging table, yep. The load of staging table shows this value: 42527 and not 06/06/2016. So i need to pick up these values and do a conversion using T-SQL and then move it to a new table (the converted values). Commented Aug 22, 2018 at 13:03
  • 1
    Yes. Excel data types are not reliable enough and clearly the actual data in the excel sheet is not reliable enough. You need to perform s cleaning process for at least three cases: 1. N/A 2. Numbers (add to 1/1/1900 and take 1) 3. Strings that look like dates (use TRY_CAST) Commented Aug 22, 2018 at 13:39

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

that where clause will limit the dataset. would case work.... case when [col] ='n/a' then null else cdate(date) end ???
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.

Improve this answer

3 Comments

yes, in my staging table almost all the columns have nvarchar datatype (at the least the necessary , except for the creationdate and lastchangedate). I have tried to use: UPDATE @stagingtable SET convertedDate=convert(datetime, CONVERT(float,convertedDate)) WHERE len(covertedDate)=5 and isnumeric(convertedDate)=1 it results for 42451 as March 24 2016 12:00 AM which I do not want... FOR n/a it is ok to keep as n/a - we use it in that way. I only need to convert these numeric values as proper date like 12-20-2017 :)
Your UPDATE @StagingTable SET ConvertedDate = CONVERT(DATE,DATEADD(d,CONVERT(INT,ThisIsADate)-1,'1900-01-01')) WHERE TRY_CONVERT(INT,ThisIsADate) IS NOT NULL; did the trick :D a minor detail: It gives only the day, how could we achieve the time as 00:00:00 , I only need this... maybe using a concat? Well I have used concat(your expression, ' 00:00:00') and it works. Thanks. For me it is enough to clean this way the NUMERIC value to DATE datatype for the date. :) All is well. Thanks!
A better way is to convert to DATETIME rather than DATE. I'll change the code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.