Home » RDBMS Server » Server Utilities » geometric data from text to table and wrong ctl upload into table (2 threads merged by bb) (Oracle 10.2.0.4.0)
geometric data from text to table and wrong ctl upload into table (2 threads merged by bb) [message #589886] Thu, 11 July 2013 11:12 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table, sample file is attached



CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm  NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.


ST
** 9005.nc1  --WO_NO
  1239401A - STRUCT 
  1    -REV_NO
  9005 -MARK
  9005  --POS
  S275JR  --GRADE
  2 --QTY
  PL12 --PROFILE
  B  --TYPE
     840.20 --LEN
     456.42 --WIDTH_WEB
      12.00 --WIDTH_TOP
      12.00 --WIDTH_BOTTON
      12.00--FLANGE_THK
       0.00  --WEB_THK
     94.200 --RADIUS
      2.088--KGM
      0.000 --KGM1
      0.000 --KGM2
      0.000 --BEVEL_PLUS
      0.000--BEVEL_MINUS




AK --CUT
  v       0.00u    222.01       0.00       0.00       0.00       0.00       0.00  
         67.34       0.00       0.00       0.00       0.00       0.00       0.00
        536.10       0.00       0.00       0.00       0.00       0.00       0.00
        840.20     234.41       0.00       0.00       0.00       0.00       0.00
        772.87     456.42       0.00       0.00       0.00       0.00       0.00
        304.10     456.42       0.00       0.00       0.00       0.00       0.00
          0.00     222.01       0.00       0.00       0.00       0.00       0.00
SI  
  v  407.87u  426.42    0.00 004  1239401A-9005
BO --HOLES
hole_v_yn hole_x_dim hole_y_dim  hole_dia
  v      63.35s    178.53      27.00         
  v      95.85s     71.35      27.00
  v     132.25s    199.42      27.00
  v     164.76s     92.25      27.00
  v     201.15s    220.32      27.00
  v     233.66s    113.14      27.00
  v     364.10s     48.00      27.00
  v     364.10s    228.21      27.00
  v     364.10s    408.42      27.00
  v     476.10s     48.00      27.00
  v     476.10s    228.21      27.00
  v     476.10s    408.42      27.00
  v     606.55s    343.28      27.00
  v     639.05s    236.10      27.00
  v     675.45s    364.17      27.00
  v     707.96s    256.99      27.00
  v     744.35s    385.07      27.00
  v     776.86s    277.89      27.00               ---NO_OF_HOLES IS THE COUNT OF V UNDER BO WILL GO INTO NO_OF_HOLES
EN
  • Attachment: 9005111.txt
    (Size: 1.81KB, Downloaded 2141 times)

[Updated on: Thu, 11 July 2013 11:14]

Report message to a moderator

Re: geometric data from text to table [message #589891 is a reply to message #589886] Thu, 11 July 2013 12:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I don't see that this has anything to do with Oracle Text or InterMedia. It looks like it has to do with loading data into a table, which you would do with SQL*Loader or an external table. So, I will move this thread from the Text & Intermedia sub-forum to the Utilities sub-forum. You need to post what results you expect in the table after the load and show what you have tried use SQL*Loader or an external table.

Report message to a moderator

Re: geometric data from text to table [message #589941 is a reply to message #589891] Fri, 12 July 2013 05:04 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
thanks mam, i will try to upload it using external table or sql loader, but i think maybe i need to create a interface using forms 6i to do this.But how can i upload the information of particular lines in particular columns.

Report message to a moderator

Re: geometric data from text to table [message #589950 is a reply to message #589891] Fri, 12 July 2013 07:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks for the response mam, i tried to upload the same file using sql loader but the data is comming as rows instead of going into each column it all went into single column.


CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm  NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)



--First i created the .csv file called 9001.csv
ST
** 9005.nc1
  1239401A
1
9005
9005
  S275JR
2
  PL12
  B
840.2
456.42
12
12
12
0
94.2
2.088
0
0
0
0




AK
  v       0.00u    222.01       0.00       0.00       0.00       0.00       0.00
         67.34       0.00       0.00       0.00       0.00       0.00       0.00
        536.10       0.00       0.00       0.00       0.00       0.00       0.00
        840.20     234.41       0.00       0.00       0.00       0.00       0.00
        772.87     456.42       0.00       0.00       0.00       0.00       0.00
        304.10     456.42       0.00       0.00       0.00       0.00       0.00
          0.00     222.01       0.00       0.00       0.00       0.00       0.00
SI
  v  407.87u  426.42    0.00 004  1239401A-9005
BO
  v      63.35s    178.53      27.00
  v      95.85s     71.35      27.00
  v     132.25s    199.42      27.00
  v     164.76s     92.25      27.00
  v     201.15s    220.32      27.00
  v     233.66s    113.14      27.00
  v     364.10s     48.00      27.00
  v     364.10s    228.21      27.00
  v     364.10s    408.42      27.00
  v     476.10s     48.00      27.00
  v     476.10s    228.21      27.00
  v     476.10s    408.42      27.00
  v     606.55s    343.28      27.00
  v     639.05s    236.10      27.00
  v     675.45s    364.17      27.00
  v     707.96s    256.99      27.00
  v     744.35s    385.07      27.00
  v     776.86s    277.89      27.00
EN


--then i created the .ctl file ,the contents of my 90011.ctl file to read the text file

load data 
infile "9001.csv" badfile "9001.bad" discardfile "imdb_.dsc"
insert
into table dstv_head
FIELDS TERMINATED BY  '\r\n ' TRAILING NULLCOLS
(
WO_NO           ,
  STRUCT             ,
  REV_NO            ,
  MARK               ,
  POS               ,
  GRADE        ,
  QTY              ,
  PROFILE           ,
  TYPE              ,
  LEN             ,
  WIDTH_WEB          ,
  WIDTH_BOTTOM   ,
  FLANGE_THK        ,
  WEB_THK           ,
  RADIUS            ,
  KGM                ,
  KGM1               ,
  KGM2              ,
  BEVEL_PLUS         ,
  BEVEL_MINUS        ,
  HOLES_YN         ,
  HOLES_VERTICAL_YN  ,
  HOLE_X_DIMENSION ,
  HOLE_Y_DIMENSION  ,
  HOLE_DIAMETER      ,
  NO_OF_HOLES  
)

--then in the command prompt c:\orant\bin\sqlldr rakaic/orion control=90011.ctl log=90011.log

--output in the rows instead of line.everything is goin under wo_no column.

WO_NO	STRUCT	REV_NO	MARK	POS	GRADE	QTY	PROFILE	TYPE	LEN	WIDTH_WEB	WIDTH_BOTTOM	FLANGE_THK	WEB_THK	RADIUS
ST	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
** 9005.nc1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  1239401A	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  9005	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  9005	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  S275JR	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  PL12	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  B	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
     840.20	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
     456.42	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      12.00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      12.00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      12.00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
       0.00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
     94.200	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      2.088	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      0.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      0.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      0.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
      0.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
AK	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SI	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
BO	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
EN	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
  • Attachment: 9001.csv
    (Size: 1.53KB, Downloaded 2249 times)

[Updated on: Fri, 12 July 2013 07:02]

Report message to a moderator

wrong ctl upload into table [message #589983 is a reply to message #589886] Fri, 12 July 2013 22:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
I am trying to upload one text file with mulitple lines into a table dstv_head but unfortunately all the lines are going into one column , how can i insert each row of text file as seperate columns.




CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,

mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),

len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm  NUMBER,

kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),

hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)










--First i created the .csv file called 9001.csv

ST

** 9005.nc1

  1239401A

1

9005

9005

  S275JR

2

  PL12

  B

840.2

456.42

12

12

12

0

94.2

2.088

0

0

0

0

AK

  v      0.00u    222.01      0.00      0.00      0.00      0.00      0.00

        67.34      0.00      0.00      0.00      0.00      0.00      0.00

        536.10      0.00      0.00      0.00      0.00      0.00      0.00

        840.20    234.41      0.00      0.00      0.00      0.00      0.00

        772.87    456.42      0.00      0.00      0.00      0.00      0.00

        304.10    456.42      0.00      0.00      0.00      0.00      0.00

          0.00    222.01      0.00      0.00      0.00      0.00      0.00

SI

  v  407.87u  426.42    0.00 004  1239401A-9005

BO

  v      63.35s    178.53      27.00

  v      95.85s    71.35      27.00

  v    132.25s    199.42      27.00

  v    164.76s    92.25      27.00

  v    201.15s    220.32      27.00

  v    233.66s    113.14      27.00

  v    364.10s    48.00      27.00

  v    364.10s    228.21      27.00

  v    364.10s    408.42      27.00

  v    476.10s    48.00      27.00

  v    476.10s    228.21      27.00

  v    476.10s    408.42      27.00

  v    606.55s    343.28      27.00

  v    639.05s    236.10      27.00

  v    675.45s    364.17      27.00

  v    707.96s    256.99      27.00

  v    744.35s    385.07      27.00

  v    776.86s    277.89      27.00

EN







--then i created the .ctl file ,the contents of my 90011.ctl file to read the text file




load data

infile "9001.csv" badfile "9001.bad" discardfile "imdb_.dsc"

insert

into table dstv_head

FIELDS TERMINATED BY  '\r\n ' TRAILING NULLCOLS

(

WO_NO          ,

  STRUCT            ,

  REV_NO            ,

  MARK              ,

  POS              ,

  GRADE        ,

  QTY              ,

  PROFILE          ,

  TYPE              ,

  LEN            ,

  WIDTH_WEB          ,

  WIDTH_BOTTOM  ,

  FLANGE_THK        ,

  WEB_THK          ,

  RADIUS            ,

  KGM                ,

  KGM1              ,

  KGM2              ,

  BEVEL_PLUS        ,

  BEVEL_MINUS        ,

  HOLES_YN        ,

  HOLES_VERTICAL_YN  ,

  HOLE_X_DIMENSION ,

  HOLE_Y_DIMENSION  ,

  HOLE_DIAMETER      ,

  NO_OF_HOLES

)

--then in the command prompt c:\orant\bin\sqlldr rakaic/orion control=90011.ctl log=90011.log




--output in the rows instead of line.everything is goin under wo_no column.

  • Attachment: 9001.csv
    (Size: 1.53KB, Downloaded 2224 times)

Report message to a moderator

Re: wrong ctl upload into table [message #589985 is a reply to message #589983] Fri, 12 July 2013 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails, Read The Fine Manual

http://docs.oracle.com/cd/E16655_01/server.121/e17639/ldr_control_file.htm#i1006645

Report message to a moderator

Re: wrong ctl upload into table [message #589986 is a reply to message #589985] Fri, 12 July 2013 22:21 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks blackswann, i will read this and try again.

Report message to a moderator

Re: geometric data from text to table [message #589987 is a reply to message #589950] Fri, 12 July 2013 23:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You need to format your create table statement. You need to provide the results that you want, not just the results that you are getting. You need to provide a terminator for your record, for example:

infile "9001.csv" "str 'EN'"

assuming that EN indicates the end of the record. You need to make sure that your field names in your control file match your column names in your table, as some do not now. You also need to use filler fields to account for the blank rows. You need to make sure that your columns are large enough to hold the values in the data file. Try to put just one column in your control file and load just one column, then two columns, then three columns, and so on, until you get it loading all of the columns. It makes it easier to see where the problem is. Solve any problems with each column before you move on to the next.

[Updated on: Fri, 12 July 2013 23:53]

Report message to a moderator

Re: geometric data from text to table [message #589988 is a reply to message #589987] Fri, 12 July 2013 23:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I have merged your 2 threads. Please continue in one thread instead of cluttering the forums with multiple threads on the same topic. When you do this, eventually we just ignore them all.

Report message to a moderator

Re: geometric data from text to table [message #589993 is a reply to message #589987] Sat, 13 July 2013 01:33 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks mam, i tried using 2 Columns , but its concatenating and inserting the records into one column, maybe i need to check more options.


create table dstv_deta (wo_no varchar2(2000),struct varchar2(2000))

--Contents of 90012cols.csv

ERRR
CDDD  
EN


--Contents of 90011.ctl the control file as suggested 'EN' Marked as end of the record.

load data 
infile "90012cols.csv"  "str 'EN'"  badfile "9001.bad" discardfile "imdb_.dsc"
insert
into table dstv_deta
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS



(
WO_NO           ,
  STRUCT        
)



--the result i am getting is 

WO_NO	STRUCT
ERRRCDDD 

--the result what i want is 

WO_NO  STRUCT
ERRR   CDDD

Report message to a moderator

Re: geometric data from text to table [message #589994 is a reply to message #589993] Sat, 13 July 2013 01:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Your data is not tab delimited, so don't use x'09'. You need to determine what your delimiter is and that may depend on your operating system. There are various ways to indicate the delimiter using x'...' or '\n' or copying and pasting a line feed between single quotes. Please see the example below. See if you can get that to run on your system, then add the other columns one at a time.

SCOTT@orcl_11gR2> HOST TYPE 90012cols.csv
ERRR
CDDD
EN

SCOTT@orcl_11gR2> HOST TYPE 90011.ctl
LOAD DATA
INFILE "90012cols.csv" "str 'EN'"
BADFILE "9001.bad"
DISCARDFILE "imdb_.dsc"
INSERT
INTO TABLE dstv_head
FIELDS TERMINATED BY '\n'
TRAILING NULLCOLS
  (wo_no,
   struct)

SCOTT@orcl_11gR2> CREATE TABLE dstv_head
  2    (wo_no   VARCHAR2(12),
  3     struct  VARCHAR2(12))
  4  /

Table created.

SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=90011.ctl LOG=90011.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jul 12 23:53:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SCOTT@orcl_11gR2> SELECT * FROM dstv_head
  2  /

WO_NO        STRUCT
------------ ------------
ERRR         CDDD


2 rows selected.

Report message to a moderator

icon14.gif  Re: geometric data from text to table [message #589995 is a reply to message #589994] Sat, 13 July 2013 02:32 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Many thanks mam, your script worked for me , i think \n did the trick at the end as i was specifying wrong terminator.I will try upload the entire data now and see . But the challenging part will be how to record all the information which is comming as subtext.how can i record them in a seperate fields like the lines below BO .Appreciate your great support.


ST
** 9005.nc1  --WO_NO
  1239401A - STRUCT 
  1    -REV_NO
  9005 -MARK
  9005  --POS
  S275JR  --GRADE
  2 --QTY
  PL12 --PROFILE
  B  --TYPE
     840.20 --LEN
     456.42 --WIDTH_WEB
      12.00 --WIDTH_TOP
      12.00 --WIDTH_BOTTON
      12.00--FLANGE_THK
       0.00  --WEB_THK
     94.200 --RADIUS
      2.088--KG/M
      0.000
      0.000
      0.000 --BEVEL_PLUS
      0.000--BEVEL_MINUS

AK --CUT
  v       0.00u    222.01       0.00       0.00       0.00       0.00       0.00  
         67.34       0.00       0.00       0.00       0.00       0.00       0.00
        536.10       0.00       0.00       0.00       0.00       0.00       0.00
        840.20     234.41       0.00       0.00       0.00       0.00       0.00
        772.87     456.42       0.00       0.00       0.00       0.00       0.00
        304.10     456.42       0.00       0.00       0.00       0.00       0.00
          0.00     222.01       0.00       0.00       0.00       0.00       0.00
SI  
  v  407.87u  426.42    0.00 004  1239401A-9005
--- the problem starts from the below line , i want to extract these dimension below BO into seperate fields,
--v into seprate column web_cnt,63.35s into x-dim,178.53--y-dim,27-dia
--likewise next line  v into seprate column web_cnt,95.85sinto x-dim,71.35--y-dim,27-dia


BO --HOLES
  v      63.35s    178.53      27.00        --   WEB_cnt      X-DIMENSION   Y-DIMENSION   DIA
  v      95.85s     71.35      27.00        
  v     132.25s    199.42      27.00
  v     164.76s     92.25      27.00
  v     201.15s    220.32      27.00
  v     233.66s    113.14      27.00
  v     364.10s     48.00      27.00
  v     364.10s    228.21      27.00
  v     364.10s    408.42      27.00
  v     476.10s     48.00      27.00
  v     476.10s    228.21      27.00
  v     476.10s    408.42      27.00
  v     606.55s    343.28      27.00
  v     639.05s    236.10      27.00
  v     675.45s    364.17      27.00
  v     707.96s    256.99      27.00
  v     744.35s    385.07      27.00
  v     776.86s    277.89      27.00               ---TOTAL_NO_OF_HOLES
EN

Report message to a moderator

Re: geometric data from text to table [message #589997 is a reply to message #589995] Sat, 13 July 2013 02:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I have no idea what you mean by subtext. Once again, you need to provide an example of what results you want if you were to select from the table after loading.

Report message to a moderator

Re: geometric data from text to table [message #589998 is a reply to message #589997] Sat, 13 July 2013 02:54 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks a lot mam, i will try to work it out on myside and if i dont succeed , i will provide the test case of what i did along with the expected results.Thanks again for the wonderfull help.

Report message to a moderator

Re: geometric data from text to table [message #590069 is a reply to message #589997] Sun, 14 July 2013 02:04 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Hi mam, thanks for the response , i think my requirement is very complex, i need to parse the csv into parts now , first as line delimiter and some part of the text when 'BO' starts it has to read each segment or value into seperate fields.


CREATE TABLE DSTV_TEXT 
(
  WO_NO              VARCHAR2(12 BYTE),
  STRUCT             VARCHAR2(240 BYTE),
  REV_NO             NUMBER,
  MARK               VARCHAR2(12 BYTE),
  BO_YN           VARCHAR2(12 BYTE),
  HOLES_VERTICAL_YN  VARCHAR2(2 BYTE),
  HOLE_X_DIMENSION   VARCHAR2(12),
  HOLE_Y_DIMENSION   VARCHAR2(12),
  HOLE_DIAMETER      VARCHAR2(12)
  )

--part of csv file.

1239
  1239401A
1
9005
BO
  v      63.35s    178.53      27.00
  v      95.85s     71.35      27.00
  v     132.25s    199.42      27.00
  v     164.76s     92.25      27.00
  v     201.15s    220.32      27.00
 EN

-- i want the data in dstv_text to be like below.


 WO_NO,STRUCT,REV_NO,MARK,BO_YN,HOLES_VERTICAL_YN,HOLE_X_DIMENSION,HOLE_Y_DIMENSION,HOLE_DIAMETER
1239   1239401A 1    9001  BO     V                  63.35s           178.35               27        
1239   1239401A 1    9001  BO     V                  95.85s            71.35               27         
1239   1239401A 1    9001  BO     V                 132.25s           199.42               27       
1239   1239401A 1    9001  BO     V                 164.76s            92.25               27        
1239   1239401A 1    9001  BO     V                 201.15s           220.32               27        

--what i tried


LOAD DATA
INFILE "900111.csv" "str 'EN'"
BADFILE "900111.bad"
DISCARDFILE "imdb_.dsc"
INSERT
INTO TABLE dstv_text
FIELDS TERMINATED BY '\n'
TRAILING NULLCOLS
(
  WO_NO          ,
  STRUCT             ,
  REV_NO           ,
  MARK              ,
  BO_YN         ,
 HOLES_VERTICAL_YN,
  HOLE_X_DIMENSION   ,
  HOLE_Y_DIMENSION  ,
  HOLE_DIAMETER   
)

--what i am getting

WO_NO	STRUCT	REV_NO	MARK	BO_YN	HOLES_VERTICAL_YN	HOLE_X_DIMENSION	HOLE_Y_DIMENSION	HOLE_DIAMETER
1239  1239401A    1     
	9005
	BO
	  v      63.35s    178.53      27.00
	  v      95.85s     71.35      27.00
	  v     132.25s    199.42      27.00
	  v     164.76s     92.25      27.00


	null	null	null	null	null	null	null	null
  • Attachment: 900111.csv
    (Size: 0.22KB, Downloaded 2090 times)

Report message to a moderator

Re: geometric data from text to table [message #590071 is a reply to message #590069] Sun, 14 July 2013 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Where does go the 9005 in the file ?
Where does come the 9001 from in your result? Is this 9005-4? Do we have to subtract 4?

Regards
Michel

Report message to a moderator

Re: geometric data from text to table [message #590072 is a reply to message #590071] Sun, 14 July 2013 02:44 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks for the resonse Michel.Please dont subtract , i am sorry its 9005 only, actually its the result i want which i prepared manually and mistake happened.


WO_NO,STRUCT,REV_NO,MARK,BO_YN,HOLES_VERTICAL_YN,HOLE_X_DIMENSION,HOLE_Y_DIMENSION,HOLE_DIAMETER
1239   1239401A 1    9005  BO     V                  63.35s           178.35               27        
1239   1239401A 1    9005  BO     V                  95.85s            71.35               27         
1239   1239401A 1    9005  BO     V                 132.25s           199.42               27       
1239   1239401A 1    9005  BO     V                 164.76s            92.25               27        
1239   1239401A 1    9005  BO     V                 201.15s           220.32               27

Report message to a moderator

Re: geometric data from text to table [message #590093 is a reply to message #590069] Sun, 14 July 2013 16:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Your hole data appears to be a collection, so it needs to be loaded into a nested table. You can load it into a staging table with a nested table, then insert from the staging table to the target table. I have provided an example below.

SCOTT@orcl_11gR2> HOST TYPE 900111.csv
1239
1239401A
1
9005
BO
v      63.35s    178.53      27.00
v      95.85s     71.35      27.00
v     132.25s    199.42      27.00
v     164.76s     92.25      27.00
v     201.15s    220.32      27.00
EN

SCOTT@orcl_11gR2> HOST TYPE test.ctl
LOAD DATA
INFILE "900111.csv" "STR 'EN'"
BADFILE "900111.bad"
DISCARDFILE "imdb_.dsc"
INSERT
INTO TABLE staging
FIELDS TERMINATED BY '\r\n'
TRAILING NULLCOLS
(wo_no
,struct
,rev_no
,mark
,bo_yn
,holes NESTED TABLE TERMINATED BY 'EN'
(holes  COLUMN OBJECT
(holes_vertical_yn TERMINATED BY WHITESPACE
,hole_x_dimension  TERMINATED BY WHITESPACE
,hole_y_dimension  TERMINATED BY WHITESPACE
,hole_diameter)))

SCOTT@orcl_11gR2> CREATE TABLE dstv_text
  2    (wo_no              VARCHAR2 ( 12 BYTE)
  3    ,struct             VARCHAR2 (240 BYTE)
  4    ,rev_no             NUMBER
  5    ,mark               VARCHAR2 ( 12 BYTE)
  6    ,bo_yn              VARCHAR2 ( 12 BYTE)
  7    ,holes_vertical_yn  VARCHAR2 (  2 BYTE)
  8    ,hole_x_dimension   VARCHAR2 ( 12)
  9    ,hole_y_dimension   VARCHAR2 ( 12)
 10    ,hole_diameter      VARCHAR2 ( 12))
 11  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE hole_typ AS OBJECT
  2    (holes_vertical_yn  VARCHAR2 ( 2 BYTE)
  3    ,hole_x_dimension   VARCHAR2 (12)
  4    ,hole_y_dimension   VARCHAR2 (12)
  5    ,hole_diameter      VARCHAR2 (12));
  6  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE hole_tab AS TABLE OF hole_typ;
  2  /

Type created.

SCOTT@orcl_11gR2> CREATE TABLE staging
  2    (wo_no   VARCHAR2 ( 12 BYTE)
  3    ,struct  VARCHAR2 (240 BYTE)
  4    ,rev_no  NUMBER
  5    ,mark    VARCHAR2 ( 12 BYTE)
  6    ,bo_yn   VARCHAR2 ( 12 BYTE)
  7    ,holes   hole_tab)
  8    NESTED TABLE holes STORE AS holes_nt
  9  /

Table created.

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

SQL*Loader: Release 11.2.0.1.0 - Production on Sun Jul 14 14:17:10 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1

SCOTT@orcl_11gR2> INSERT INTO dstv_text
  2  SELECT wo_no, struct, rev_no, mark, bo_yn,
  3         holes_vertical_yn, hole_x_dimension, hole_y_dimension, hole_diameter
  4  FROM   staging, TABLE (holes)
  5  /

5 rows created.

SCOTT@orcl_11gR2> COLUMN struct FORMAT A10
SCOTT@orcl_11gR2> SELECT * FROM dstv_text
  2  /

WO_NO        STRUCT         REV_NO MARK         BO_YN        HO HOLE_X_DIMEN HOLE_Y_DIMEN HOLE_DIAMETE
------------ ---------- ---------- ------------ ------------ -- ------------ ------------ ------------
1239         1239401A            1 9005         BO           v  63.35s       178.53       27.00
1239         1239401A            1 9005         BO           v  95.85s       71.35        27.00
1239         1239401A            1 9005         BO           v  132.25s      199.42       27.00
1239         1239401A            1 9005         BO           v  164.76s      92.25        27.00
1239         1239401A            1 9005         BO           v  201.15s      220.32       27.00

5 rows selected.

Report message to a moderator

Re: geometric data from text to table [message #590096 is a reply to message #590093] Sun, 14 July 2013 21:45 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
thank you very much mam, this is what exactly i want.One clarification,in case if i need to select multiple .csv files together and upload into table is it possible ,like i will select 10 .csv files of same format and using one .ctl file can i upload them at once into table.For example i have 10 .csv files 900111.csv ,900112.csv ...900120.csv having same format, can i select all of them at once and upload using one ctl file.

Report message to a moderator

Re: geometric data from text to table [message #590100 is a reply to message #590096] Sun, 14 July 2013 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
concatenate the 10 files into a single file before loading

Report message to a moderator

Re: geometric data from text to table [message #590102 is a reply to message #590100] Sun, 14 July 2013 22:31 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks blackswan, what i understand from you is to copy the contents of all .csv files into one .csv or do i need to do concatenate inside .ctl file itself as shown below but the problem is every 3d model produces n number of text files which may vary and the names also change so i cant really concatenate them manually, maybe there is an option like opening a files similar to window file open and selecting all the files through a window interface and process them.Actually i am using forms 6i , there is one option of text_io and getfilename maybe using that i need to try something.Lots of points to ponder, appreciate if you can refer me to some link or example which does the similar to my requirement.


--option a let say i have 2 .csv files 900111.csv and 900112.csv


--contents of 900111.csv
1239
  1239401A
1
9005
BO
  v      63.35s    178.53      27.00
  v      95.85s     71.35      27.00
  v     132.25s    199.42      27.00
  v     164.76s     92.25      27.00
  v     201.15s    220.32      27.00
EN

--contents of 900112.csv
1238
  1238401B
2
9001
BO
  v      43.35s     78.53      37.00
  v      45.85s     41.35      47.00
  v     130.25s    180.42      27.00
  v     165.76s     88.25      27.00
  v     241.15s    212.32      27.00
EN



--DO  i need to merge them into file as below

1239
  1239401A
1
9005
BO
  v      63.35s    178.53      27.00
  v      95.85s     71.35      27.00
  v     132.25s    199.42      27.00
  v     164.76s     92.25      27.00
  v     201.15s    220.32      27.00
EN

1238
  1238401B
2
9001
BO
  v      43.35s     78.53      37.00
  v      45.85s     41.35      47.00
  v     130.25s    180.42      27.00
  v     165.76s     88.25      27.00
  v     241.15s    212.32      27.00
EN

or option B , Do i need to change the .ctl file as below but the problem is i may not know exact number of files , there can be 100's of files.

LOAD DATA
INFILE "900111.csv" ||"900112.csv" "str 'EN'"
BADFILE "900111.bad"
DISCARDFILE "imdb_.dsc"
INSERT
INTO TABLE dstv_text
FIELDS TERMINATED BY '\n'
TRAILING NULLCOLS
(
  WO_NO          ,
  STRUCT             ,
  REV_NO           ,
  MARK              ,
  BO_YN         ,
 HOLES_VERTICAL_YN,
  HOLE_X_DIMENSION   ,
  HOLE_Y_DIMENSION  ,
  HOLE_DIAMETER   
)


Report message to a moderator

Re: geometric data from text to table [message #590108 is a reply to message #590102] Sun, 14 July 2013 23:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Use a separate infile clause in your SQL*Loader control file for each data file, like this:

INFILE "900111.csv" "STR 'EN'"
INFILE "900112.csv" "STR 'EN'"

as documented here:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL1065

Report message to a moderator

Re: geometric data from text to table [message #590110 is a reply to message #590108] Sun, 14 July 2013 23:40 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks mam, so i have to go with option b of adding them in control file ,but can it be done dynamically using forms 6i like file open , select all the files , once selected all the file names be written in one text file as concatenation.

Report message to a moderator

Re: geometric data from text to table [message #590111 is a reply to message #590110] Sun, 14 July 2013 23:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
arif_md2009 wrote on Sun, 14 July 2013 21:40
Thanks mam, so i have to go with option b of adding them in control file ,but can it be done dynamically using forms 6i like file open , select all the files , once selected all the file names be written in one text file as concatenation.


I don't know. That would be a question for the forms sub-forum. However, not many people may know what will work in an outdated version or have an outdated version to test with. You should upgrade your Forms.

Report message to a moderator

Re: geometric data from text to table [message #590112 is a reply to message #590111] Sun, 14 July 2013 23:53 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member
Thanks very much mam, for the valuable help provided, i will try to research more on forms subtopic and try to upgrade my forms or if not try using some API which will work with Oracle.Thanks very much for all the support provided.

Report message to a moderator

Previous Topic: ORA-02374: conversion error loading table (through Data-pump)
Next Topic: Problems DBLINK
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 16:19:37 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.