My big problem after import TXT FILE into sql server

Question

My big problem after import TXT FILE into sql server

midan1

SSCertifiable

Points: 5100
More actions
March 9, 2004 at 7:39 am

#107324

how can i do this ???
combining
evry 3 records
to One
between the asterisk(***)
******************************
fld1,+fld2,+fld3,+fld4,+fld5,+fld6,+fld7,+fld8,+fld9,+fld10,+fld11
******************************
thnks ilan

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734605 More actions · Answer 1

Your image isn't appearing, but can you show a few rows of the table, like 4 or 5 and then what you want it to look like? Not sure where the asterisks come into play.

midan1 SSCertifiable Points: 5100 More actions · Answer 2

this is my table

an like this i have 800000 records

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

INIT/A1AH105, AI, 1.0, IV = LATCHED, FM = 2, DG = 0, TB = 20, BB = 0,

ED = ' aabbccdd', EU = ' PSI',

EV = 0.00000, CD = 74, HW = 000B6H, AP = 17, LC = 1, HL = 14.5000,

***************

INIT/A1AP022, AI, 1.0, IV = LATCHED, FM = 1, DG = 0, TB = 150, BB = 0,

ED = 'vvnnssee, EU = ' °C',

EV = 0.00000, CD = 12, HW = 001A2H, AP = 17, LC = 41, HL = 105.000,

***************

INIT/A1AP023, AI, 1.0, IV = LATCHED, FM = 1, DG = 0, TB = 150, BB = 0,

ED = 'qqeerrttA123', EU = ' °C',

EV = 0.00000, CD = 12, HW = 001A4H, AP = 17, LC = 41, HL = 105.000,

***************

INIT/A1AP123, AI, 1.0, IV = LATCHED, FM = 0, DG = 0, TB = 1500, BB = 0,

" ED = 'ghgjgjghjgh', EU = ' nnnnnhhfhf',"

EV = 0.00000, CD = 74, HW = 00038H, AP = 34, LC = 41, HL = 1000.00,

***************

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

and i only wont This selected filed like this

INIT | ED | EU

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

A1AP123 |ghgjgjghjgh | nnnnnhhfhf

A1AP124 |bbbbbbbbbb | zxxczczczcz

A1AP125|ddgdgfghfhh | nnnnnhhfhf

A1AP126 |uiuiuiuoiuou | sdsfsdfdfdfd

midan1 SSCertifiable Points: 5100 More actions · Answer 3

midan1

SSCertifiable

Points: 5100

March 10, 2004 at 12:57 pm

#498174

this is my table

any solution ????

john-132685 SSCertifiable Points: 6084 More actions · Answer 4

You could always do it the old fashioned way: kluge

Like, BCP the thing as each row being separate row into a work table with an identity column to sequentially number the rows as they appear in the source:

worktable:

rid rtext

1 INIT/A1AH105, AI, 1.0, IV = LATCHED, FM = 2, DG = 0, TB = 20, BB = 0,

2 ED = ' aabbccdd', EU = ' PSI',

3 EV = 0.00000, CD = 74, HW = 000B6H, AP = 17, LC = 1, HL = 14.5000,

4 ***************

5 INIT/A1AP022, AI, 1.0, IV = LATCHED, FM = 1, DG = 0, TB = 150, BB = 0,

6 ED = 'vvnnssee, EU = ' °C',

7 EV = 0.00000, CD = 12, HW = 001A2H, AP = 17, LC = 41, HL = 105.000,

8 ***************

etc.

Then join and parse the strings to find the values:

select a.rtext as line1, b.rtext as line2, c.rtext as line3

from worktable a

join worktable b on a.rid = b.rid - 1

join worktable c on a.rid = c.rid - 2

where a.rtext like "INIT/%"

(See posts on string manipulation/pattern matching for parsing)

midan1 SSCertifiable Points: 5100 More actions · Answer 5

but how can i do this ???

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

1 INIT/A1AH105, AI, 1.0, IV = LATCHED, FM = 2, DG = 0, TB = 20, BB = 0,

2 ED = ' aabbccdd', EU = ' PSI',

3 EV = 0.00000, CD = 74, HW = 000B6H, AP = 17, LC = 1, HL = 14.5000,

4 ***************

5 INIT/A1AP022, AI, 1.0, IV = LATCHED, FM = 1, DG = 0, TB = 150, BB = 0,

6 ED = 'vvnnssee, EU = ' °C',

7 EV = 0.00000, CD = 12, HW = 001A2H, AP = 17, LC = 41, HL = 105.000,

8 ***************

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

PUT the RED rows into ONE row
remove the asterisk(***)
put the blue rows into ONE row
remove the asterisk(***)
...........................................
and go on like this in the table for 800000 records
and in the end i get it in a new table

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

INIT | ED | EU

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

A1AP123 |ghgjgjghjgh | nnnnnhhfhf

A1AP124 |bbbbbbbbbb | zxxczczczcz

A1AP125|ddgdgfghfhh | nnnnnhhfhf

A1AP126 |uiuiuiuoiuou | sdsfsdfdfdfd

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

thnks ilan

john-132685 SSCertifiable Points: 6084 More actions · Answer 6

use the self join as shown to create a view of the imported table which has each row as a separate column.

This self join view will look like this (3 string columns say varchar(80) )

line1 ... Line2 ... Line 3

INIT/A1AH105, AI, ... ED = ' ... EV = 0.00000...

Now you have 1 record per row. you can either spit this back out as a text file and suck it in again using DTS/BCP or parse these string fields while loading into the destination table.

There are several articles on this site in how to use charindex, substring, etc.

midan1 SSCertifiable Points: 5100 More actions · Answer 7

can someone help

and show how to do it on my problematic Table

at the moment the All the Records is in One Table in One Field

thnks a loot

ilan