import a complex data from one table to another table

Question

Post reply

import a complex data from one table to another table

midan1

SSCertifiable

Points: 5100
More actions
March 21, 2006 at 3:20 am

#112397

need help to fix data and import it to another table
all the data in my table is in one field
i wont to put a cursor
in the word "DN:"
and put each filed in
this is an example table "TABLE1"
DATA TYPE =text 16
but i have problem in the filed DN:
it appear evry 12 or 14 rows approximately
i have 300000 rows like this
---------fld1-----------------
DN:     6361002
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 23
LINE EQUIPMENT NUMBER:     AKVA 07 0 34 24
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 23
LINE TREATMENT GROUP:     23
CARDCODE: 6K17BA    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    125
PM TERMINAL NUMBER :    1113
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA SCWID DGT DDN NOAMA CFDA N NSCR
5 A 24 15046361002 MWT CMWI Y N N N WUCR I VMI N N CARR 1 NONE CRA 1 2 4
DN:     6361004
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 20
LINE EQUIPMENT NUMBER:     AKVA 04 0 12 24
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 20
LINE TREATMENT GROUP:     20
CARDCODE: 6K17AC    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    122
PM TERMINAL NUMBER :    409
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT 3WC CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA DGT DDN NOAMA CFW C NSCR 5 I
$ CFDA N NSCR 5 A 20 15046361004 MWT CMWI Y N N N WUCR I VMI N N CARR 1
NONE
DN:     6361005
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 23
LINE EQUIPMENT NUMBER:     ZRKA 01 0 12 28
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 23
LINE TREATMENT GROUP:     23
CARDCODE: 6K17AC    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    131
PM TERMINAL NUMBER :    413
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT CWR CCWB ACB NOAMA AR NOAMA DGT DDN NOAMA SUPPRESS PUBLIC Y Y RSUS ANC6
ANC3 CFDA N NSCR 5 A 24 15046361005 MWT CMWI Y N N N WUCR I VMI N N CARR 1
NONE CRA 4
DN:     6361006
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 20
LINE EQUIPMENT NUMBER:     AKVA 06 0 21 01
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 20
LINE TREATMENT GROUP:     20
CARDCODE: 6K18AA    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    124
PM TERMINAL NUMBER :    674
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT 3WC CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA DGT CFW C NSCR 5 I $ CFDA N
NSCR 5 A 24 15046361006 MWT CMWI Y N N N WUCR I VMI N N CARR 4 NONE
DN:     6361007
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 20
LINE EQUIPMENT NUMBER:     AKVA 05 0 20 02
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 20
LINE TREATMENT GROUP:     20
CARDCODE: 6K17AC    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    123
PM TERMINAL NUMBER :    643
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT 3WC CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA SCWID DGT DDN NOAMA CFW C
NSCR 5 I 0522413511 CFDA N NSCR 5 A 24 15046361007 CFBL N NSCR 5 A
15246361007 MWT CMWI Y N N N WUCR I VMI N N CARR 4 NONE
DN:     6361008
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 20
LINE EQUIPMENT NUMBER:     AKVA 07 0 10 24
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 20
LINE TREATMENT GROUP:     20
CARDCODE: 6K17AC    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    125
PM TERMINAL NUMBER :    345
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA DGT CFW C NSCR 5 I $ WUCR I CARR
1 NONE
DN:     6361010
TYPE: SINGLE PARTY LINE
SNPA: 304   SIG: DT    LNATTIDX: 20
LINE EQUIPMENT NUMBER:     AKVA 01 0 18 04
LINE CLASS CODE:      1FR
IBN TYPE: STATION
CUSTGRP:         RESGRP     SUBGRP: 0 NCOS: 20
LINE TREATMENT GROUP:     20
CARDCODE: 6K17AC    GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER     :    119
PM TERMINAL NUMBER :    581
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CNDB NOAMA ACB NOAMA AR NOAMA DGT CFW C NSCR 5 I 6267080 WUCR I CARR 1 NONE
CRA 1 2 4

-----------------
thnks
ilan

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

Why you did not import this file with field terminator = \n and rowterminator = 'DN:' ?

_____________
Code for TallyGenerator

midan1 SSCertifiable Points: 5100 More actions · Answer 2

i tray to import it with rowterminator

but because the difference between one record to another

i the the row number

and because the line 'DN:'

it appear evry 11 or 16 rows approximately

thre is a difference between one record to another !!!

in the numbrs of the rows !

is it possible to import it ?????

thnks

ilan

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Your rows will become columns. Because your ROWTERMINATOR is 'DN:'

_____________
Code for TallyGenerator

midan1 SSCertifiable Points: 5100 More actions · Answer 4

how to import from table with

ROWTERMINATOR ???

did ROWTERMINATOR work only on file ?

or i can use it on table in sql server ???

ilan

midan1 SSCertifiable Points: 5100 More actions · Answer 5

this is the problem whan i import from file with ROWTERMINATOR

my rows DONT become columns !!!!

-----------------

203,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 23 LINE EQUIPMENT NUMBER: AKVA 07 0 34 24 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 23 LINE TREATMENT GROUP: 23 CARD

204,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 04 0 12 24 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

205,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 23 LINE EQUIPMENT NUMBER: ZRKA 01 0 12 28 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 23 LINE TREATMENT GROUP: 23 CARD

206,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 06 0 21 01 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

207,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 05 0 20 02 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

208,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 07 0 10 24 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

209,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 01 0 18 04 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

210,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: KSA 03 0 10 24 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

211,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 23 LINE EQUIPMENT NUMBER: AKVA 05 0 18 13 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 23 LINE TREATMENT GROUP: 23 CARD

212,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 06 0 17 12 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

213,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 03 0 05 26 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

214,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 03 0 21 15 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

215,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 01 0 27 15 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

216,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: ZRKA 01 0 14 30 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

217,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 01 0 30 07 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

218,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 01 0 25 30 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

219,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 01 0 25 12 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

220,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 03 0 18 30 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

221,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 02 0 10 21 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

222,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 07 0 16 10 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

223,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: ZRKA 01 0 03 11 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

224,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: ZRKA 01 0 05 26 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

225,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: ZRKA 01 0 08 09 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

226,TYPE: SINGLE PARTY LINE SNPA: 304 SIG: DT LNATTIDX: 20 LINE EQUIPMENT NUMBER: AKVA 06 0 14 16 LINE CLASS CODE: 1FR IBN TYPE: STATION CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20 LINE TREATMENT GROUP: 20 CARD

Sergiy SSC Guru Points: 110206 More actions · Answer 6

But what they have become?

_____________
Code for TallyGenerator

midan1 SSCertifiable Points: 5100 More actions · Answer 7

they become like the example above

can you help me how to solve the problem

instead of QUOTE OR clue

thnks

ilan

Sergiy SSC Guru Points: 110206 More actions · Answer 8

If this is UNIX format file with only LF as line terminator or Win format with CR+LF?

_____________
Code for TallyGenerator

midan1 SSCertifiable Points: 5100 More actions · Answer 9

this is a win format

what is

CR+LF ??????

what to do ?

ilan

Sergiy SSC Guru Points: 110206 More actions · Answer 10

CR is "Carriage Return", CHAR(13), 0D in HEX;

LF is "Line Feed", CHAR(10), 0A in HEX.

Standard text file in Windows uses both of them to start a new line.

Standard text file in UNIX uses only LF to start a new line.

"\n" in BULK INSERT means CR+LF. If there is only LF in your file it cannot recognize it as field terminator. In such case you need to use Format File. See BOL.

P.S. How many weeks are you doing computers?

_____________
Code for TallyGenerator