Solved

Exporting a table data to csv file

Posted on 2012-04-03
9
684 Views
Last Modified: 2012-04-03
Hi,

I am having a stored procedure which creates a csv file and insets data into the file from the table.

one column of the table is a number(4) and it requires a formatting of hh:mm
I did that formatting and this table also has -ve values.

Like -23:59, -03:56 etc..

On exporting to CSV file, when opened in note pad or word pad, the records are written properly without any problem but on opening with excel file, there is a problem.

For the rows which should contain records like -23:59 etc it i showing #value and when i clicked on the #value, it shows =-23:59..which means it is taking that number as a formula.

I need to know how to overcome this problem, all i wanted is that -23:59 and similar records should be available as it is required.

Please help resolve(I am using UTIL file package to write the data to csv file)

Regards
0
Comment
Question by:neoarwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:Billy Roth
ID: 37799903
you could try using another spreadsheet app that allows different csv delimiters such as openoffice.org calc.  setup pure comma delimiters and remove the "" text field quoted delimiters.  Do most of your data processing in a editor like notepad++ with regular expressions and such, and then open in openoffice calc.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 37800355
1) While writing data to CSV file using UTIL file package, make sure for at least negative values to surround with string delimiter as double quotes (") on either side. Also, in between column/field values, add a comma (,) as delimter

2) If double quotes (") OR comma (,) are used by any chance in actual data, then use any other special characters like #, ~, ^ etc. to make the CSV & it's data to open up correctly in Excel workbook's sheet

3)  Always, it is better to use TextPad for opening CSV or DAT files. It has rich formatting features

All the best.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37800627
Make sure the data is stored as:  ="-23:39",="-03:56"

Then when you open it in Excel it will be seen as text.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:neoarwin
ID: 37801177
@slightwv I tried doing this ="-23:39",="-03:56"

But now the csv file is not getting generated, when I tried .txt it is getting generated.

UTL_FILE.put_line (v_file,
                            v_sector
                         || ','
                         || v_division
                         || ','
                         || v_region
                         || ','
                         || v_cmpgn_nr
                         || ','
                         || v_cmpgn_yr
                         || ','
                         || v_biling_dt
                         || ','
                         || v_dispatch_dt
                         || ','
                         || v_reg_ord_zone_mgr_lmt_dt
                         || ','
                         || v_zone_mgr_reg_ord_blok_tm
                         || ','
                         || v_zone_mgr_comp_ord_blok_tm
                        );

 v_zone_mgr_comp_ord_blok_tm this column is what I am talking about.
I don't know why now even the csv file is not getting generated.

Please help
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801201
>>I tried doing this ="-23:39",="-03:56"

How did you try it?  I don't see it in the code you posted.


Does v_zone_mgr_comp_ord_blok_tm contain a single value like -23:59 or does it contain the entire list?

If a single value:

...
|| '="' || v_zone_mgr_comp_ord_blok_tm || '"'
...

>>I don't know why now even the csv file is not getting generated.

Likely a trapped error in the exception handler.
0
 

Author Comment

by:neoarwin
ID: 37801231
900,Divisão Pantanal,NO ,3,2012,23-JAN-12,24-JAN-12,'' 00:00'','' 21:22'','' 22:33''

These are the records I am trying to insert but now csv file is not getting generated.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801277
>>These are the records I am trying to insert but now csv file is not getting generated.

That does not show me how you are generating it...

I need to see the actual code you are using.

>>but now csv file is not getting generated.

It is likely because you are now getting some error in the code when you attempted to add the quotes and '='.

The code probably has an exception handler.

What changes did you make to the code?
0
 

Author Comment

by:neoarwin
ID: 37802064
@slightvw It is working :)
0
 

Author Closing Comment

by:neoarwin
ID: 37802071
Than you very much!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email query results in HTML 6 48
How to create an alias for a column name in ORACLE sql ? 2 72
oracle query 3 34
Errror when importing data from Oracle to SQL 6 67
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question