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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics