Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Type Mismatch on Excel Input Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 6:47 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 529, Visits: 1,562
I'm working in SSIS 2012 and I'm trying to use a query in an Excel Source to bring in only the data I want to use. My query is

SELECT F1, F2, F3, F4, F5, F6, F7, F8 From [SheetName$] WHERE F1 > '1/1/2014' and F4 > 0

In this case, F1 is data type date[DT_DATE] and F4 is currency [DT_CY].

I getting a type mismatch error that seems to be in the date column because is I remove it I am able to preview data. Is it possible to use an Excel file as a source and query by date range?

Post #1553724
Posted Monday, March 24, 2014 3:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 386, Visits: 623
dates in excel are actually numeric. the integer is the days and the decimal is the time.

If you have access to the data in any format other than Excel you will save yourself a lot of heartache.
Post #1553930
Posted Monday, March 24, 2014 3:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
dan-572483 (3/21/2014)
I'm working in SSIS 2012 and I'm trying to use a query in an Excel Source to bring in only the data I want to use. My query is

SELECT F1, F2, F3, F4, F5, F6, F7, F8 From [SheetName$] WHERE F1 > '1/1/2014' and F4 > 0

In this case, F1 is data type date[DT_DATE] and F4 is currency [DT_CY].

I getting a type mismatch error that seems to be in the date column because is I remove it I am able to preview data. Is it possible to use an Excel file as a source and query by date range?



You can save yourself from a lot of pain by just reading in all of the data (maybe keep F4 > 0) and filter out unwanted rows with a conditional split. It will be easier to implement.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1553931
Posted Tuesday, March 25, 2014 2:50 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 529, Visits: 1,562
The Excel file contains data going back several years and I'm only looking for data entered in the past few weeks. (Honestly it is a personal project I'm using to teach myself SSIS using a spreadsheet of gasoline purchases I've been keeping since the 1990s. I find myself more motivated to practice with data that has personal meaning to me than generic leaning guide projects on AdventureWorks )

My goal is to learn to design projects efficiantly, which means reading in only data that is needed (the past week or two using a SELECT MAX() variable assigned from the target DB) rather than all 10+ years of data. If I connect the Conditional Split to the Source connector I can do that? I've also found messages about exporting Excel to CSV before importing to SQL, but that seems more complex that it should be.
Post #1554691
Posted Wednesday, March 26, 2014 1:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
dan-572483 (3/25/2014)
The Excel file contains data going back several years and I'm only looking for data entered in the past few weeks. (Honestly it is a personal project I'm using to teach myself SSIS using a spreadsheet of gasoline purchases I've been keeping since the 1990s. I find myself more motivated to practice with data that has personal meaning to me than generic leaning guide projects on AdventureWorks )

My goal is to learn to design projects efficiantly, which means reading in only data that is needed (the past week or two using a SELECT MAX() variable assigned from the target DB) rather than all 10+ years of data. If I connect the Conditional Split to the Source connector I can do that? I've also found messages about exporting Excel to CSV before importing to SQL, but that seems more complex that it should be.


If you connect the conditional split to the source, you can filter out rows but only right after they were read.
So you are still reading everything in. In my opinion, that's OK. Some types of input are not easy to filter (such as flat files) and it might be easier to just read everything. Excel files can contain only so much rows - depending on the version - so you have to ask yourself if it is worth investing a lot of development time in gaining just a few seconds of performance.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1554797
Posted Wednesday, March 26, 2014 1:19 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
dan-572483 (3/25/2014)
The Excel file contains data going back several years and I'm only looking for data entered in the past few weeks. (Honestly it is a personal project I'm using to teach myself SSIS using a spreadsheet of gasoline purchases I've been keeping since the 1990s. I find myself more motivated to practice with data that has personal meaning to me than generic leaning guide projects on AdventureWorks )

My goal is to learn to design projects efficiantly, which means reading in only data that is needed (the past week or two using a SELECT MAX() variable assigned from the target DB) rather than all 10+ years of data. If I connect the Conditional Split to the Source connector I can do that? I've also found messages about exporting Excel to CSV before importing to SQL, but that seems more complex that it should be.


I haven't done exactly what you are doing, but I do have some comments.

1) As Aaron has already pointed out, Excel doesn't do data-typing. Regardless of how Excel displays data, it is either text or numeric. Dates (I think) are stored as the number of days since 1/1/1900 (or maybe 1/1/1899). Decimals represent the time component. So I would try WHERE XLDate > nnnn (you'll have to work out what this number is).

2) If you want to be efficient, don't use Excel files as data sources. Use CSV files instead.

3) I understand your wanting to filter your source data, but using a conditional split to direct unwanted rows to oblivion is fast - you should try it. Remember, once again, that Excel is not a database engine, so queries against it are not likely to be optimised.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1554801
Posted Wednesday, March 26, 2014 1:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
Phil Parkin (3/26/2014)

1) As Aaron has already pointed out, Excel doesn't do data-typing. Regardless of how Excel displays data, it is either text or numeric. Dates (I think) are stored as the number of days since 1/1/1900 (or maybe 1/1/1899). Decimals represent the time component. So I would try WHERE XLDate > nnnn (you'll have to work out what this number is).



I believe it is 1/1/1900, but I think they forgot the leap day in 1900, so the count is one day off. Just to show how complicated it can get with Excel. It's just easier to read everything as-is and deal with it at a later point.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1554807
Posted Wednesday, March 26, 2014 10:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 529, Visits: 1,562
I have noticed that Excel stores dates in a numberic format. For example if you enter 3/26/2014 in Excel and then format it for numeric you get 41724. I suppose you could covert the MAX(date) value from the target DB to the numeric value Excel uses (if more recent dates translate to greater numberic values).

Again, for this project it doesn't matter if I read all the data, but I'm training myself to use best practices, and neither reading 10 years of data to get records from the past week nor converting 10 years to CSV sounds "best" to me.

Post #1555081
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse