Exporting a table data to csv file

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
Aravindan GPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billy RothTech Team VolunteerCommented:
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
k_murli_krishnaCommented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Aravindan GPAuthor Commented:
@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
slightwv (䄆 Netminder) Commented:
>>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
Aravindan GPAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
Aravindan GPAuthor Commented:
@slightvw It is working :)
0
Aravindan GPAuthor Commented:
Than you very much!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.