csv to table [message #316129] |
Wed, 23 April 2008 18:28  |
oracle_coorgi
Messages: 188 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 693 times)
|
|
|
|
|
|
|
Re: csv to table [message #316295 is a reply to message #316252] |
Thu, 24 April 2008 09:10   |
oracle_coorgi
Messages: 188 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   |
oracle_coorgi
Messages: 188 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 #316681 is a reply to message #316672] |
Sun, 27 April 2008 14:49   |
 |
Barbara Boehmer
Messages: 9104 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   |
oracle_coorgi
Messages: 188 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   |
 |
Barbara Boehmer
Messages: 9104 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   |
oracle_coorgi
Messages: 188 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 #317068 is a reply to message #316901] |
Tue, 29 April 2008 07:06   |
oracle_coorgi
Messages: 188 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  |
 |
Barbara Boehmer
Messages: 9104 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.
|
|
|