Home » RDBMS Server » Server Utilities » Need help in importing data from csv
Need help in importing data from csv [message #191685] Thu, 07 September 2006 14:04 Go to next message
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
Re: Need help in importing data from csv [message #191698 is a reply to message #191685] Thu, 07 September 2006 16:05 Go to previous messageGo to next message
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

Re: Need help in importing data from csv [message #191711 is a reply to message #191698] Thu, 07 September 2006 19:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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> 


Re: Need help in importing data from csv [message #191719 is a reply to message #191685] Thu, 07 September 2006 21:15 Go to previous messageGo to next message
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
Re: Need help in importing data from csv [message #191879 is a reply to message #191719] Fri, 08 September 2006 08:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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?
Re: Need help in importing data from csv [message #192020 is a reply to message #191685] Sat, 09 September 2006 15:26 Go to previous messageGo to next message
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

Re: Need help in importing data from csv [message #192818 is a reply to message #192020] Wed, 13 September 2006 15:09 Go to previous message
Barbara Boehmer
Messages: 8625
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>




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


Current Time: Sun Dec 04 21:01:28 CST 2016

Total time taken to generate the page: 0.10370 seconds