SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Type Mismatch on Excel Input


Type Mismatch on Excel Input

Author
Message
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3609 Visits: 2000
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?
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 907
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62912 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3609 Visits: 2000
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62912 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51981 Visits: 21170
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62912 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3609 Visits: 2000
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search