SQL Clone
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 (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1315 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 (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

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

MCSE Business Intelligence - Microsoft Data Platform MVP
surendra.chatakondu
surendra.chatakondu
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1315 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 Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 468
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 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 (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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 Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 468
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