Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Importing issue with EXCEL


SSIS Importing issue with EXCEL

Author
Message
surendra.chatakondu
surendra.chatakondu
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 48
My problem is, i have a discount column in excel with value of 0.74 but i load this data into a table it is loading as 0.7399. Column datatype is numeric(7,4). I tried to load 0.75 and it loaded successfully. Whenever i type 0.74 in that cell, it is loading as 0.7399. I am mad at this and i don't know what is the issue exactly. Please give your thoughts if any.

Thanks in advance.

Thanks
Suri
Ganesh Lohani
Ganesh Lohani
SSC Eights!
SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

Group: General Forum Members
Points: 891 Visits: 1739
Use data type Numeric(7,2) or
or
you can use CAST function.

For example,

select CAST(24.4567 AS DECIMAL(4,2)) AS Price
surendra.chatakondu
surendra.chatakondu
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 48
I did converted into numeric in my derived column task as (DT_NUMERIC,7,4)([Third Party Discount]). Still no luck. I have to use 4 decimals, the one i showed you is only sample. Sometimes i get data with four decimals.

thanks for your comments.

Thanks
Suri
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16447 Visits: 13202
Do you type in 0.74 yourself or is the data already present in the Excel sheet.
It is possible that the actual value is 0.7933 and that Excel formats it in another way, showing it as 0.74.

You can always save your excel file as .csv and import that file. Much easier and less headaches...



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

MCSA SQL Server 2012 - MCSE Business Intelligence
surendra.chatakondu
surendra.chatakondu
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 48
I typed myself. I have already bulit-in package in production that talks to excel files only. Each excel file will have multiple tabs which is not possible in csv.

Thanks for your comments.

Suri
Ganesh Lohani
Ganesh Lohani
SSC Eights!
SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

Group: General Forum Members
Points: 891 Visits: 1739
The another way to solve this type of challange is to use data conversion task between source file and destination table.
avinash jobanputra
avinash jobanputra
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 395
I cant seem to replicate this problem. My advise to you firstly would be to use the data viewer within SSIS and see what the value is once is leaves excel. Often it is to do with the connection manager you use to connect to Excel. Please let us know
divyanth
divyanth
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 556
You seem to have the similar problem.... Check this post..you will find the explanation and answer

http://www.sqlservercentral.com/Forums/Topic876267-148-1.aspx#bm876827
surendra.chatakondu
surendra.chatakondu
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 48
I looked at preview in dataflow task in my excel source, there it is showing correctly but when i look at the preview in sql destination task, it is showing 0.7399. Between that i am using derived column task and data conversion task. Derived column task converts that value into numeric(7,4) and data conversion task also do the same.

Thanks
Suri
avinash jobanputra
avinash jobanputra
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 395
Ok it is definately something to do with the conversion. Can you confirm what the data type is of the tabel you are writing to?
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