On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, October 16, 2010

Generate Code to Create a CSV File

I was continually reworking the code to create CSV files for a particular application, so I wrote a query to do it for me. (It is long enough that I stored it as an attachment on the following page Cr_Csv.Sql. You'll have to scroll to the bottom to see the attachment.)

It will read through the User_Tab_Columns table, and pull all columns (except those in tables in the recycle bin). The data is dumped in column_id order. The first row will be the column_names. After that, each row of data is converted to the CSV format.

You will need to modify the code in the 'With' clause if you want other tables (or a select few). You may also need to add additional data types that may contain a comma as I'm only covering 'DATE', and 'VARCHAR2'.

Also, if your column_names could be longer than 1000 characters you will want to bump that so there is no wrapping. This is currently set to be one long literal with commas between each name (called Headings).

After the code is generated, it is automatically run. You will see a list of each table as it is processed:

If you looked at the output under notepad, it would look something like the following:
And in Excel or Quattro Pro, you would see this:

If you have either success or problems with this code, I'd appreciate it if you would email me. Enjoy.

No comments:

Post a Comment