Home » SQL & PL/SQL » SQL & PL/SQL » Merge CSV files
Merge CSV files [message #229596] Mon, 09 April 2007 06:18 Go to next message
jvjmohan
Messages: 5
Registered: April 2007
Location: India
Junior Member

Hi,

Please help me with a procedure to Merge two csv files. I would be getting two large CSV files but need to concatenate them into a single file for further processing.

Anxious to know any method other than copying line by line from source to destination. Any built-in functions or procedures will be very helpful

Thanks

Vijay Mohan

Re: Merge CSV files [message #229597 is a reply to message #229596] Mon, 09 April 2007 06:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Why not use simple OS methods? What is your OS?
Is there any special processing need to be done in those files?
Re: Merge CSV files [message #229610 is a reply to message #229597] Mon, 09 April 2007 06:44 Go to previous messageGo to next message
jvjmohan
Messages: 5
Registered: April 2007
Location: India
Junior Member

Hi,

Thanks first of all.

The OS we are using is Sun Solaris. I even thoght of doing that but its a bit difficult to call the OS statements from PL/SQL. From the bit of re-searching done i found out that it requires a Pro C compiler or Java, and we are not allowed to do that.

There is no special processing to be done to the contents of the files. I will be changing the file names after merging the two files.

Any other methods plese let me know.

Vijay

[Updated on: Mon, 09 April 2007 06:46]

Report message to a moderator

Re: Merge CSV files [message #229611 is a reply to message #229610] Mon, 09 April 2007 06:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First, why do want to use pl/sql (using UTL_FILE) for file operations?
You can easily fix it in OS (without Pl/sql).
If you still want to do it with pl/sql, look into UTL_FILE.
Search the forum for examples.

Question is what are going to do with the file?

[Updated on: Mon, 09 April 2007 06:51]

Report message to a moderator

Re: Merge CSV files [message #229613 is a reply to message #229611] Mon, 09 April 2007 06:56 Go to previous messageGo to next message
jvjmohan
Messages: 5
Registered: April 2007
Location: India
Junior Member

Thanks again,

Using UTL_FILE i have to read one line and paste it in the destination file. Line by line. I will be having GBs of data and the is method it toooo slow.

I would really appreciate one of the following two solutions.

1. To Transfer bulk data from one file to another or
2. Run shell script from the PL/SQL ( Only PL/SQL)

Basically, I will have to Convert this CSV file into a Table.


Thanks a lot.
Re: Merge CSV files [message #229614 is a reply to message #229613] Mon, 09 April 2007 06:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I will have to Convert this CSV file into a Table.
All you have to do is,
use sql*loader to load it directly into an oracle table.
or
use External Tables and treat those files as Oracle tables.
Re: Merge CSV files [message #229634 is a reply to message #229614] Mon, 09 April 2007 07:54 Go to previous messageGo to next message
jvjmohan
Messages: 5
Registered: April 2007
Location: India
Junior Member

Hi Mahesh,

Thanks again.

The process of using External tables was already done but this procedure was discareded so only we are using this method of transfering directly from CSV to Table is being done.

And in Using SQL * loader, we are allowed to. After all the dialema we found out that only other method is to Concatenate the files.

Irrespective of the "Flashback" please suggest me a method to Concatenate the CSV files into one with the aid of PL/SQL ( but not line by line) or Shell script (Without depending on any other 3rd party S/W).

But thanks for the Suggestions. I have some Constraints.

Thanks.

Vijay.
Re: Merge CSV files [message #229660 is a reply to message #229596] Mon, 09 April 2007 09:09 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you are in unix, simply use the cat command BEFORE running your script. You could also use sqlloader and have it load both files into the same oracle table and as for external tables, they use sql loaders modules to access the data. Simply setup two external tables to your input flat files and then

insert into my_real_table
select col1,col2,col3
from external_table1
union all
select col1,col2,col3
from external_table2;

Using sql*loader would be no faster then simply doing a sql insert. Both methods use the same code modules.
Re: Merge CSV files [message #229668 is a reply to message #229660] Mon, 09 April 2007 10:26 Go to previous messageGo to next message
jvjmohan
Messages: 5
Registered: April 2007
Location: India
Junior Member

Hi Bill,

I am not supposed to use external tables. End of Story. This was already implemented and removed.

Thanks Anyways.

Vijay
Re: Merge CSV files [message #229674 is a reply to message #229668] Mon, 09 April 2007 11:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I don't see what your "constraints" are~.
Sql*loader works as-is.
>>PL/SQL ( but not line by line)
I beleive, there is no such method except UTL_FILE.
>> Shell script (Without depending on any other 3rd party S/W).
Already suggested by myself and Bill.
It is just a one liner thingy!. You just cat the files A and B into C (first write and then append).

cat a > c
cat b >> c

Sql*loader COULD be faster if you can employ Direct Loading methods ( by-passing sql layers). Pain is
it would disable indexes and enable them back and may leave indexes in unusable state(if any issues).

[Updated on: Mon, 09 April 2007 12:02]

Report message to a moderator

Re: Merge CSV files [message #229695 is a reply to message #229660] Mon, 09 April 2007 13:40 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Since external tables allow an arbitrary number of filenames to be listed in the "location" attribute, you don't even need the UNION ALL. Also, the loading can be very easily parallelized because a separate OS process could be launched for each file listed. Pity you can't use the right tool. Any particular reason other than your boss saying no?
Previous Topic: Cardinality Examples
Next Topic: Aleatory Numeric or Value error
Goto Forum:
  


Current Time: Mon Dec 05 15:00:48 CST 2016

Total time taken to generate the page: 0.12035 seconds