[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

convert txt file to csv using PLSQL

Posted on 2012-08-13
10
Medium Priority
?
1,447 Views
Last Modified: 2012-08-15
Hello Experts,


I have ZIP file which is been stored on a directory. And it has many text files on it. Where each file has to loaded to the specific table.

For example :
If the zip file name is "test.zip" , and it contains "emp.text", dept.text

Where emp has to be loaded to emp table and dept to dept table.

Is there any way to achieve the below goals:
1) unzip the file using PLSQL. And convert it to csv .
2) once converted to csv then it will be loaded to the tables
3) Then rename the zip file by adding timestamp and deleting the unziped folder.

Or

1) unzip the file using PLSQL.  
2) Load the text files to the tables
3) Then rename the zip file by adding timestamp and deleting the unziped folder.
0
Comment
Question by:Swadhin Ray
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38289050
I would probably do all this from the OS and use SQL Loader to load the files.  It would probably be a lot more efficient.

That said:  It appears someone has written some PL/SQL to unzip and zip files:
https://forums.oracle.com/forums/thread.jspa?messageID=9435449

You should be able to take that code and add the load and rename code.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38289081
using sqlloader external tables you can run a preprocessor to unzip the file for you.

no pl/sql is needed at all

you'll just need to do the delete afterward.  I suppose you could use pl/sql to do that with utl_file.fremove  or just do it from a shell script.

preprocessor info can be found here


http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1106.pdf
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38289137
Thanks a lot experts.

But using loader or external table will not make fully automated work.

What my intention was to make it everything on PLSQL and to schedule it in such a way that when ever any zip file will be uploaded then it will be loaded without any manual work.

But still I would like to see any solution on PLSQL side.

Thanks.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38289176
>>will be uploaded then

Uploaded how and to where?

>>it will be loaded without any manual work.

This will require A LOT of code once you include all the error checking.  Likely more than you will get anyone here to write for you.

Then again, maybe someone will write it for you.

I've never seen the ZIP pl/sql I posted the link to so I cannot say if it will work or not but if it does, it is a starting point.  If you are ensured flat text files, I might tweak that code to return a CLOB instread of a BLOB.  It will make parsging easier.

I'm not sure why you want to parse it into a CSV and not just parse it and load it.

If you need help with parsing the txt file or renameing the zip, you can always ask those in different questions.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38289183
>>But using loader or external table will not make fully automated work.

Just thought of this:  Sure it can be automated.  When it is uploaded, if it can call a procedure, then you can schedule an external script with DBMS_SCHEDULER to run immediately.  Then unzip/sql loader/move from the BAT file or script.
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1000 total points
ID: 38289273
if you have toad, use it.
toad has a wizard for creating all command line and script files needed for sql loader

i used it last week, took about 2 minutes
and it's fully automated

it's even got a wizard to set up the windows scheduled job
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38291519
@Geert_Gruwez : We don't have toad.

Is it possible to by using the same functionaluity on SQL Developer ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38291927
>>> when ever any zip file will be uploaded

how will this be detected?  Are you supposed to periodically scan a directory for new files?
or will the upload process invoke your script/procedure/program?
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1000 total points
ID: 38292150
it can all be automated
... but i've never done all the steps ... yet

oracle 11.2 is be the database you need with the file watcher (new feature)
File watcher, to trigger an action when a file arrives on the db host
http://docs.oracle.com/cd/E11882_01/server.112/e22487/chapter1.htm#sthref13
More info on file watcher:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm#CIAEJGJE

unzipping the file
>> inidicated by sligthwv
http://www.experts-exchange.com/Database/Oracle/Q_27827183.html#a38289050

you'll have to process the file list in the directory and create a script for sql loader
>> for this you'll need some batch processing
like dir unzipped_dir\*.csv>file_list.txt
here is a good site for scripting:
http://www.dostips.com/DtGeneSitemap.php

your somewhat vague with the converting to csv comments
but those batch scripts with for /L or for /F will probably do the trick

then use sql loader with the generated scripts
>> indicated by sdstuber
http://www.experts-exchange.com/Database/Oracle/Q_27827183.html#a38289081

helping further will be difficult, since it will require all those steps to be worked out to all the correct paths on your oracle server
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 38296788
Thanks a lot to all the experts for your valuable comments.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month20 days, 5 hours left to enroll

873 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