convert txt file to csv using PLSQL

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.
LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
Swadhin RaySenior Technical Engineer Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Geert GOracle dbaCommented:
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
 
Swadhin RaySenior Technical Engineer Author Commented:
@Geert_Gruwez : We don't have toad.

Is it possible to by using the same functionaluity on SQL Developer ?
0
 
sdstuberCommented:
>>> 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
 
Geert GOracle dbaCommented:
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
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks a lot to all the experts for your valuable comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.