Excel choosing datatype DT_CY
-
Hi
I'm starting to loose my hair 😉
I'm importing a Excel-sheet that has some formating when looking at it.
I don't know if the datatypes can be changed in some way without doing anything to the actual excel-sheet.
My problem is that I have a column that has for example the value 188,12445
That is formatted to $188,12 visually.
When I import the Sheet it is choosing DT_CY, probobly because the $. DT_CY cannot hold more than 4 digits??? and on top of that it is not rounded right. I get 188,1244.
Am I doing something wrong?
I'm have tried openrowset with sheet$Range1:range2 and Commandobject.
Can I somehow lock the incolumns to be WSTR(255) in SSIS?
Thanks
Michael
-
If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:
* Excel Source component - for reading data from Excel worksheet.
* Excel Destination component - for writing data in Excel worksheet.
* Excel Task - for manipulating Excel workbooks.
* Excel Connection - used by the components above and also for implementing custom scripts based on it.
CozyRoc Excel Source doesn't try to be smart about the column type. You can define it to be any type from the types available in SSIS. Cheers!
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
You can try three things:
* format the columns in the Excel file to General.
* write a SQL query to the Excel file to fetch the data and format the data in the query the way you want
* try setting the datatype to the one you want in the Advanced Editor of the Excel source
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply