Home » SQL & PL/SQL » SQL & PL/SQL » csv to table (10g windows)
csv to table [message #316129] Wed, 23 April 2008 18:28 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i am attaching a sample std.csv file, i have 5 .csv same format.
At present iam storing them in one temp table and loading it in actual table using merge when matched update not matched insert
how can i load 5 .csv one by one .once first merge is completed then take the second .csv then merge it to actual table, next take the third .csv and merge next the other one till last ..
can any help me...
  • Attachment: std.csv
    (Size: 0.25KB, Downloaded 159 times)
Re: csv to table [message #316151 is a reply to message #316129] Wed, 23 April 2008 23:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table.

Regards
Michel
Re: csv to table [message #316153 is a reply to message #316129] Thu, 24 April 2008 00:18 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hi,
U can use SQL Loader also.

regards
Deepak
Re: csv to table [message #316237 is a reply to message #316129] Thu, 24 April 2008 06:12 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

You can use UTL_FILE method with append mode.

Thanks
Mano
Re: csv to table [message #316252 is a reply to message #316237] Thu, 24 April 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can use UTL_FILE method

This is the worst method.

Regards
Michel
Re: csv to table [message #316295 is a reply to message #316252] Thu, 24 April 2008 09:10 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
here in the load operation user only will change the path name
INFILE 'C:\test\result\01std.csv' "str '|+#'"
INFILE 'C:\test\result\02std.csv' "str '|+#'"
INFILE 'C:\test\result\03std.csv' "str '|+#'"
INFILE 'C:\test\result\03std.csv' "str '|+#'"
once the first 01std.csv file is loaded to temp_table
then i will use merge to load into actual table once done truncate the table next
02std.csv has to be loaded into temp_table using merege load them to actual then truncate the table load next till the last .csv


Re: csv to table [message #316672 is a reply to message #316295] Sun, 27 April 2008 09:27 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i have 5 .csv file iam using external table to import
first .csv file imports with all records skipping the header
(10 rows)
but for rest import of the .csv its loading the header also .. (10 rows plus the rows)
WHERE iam going wrong ..

[Updated on: Sun, 27 April 2008 09:32]

Report message to a moderator

Re: csv to table [message #316673 is a reply to message #316129] Sun, 27 April 2008 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
After more than 100+ posts you should know that you need to
http://www.orafaq.com/forum/t/88153/0/
read & FOLLOW posting guidelines as stated above
Re: csv to table [message #316681 is a reply to message #316672] Sun, 27 April 2008 14:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Your various posts are contradictory. You say that you are using external tables, but you have posted statements using infile which is used in SQL*Loader, instead of location which is used with external tables. You have also said that you are loading one file at a time into a temporary table, then using merge to upsert the data into another table. If this were true, then you would not be having a problem.

If you are using SQl*Loader, then you need to use options (skip=1) at the top of your control file or put the skip command in your command line and load one file at a time.

If you are using an external table, then you need to use "records delimited by newline skip 1" in your external table definition and use alter table to change the location for each separate file.

In either case, you must process each file using merge before you load the next, in order to avoid loading the header and do your upsert.

[Updated on: Sun, 27 April 2008 14:50]

Report message to a moderator

Re: csv to table [message #316738 is a reply to message #316681] Mon, 28 April 2008 02:37 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
sorry for misguding or posting various contradictory
because first i thought i can use sqlldr to do this later relased as pre reqirment it would be better to use external table.
ok at iam using external table do this, present i have many .csv file they all in the same format and same path

....
..
ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE SKIP=16
 	   BADFILE 'DIR_EMP':'emp.bad'
   	   LOGFILE 'DIR_EMP':'emp.log'
          FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL.....
...
 LOCATION ('01emp.csv','02emp.csv','03emp.csv')


iam able to load the first by skiping 16rows(header) and data is looaded properly
when loading second third ... its taking the 16 rows(header) also
in my location if i takeout '01emp.csv' then loads correctly for second,
LOCATION ('02emp.csv','01emp.csv','03emp.csv')[/CODE]rest of the .csv file takes header also (16 rows)..
Re: csv to table [message #316847 is a reply to message #316738] Mon, 28 April 2008 09:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You have to process one file at a time. If you try to load them all together, it will only skip the header in the first file listed and your merge will not do proper updates on records that are contained in multiple files. So, as I said before, you need to use alter table to change the location:

CREATE TABLE temp_table ...
LOCATION ('01emp.csv') ...;

MERGE ...;

ALTER TABLE temp_table LOCATION ('02emp.csv');

MERGE ...;

ALTER TABLE temp_table LOCATION ('03emp.csv');

MERGE ...;



[Updated on: Mon, 28 April 2008 09:57]

Report message to a moderator

Re: csv to table [message #316888 is a reply to message #316847] Mon, 28 April 2008 14:33 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
in some of the post i saw that from
LOCATION (here he can pass any number of file)
actually why iam asking this is if its 100's of .csv
do i need to go to scroll and change location ALTER TABLE temp_table LOCATION
here most of the time number of file will change say some TIME 15, 300, 20, 45 etc...
can u pls guide me ..
Re: csv to table [message #316901 is a reply to message #316888] Mon, 28 April 2008 18:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If all you wanted to do was load the data without skipping headers or doing individual merges, then you could put all the file names in one location clause. But, if you want it to skip the headers and do a separate merge of each file, then you cannot do it that way and you have to have one alter table statement per file and one merge statement per file. You can use dynamic SQL (SQL to create SQL) or any other method you choose to create a script that has one alter table statement and one merge statement per file. You would need to have the file names in a table that you could select from.

Re: csv to table [message #317068 is a reply to message #316901] Tue, 29 April 2008 07:06 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
thx
but cilent particular wants just in one line its easy for him he will enter all the .csv file (thats external table location)
i am confused ..
Re: csv to table [message #317114 is a reply to message #317068] Tue, 29 April 2008 10:23 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You need to accept that you cannot do exactly what you want to do in exactly the way you want to do it. There is no command to get it to automatically skip the headers in the subsequent files. It just will not work that way. You need to understand how things work and what your other options are. If you allow your client to enter all of the file names and use those in one location clause and the files have headers then you cannot skip loading the headers. Also, if the different files contain rows for the same record, then your merge will not work correctly. I don't know what your data is like, so I don't know if that is a problem or not.

You need to look into different options. If you can get the text files without headers that would solve the header problem. Another option would be to load the data with the headers, then delete the rows with the headers. You could do this simply by deleting all rows where the column values are equal to the header names. Or, if you can have your client input the file names the way he wants as a parameter to a procedure then you can parse the names out and process them one at a time in your procedure. It would be real simple to create a loop and for each file within the loop, do a dynamic alter table using the file name for the location, then a merge.


Previous Topic: Grouping by a Date Range
Next Topic: Trigger not working
Goto Forum:
  


Current Time: Sun Dec 04 14:46:48 CST 2016

Total time taken to generate the page: 0.10805 seconds