We help IT Professionals succeed at work.

convert txt file to csv using PLSQL

Swadhin Ray
Swadhin Ray asked
on
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
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
CERTIFIED EXPERT

Author

Commented:
@Geert_Gruwez : We don't have toad.

Is it possible to by using the same functionaluity on SQL Developer ?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> 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?
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
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
CERTIFIED EXPERT

Author

Commented:
Thanks a lot to all the experts for your valuable comments.