OraFAQ Forum: Server Utilities » Need help in importing data from csv

Home » RDBMS Server » Server Utilities » Need help in importing data from csv

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help in importing data from csv [message #191685]

Thu, 07 September 2006 14:04

grk28
Messages: 38
Registered: June 2006

Member

Hi frnds,

I want to import data from csv file to my oracle table.

Here is the sampel CSV data

"44","45","1","hi "hello" ","565","09/06/2006"
"5","343","1",""K" good ","56","09/06/2006"

here is my ctl file

load data
infile 'C:\mycsv.csv'
into table X
fields terminated by "," enclosed by '"'
TRAILING NULLCOLS
(A , B, C, D, E, F "to_date(:F, 'MM/DD/YYYY')"
)

now my problem is as the 5th column is having '"' in the middle of the sentence when i say enclosed by '"' it is rejecting that record.

I would like to know how to handle such situations.There should be some escape character right ??.

if yes please let me know

immediate help wud be appreciated.

Regards,
Raghukalyan.G

Report message to a moderator

Re: Need help in importing data from csv [message #191698 is a reply to message #191685]

Thu, 07 September 2006 16:05

grk28
Messages: 38
Registered: June 2006

Member

Hi Frnds,

After searching few forumns i have got some clue to handle such situations.

load data
infile c:\testcsv.csv'
into table X
fields terminated by ","
TRAILING NULLCOLS
(A "no_quotes(:A)", B "no_quotes(:B)", C "no_quotes(:C)",
D "no_quotes( Very Happy

)",
E "no_quotes(:E)" ,
F"to_date(no_quotes(:F), 'MM/DD/YYYY')"
)

But i have got a typical data which is as follows

"1","1","1","Customer's asdf asdfhas changed dsafmain st; ; asdf, asdf sadfto asdf; ; asdf , adsf asdf.","1","09/06/2006"

the above code is rejecting this record as well.Is there any way to avoid this situation.

Please let me know.

Barbara can you please help me out .

Regards,
GRK

Report message to a moderator

Re: Need help in importing data from csv [message #191711 is a reply to message #191698]

Thu, 07 September 2006 19:32

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

You have to have some way to determine where one field ends and the next one begins. If you have both commas and double quotes in your data and those are also your delimiters, then you can't load it. You need to see if you can get the data in a better format, wherever it comes from, such as fixed position, or using some delimiter that is not contained in the data. One possible workaround, if each row only has one problem or the other and not both, is to load the data using one method, then use the bad file from that load to load that data using the other method, as shown below.

-- C:\mycsv.csv:
"44","45","1","hi "hello" ","565","09/06/2006"
"5","343","1",""K" good ","56","09/06/2006"
"1","1","1","Customer's asdf asdfhas changed dsafmain st; ; asdf, asdf sadfto asdf; ; asdf , adsf asdf.","1","09/06/2006"
 

-- test.ctl:
load data
infile 'C:\mycsv.csv'
badfile 'C:\mybad.csv'
into table X
fields terminated by "," enclosed by '"'
TRAILING NULLCOLS
(A , B, C, D, E, F "to_date(:F, 'MM/DD/YYYY')")


-- test2.ctl:
load data
infile 'c:\mybad.csv'
APPEND into table X
fields terminated by ","
TRAILING NULLCOLS
(A "no_quotes(:A)",
B "no_quotes(:B)",
C "no_quotes(:C)",
D "no_quotes(:D)",
E "no_quotes(:E)" ,
F "to_date(no_quotes(:F), 'MM/DD/YYYY')")


SCOTT@10gXE> CREATE TABLE x
  2    (a NUMBER,
  3  	b NUMBER,
  4  	c NUMBER,
  5  	d VARCHAR2 (100),
  6  	e NUMBER,
  7  	f DATE)
  8  /

Table created.

SCOTT@10gXE> CREATE OR REPLACE FUNCTION no_quotes
  2    (p_string IN VARCHAR2)
  3    RETURN VARCHAR2
  4  AS
  5  BEGIN
  6    RETURN LTRIM (RTRIM (p_string, '"'), '"');
  7  END no_quotes;
  8  /

Function created.

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test2.ctl LOG=test2.log

SCOTT@10gXE> COLUMN d FORMAT A30 WORD_WRAPPED
SCOTT@10gXE> SELECT * FROM x
  2  /

         A          B          C D                                       E F
---------- ---------- ---------- ------------------------------ ---------- ---------
         1          1          1 Customer's asdf asdfhas                 1 06-SEP-06
                                 changed dsafmain st; ; asdf,
                                 asdf sadfto asdf; ; asdf ,
                                 adsf asdf.

        44         45          1 hi "hello"                            565 06-SEP-06
         5        343          1 K" good                                56 06-SEP-06

SCOTT@10gXE>

Report message to a moderator

Re: Need help in importing data from csv [message #191719 is a reply to message #191685]

Thu, 07 September 2006 21:15

grk28
Messages: 38
Registered: June 2006

Member

Hi Barabara,

Thanks for you response.But i am facing a different problem again .I'm having data in this way as well.

"45","45","1","sadf: dfasdf/asdfsadfasdf asdf/sadf:lady sadf "sdaf 456456 545!" safsadf saf/ sadf","5","09/06/2006".

so if i used the test1.ctl it will fail and this record is not reflecting in the bad file as well.

One more thing is once i am running test2.ctl i am still finding some records in bad file after running the sqlloader.I have executed the test2.ctl even twice but of no use.

Can you please help me out ?

Regards,
GRK

Report message to a moderator

Re: Need help in importing data from csv [message #191879 is a reply to message #191719]

Fri, 08 September 2006 08:05

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

The problem with that record is that it ends with a period instead of a comma. You could load each whole row into one column of a table, use rtrim to remove the trailing period, then spool it back to your text file, then load it into your tables. But it sounds like you really need to determine why the data file that you are trying to load from is such a mess and fix that. Where does this data come from and how?

Report message to a moderator

Re: Need help in importing data from csv [message #192020 is a reply to message #191685]

Sat, 09 September 2006 15:26

grk28
Messages: 38
Registered: June 2006

Member

Hi Barbara,

Thanks for your repsonse.

I am bit confused with your statement.Can you please give me an example for the same.

I will be thankful to you if you do it for me Razz

.

Regards

Report message to a moderator

Re: Need help in importing data from csv [message #192818 is a reply to message #192020]

Wed, 13 September 2006 15:09

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

-- c:\mycsv.csv:
"44","45","1","hi "hello" ","565","09/06/2006"
"5","343","1",""K" good ","56","09/06/2006"
"1","1","1","Customer's asdf asdfhas changed dsafmain st; ; asdf, asdf sadfto asdf; ; asdf , adsf asdf.","1","09/06/2006"
"45","45","1","sadf: dfasdf/asdfsadfasdf asdf/sadf:lady sadf "sdaf 456456 545!" safsadf saf/ sadf","5","09/06/2006".
 
-- test.ctl:
load data
infile 'c:\mycsv.csv'
into table staging
fields terminated by x'0a'
(all_in_one)

-- test2.ctl:
load data
infile 'C:\newcsv.csv'
badfile 'C:\mybad.csv'
into table X
fields terminated by "," enclosed by '"'
TRAILING NULLCOLS
(A , B, C, D, E, F "to_date(:F, 'MM/DD/YYYY')")

-- test3.ctl:
load data
infile 'c:\mybad.csv'
APPEND into table X
fields terminated by ","
TRAILING NULLCOLS
(A "no_quotes(:A)",
B "no_quotes(:B)",
C "no_quotes(:C)",
D "no_quotes(:D)",
E "no_quotes(:E)" ,
F "to_date(no_quotes(:F), 'MM/DD/YYYY')")

SCOTT@10gXE> CREATE TABLE staging
  2    (all_in_one VARCHAR2 (255))
  3  /

Table created.

SCOTT@10gXE> CREATE TABLE x
  2    (a NUMBER,
  3  	b NUMBER,
  4  	c NUMBER,
  5  	d VARCHAR2 (100),
  6  	e NUMBER,
  7  	f DATE)
  8  /

Table created.

SCOTT@10gXE> CREATE OR REPLACE FUNCTION no_quotes
  2    (p_string IN VARCHAR2)
  3    RETURN VARCHAR2
  4  AS
  5  BEGIN
  6    RETURN LTRIM (RTRIM (p_string, '"'), '"');
  7  END no_quotes;
  8  /

Function created.

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@10gXE> SELECT * FROM staging
  2  /

ALL_IN_ONE
----------------------------------------------------------------------------------------------------
"44","45","1","hi "hello" ","565","09/06/2006"
"5","343","1",""K" good ","56","09/06/2006"
"1","1","1","Customer's asdf asdfhas changed dsafmain st; ; asdf, asdf sadfto asdf; ; asdf , adsf as
df.","1","09/06/2006"

"45","45","1","sadf: dfasdf/asdfsadfasdf asdf/sadf:lady sadf "sdaf 456456 545!" safsadf saf/ sadf","
5","09/06/2006".


SCOTT@10gXE> UPDATE staging SET all_in_one = RTRIM (RTRIM (all_in_one, '.'), ',') || ','
  2  /

4 rows updated.

SCOTT@10gXE> SELECT * FROM staging
  2  /

ALL_IN_ONE
----------------------------------------------------------------------------------------------------
"44","45","1","hi "hello" ","565","09/06/2006",
"5","343","1",""K" good ","56","09/06/2006",
"1","1","1","Customer's asdf asdfhas changed dsafmain st; ; asdf, asdf sadfto asdf; ; asdf , adsf as
df.","1","09/06/2006",

"45","45","1","sadf: dfasdf/asdfsadfasdf asdf/sadf:lady sadf "sdaf 456456 545!" safsadf saf/ sadf","
5","09/06/2006",


SCOTT@10gXE>

STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF LINESIZE 255 PAGESIZE 0 VERIFY OFF
SPOOL C:\newcsv.csv
SELECT all_in_one FROM staging
/
SPOOL OFF

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test2.ctl LOG=test2.log

SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test3.ctl LOG=test3.log

SCOTT@10gXE> COLUMN d FORMAT A30 WORD_WRAPPED
SCOTT@10gXE> SELECT * FROM x
  2  /

         A          B          C D                                       E F
---------- ---------- ---------- ------------------------------ ---------- ---------
         1          1          1 Customer's asdf asdfhas                 1 06-SEP-06
                                 changed dsafmain st; ; asdf,
                                 asdf sadfto asdf; ; asdf ,
                                 adsf asdf.

        44         45          1 hi "hello"                            565 06-SEP-06
         5        343          1 K" good                                56 06-SEP-06
        45         45          1 sadf: dfasdf/asdfsadfasdf               5 06-SEP-06
                                 asdf/sadf:lady sadf "sdaf
                                 456456 545!" safsadf saf/ sadf


SCOTT@10gXE>

Report message to a moderator

Previous Topic:	What does the Buffer imply in the Import and Export ?
Next Topic:	Strange SQL Loader problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 06:12:49 CDT 2024