| Load data from thousands of CSV files into one table [message #394234] |
Thu, 26 March 2009 04:32  |
gbaliotis
Messages: 8
Registered: April 2007
Location: Greece
|
Junior Member |
|
|
Dear all,
I get thousands of CSV files every day with the following name : CDR_<date>_time.csv. All these files have the same format, regarding their contents. 4 columns - VARCHAR2 type. We would like to run a script every hour which will load the data of every CSV file into a specific table. Afterwords, we will cut these csv files and paste them into a new folder.
Our Server's OS is Windows Server 2003 and our Database is Oracle 10GR2.
I would be delighted if you could help me! It is very urgent for me.
Thank you very, very much in advance!
Giorgos Baliotis
|
|
|
|
|
|
|
|
| Re: Load data from thousands of CSV files into one table [message #394257 is a reply to message #394246] |
Thu, 26 March 2009 05:38  |
gbaliotis
Messages: 8
Registered: April 2007
Location: Greece
|
Junior Member |
|
|
What do I have to write in the script? Can you give me an example?
I tried the following scripts, but with no success :
#imp_cdr_row.bat file
cd D:\EMM\CDRs
echo "Loading the CSV files......................."
echo ""
for /F %%rep1 in ("D:\EMM\CDRs\CDR*.csv") do
echo %%rep1
echo "......................................................"
sqlldr <user>/<password>@<SID> silent=\(HEADER,FEEDBACK\) data=$rep1 control=D:\EMM\CDRs\Insert_CDRs.ctl log=%%rep1.log bad=%%rep1.bad bindsize=505000 readsize=505000 errors=10000 direct=y
mv $rep1
# Insert_CDRs.ctl
LOAD DATA
INFILE *
APPEND INTO TABLE CDR_ROW
FIELDS TERMINATED BY COMMA
TRAILING NULLCOLS
(IMEI char,
MSISDN char,
LAC char,
CELL_ID char)
I would be delighted if you could help me.
Giorgos Baliotis
|
|
|
|
|
|
|
|
| Re: Load data from thousands of CSV files into one table [message #394278 is a reply to message #394271] |
Thu, 26 March 2009 06:35 |
gbaliotis
Messages: 8
Registered: April 2007
Location: Greece
|
Junior Member |
|
|
The error I get executing the bat file is the following :
'imp_cdr_row.dat' is not recognized as an internal or external command,
operable program or batch file.
D:\EMM\CDRs>imp_cdr_row.bat
'#This' is not recognized as an internal or external command,
operable program or batch file.
D:\EMM\CDRs>cd D:\EMM\CDRs
D:\EMM\CDRs>echo "Loading the CSV files......................."
"Loading the CSV files......................."
D:\EMM\CDRs>echo ""
""
%rep1 was unexpected at this time.
D:\EMM\CDRs>for /F %rep1 in ("D:\EMM\CDRs\CDR*.csv") do
-----------------------------------------------
An example of these csv files is the following :
352287452611079 708937097386 4901 a3ac
354894012359240 900978997618 5701 2d8e
355137001280470 806977636126 3099 cbfb
355509019958026 406721329301 2cd1 d2ff
356503012793478 906990004124 da01 a8ec
I remain at your disposal for any clarifications.
Thank you very much!
[Updated on: Thu, 26 March 2009 06:37] Report message to a moderator |
|
|
|
|
|
| Re: Load data from thousands of CSV files into one table [message #394290 is a reply to message #394278] |
Thu, 26 March 2009 06:58 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
A simple example.
You can get any fancy you want.
C:\Documents and Settings\oracle>load.bat
SQL*Loader: Release 10.1.0.3.1 - Production on Thu Mar 26 07:54:25 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 2
SQL*Loader: Release 10.1.0.3.1 - Production on Thu Mar 26 07:54:26 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
SQL*Loader: Release 10.1.0.3.1 - Production on Thu Mar 26 07:54:27 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
C:\Documents and Settings\oracle>sqlplus scott/tiger
SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 26 07:54:41 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from mytable;
ID
----------
1
2
3
4
5
6
6 rows selected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\oracle>type somectl.ctl
LOAD DATA
APPEND INTO TABLE mytable
FIELDS TERMINATED BY',' TRAILING NULLCOLS
(id)
C:\Documents and Settings\oracle>type 1.csv
1
2
C:\Documents and Settings\oracle>type 2.csv
3
4
C:\Documents and Settings\oracle>type 3.csv
5
6
C:\Documents and Settings\oracle>type load.bat
@echo off
FOR %%i IN (*.csv) DO sqlldr scott/tiger control=somectl.ctl data=%%i |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
