Formatting a column for CSV Output
Hello,
I am trying to fetch a some data from Oracle database and writing to CSV file. My output in csv for a column is displyaing the number as 6.42451E+12 istaed of 6424513039908.
I tried the below to but it is not workig. can anyone suggest me to fix this?
1) TO_CHAR(column1, '9999999999999') in the SQL
2) created a derived column with expression as (DT_STR,13,1252)column1
3) Changed the Input, output column properties to DT_STR and length 13 using ShowAdvansedEditior optiom of the Faltfile destination control.
Thanks,
Srinivas
July 8th, 2011 8:19pm
Did you tried TO_NUMBER
In the TO_NUMBER function to translate a value of CHAR or
VARCHAR2 datatype to NUMBER datatype
latha
Free Windows Admin Tool Kit Click here and download it now
July 8th, 2011 8:24pm
Latha, the column is a Number(13) type and I need to make it to a String.
July 8th, 2011 8:34pm
Also I tried cast(column1
as
varchar2(13))
but still the issue remains
Free Windows Admin Tool Kit Click here and download it now
July 8th, 2011 8:37pm
are you seeing 6.42451E+12 in excel or outputPlease mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
July 8th, 2011 9:06pm
Hello,
I am trying to fetch a some data from Oracle database and writing to CSV file. My output in csv for a column is displyaing the number as 6.42451E+12 istaed of 6424513039908.
I tried the below to but it is not workig. can anyone suggest me to fix this?
1) TO_CHAR(column1, '9999999999999') in the SQL
2) created a derived column with expression as (DT_STR,13,1252)column1
3) Changed the Input, output column properties to DT_STR and length 13 using
ShowAdvansedEditior optiom of the Faltfile destination control.
HI Whats your Source OLEDB ?
what do u see default data type for that column at oledb Source ... [go to advance editor last tab ] if its float change it to big deimal value or DT I8 ...
[Try playing your bold point 3 with OLEDB source ]Hope that helps ... Kunal
Free Windows Admin Tool Kit Click here and download it now
July 8th, 2011 9:15pm
Hello Kunal, it is numeric [DT_NUMERIC] and i changed th output to DT_STR with lenght 13.
the OLEDB source is of OLEDB provider for Oracle. i.e.
oraoledb.oracle.1
July 8th, 2011 9:49pm
Hello Kunal, it is numeric [DT_NUMERIC] and i changed th output to DT_STR with lenght 13.
the OLEDB source is of OLEDB provider for Oracle. i.e.
oraoledb.oracle.1
Did you try with DT_I8 .. changing to Str wont help you i guess
1 what do u see in data viewer by the way with default metadata ?
2.here are couple of more things u can try out try changing the metadata to decimal (25,5)
3. Try to multiply this column by 1 or 1.0 in source query itself ... do some mathematical operation with column [that'll basicaly give output same as input ]
see basically u need to play around it .. i dont remember exactly but faced similar problem long back and did some silly stunts like these only strange though
let me know what u see in data viewer also .. what happening is this value 6424513039908 is some how read
as float / numeric datatype ...
let me see if i can find i guess we have had similar discussion in same forum long back as well Hope that helps ... Kunal
Free Windows Admin Tool Kit Click here and download it now
July 8th, 2011 10:07pm
Kunal I tried all the option you suggested and still it doesn't work.
please let me know if you recollect something on this.
Thank you
July 8th, 2011 10:44pm
The problem with writing to CSVs is that if you want a particular format for the data in the CSV, you can't let SSIS write non-string columns. You'll have to convert all of your columns to DT_WSTR or DT_STR before you send them to the Flat File Destination.
You'll also have to configure your Flat File Connection Manager to have all columns identified as strings - not numerics, ints, or anything else.
Playing with the source query, or the advanced properties of any component won't help you at all.
Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
July 9th, 2011 1:17am
Todd, thank for your suggestion. I tried making the column type as DT_STR in Flatfile (CSV file) connection manger and created a derived column of type DT_STR for the Source column of Type Numeric.
It still displyaing the number as 6.42451E+12 instaed of 6424513039908.
Please suggest.
Thank you,
Srinivas
July 11th, 2011 11:19pm
See if my old post helps:
How to Import Negative Decimal or Floating Values to Flat File Without
Losing Leading Zero
http://geekswithblogs.net/Compudicted/archive/2011/03/28/how-to-import-negative-decimal-or-floating-values-to-flat.aspxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 11th, 2011 11:31pm
Arthur's blog post will probably help. The key is to control the conversion yourself, not let an implicit conversion get done to you.
Talk to me now on
July 12th, 2011 1:07am
Hello, All I found that writing a Number of more than 11 digits with proper display is not fesible in CSV file doesn't save any formatting. so even if we format a column it cannot be saved.
Also I observed even if write a String with all digits and and Leading zeros, CSV file will not Store Leading Zeros.
Please update me if any one did the same with CSV file.
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2011 9:40am
It is definitely possible. I just did it. Download the package named "c878a4c9-6dbd-4738-a622-7f3e5eb7c48e Write formatted numbers to CSV.zip" from
my SkyDrive. (I'm learning to HATE SkyDrive since the last update - no direct linking sucks.)
Talk to me now on
July 26th, 2011 11:25am
Thank you Todd for the Source code, I will try with this later a sI cannot download the same in Our corporate network.
if you can provide some documentation on this that will be great.Srinivas
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2011 12:58pm
Seriously, no. Although I don't mind helping people out, I really do want people to try to help themselves first. There's more than enough information in that package to clarify the process - please do your best to work it out.
Talk to me now on
July 26th, 2011 5:11pm
Todd, I am able to download your source code and it has the output as txt file instead of csv. I am able to pull data in desired format to Txt file but not to CSV.
if you have any sample with output as CSV file that will be helpful.Srinivas
Free Windows Admin Tool Kit Click here and download it now
July 27th, 2011 8:36am
Change the file extension.
Talk to me now on
July 27th, 2011 12:26pm
If I change the file extension programatically and open the CSV file it shows all the columns of the Text file in a Single column with a seperator which doesn't solve the purpose.
Srinivas
Free Windows Admin Tool Kit Click here and download it now
July 27th, 2011 12:53pm
I'm sorry if I'm going to be short here - but I can't show you every little step.
"TXT" is a file extension that by convention only means it should contain human-readable text.
"CSV" is a file extension that by convention only means it should contain "comma separated values". "CSV" files necessarily are a subset of TXT files. That means that every file that fits the "convention" for CSV also fits the convention
for "TXT".
In this particular case, the TXT file generated is a "comma separated value" file. At least it is in my example - I don't know what you're filling yours with.
The file extension is irrelevant. It's the contents of the file that are relevant. Your perception that they're different is only related to how you're opening the file. Please take the time to learn some basic, basic, basic information
technology. Excel and Notepad are different.
Talk to me now on
July 27th, 2011 3:39pm
In my perception a CSV file will be opened with excel (generally) not with notepad. if you are able to find a solution that will be fine otherwise please leave it.
I am not forcing you to find solution nor challenging your knowledge.
Take careSrinivas
Free Windows Admin Tool Kit Click here and download it now
July 27th, 2011 11:53pm