Below you will see a section of fixed width data that I need to import into
SQL server. When all the data is on one line, I have no problem, but as you
can see below, each record has multiple parts to it. How can I get the 4
subsequent lines imported in the same record with the demographic data on
line 1?

JONES COUNTY 668NORTHSIDE HIGH 196 1113 4 153 0 20081987413708
372088 0000
11 531 41 81100 78 48140 2120 7
32433413324142421234443144224132224311233222113233 12324
531538
11 543 77100 93 85 89 58170 12131716
31431144432414412223332233232143234232114223131121 211113333211242
543540
11 505 52100 61 54 500 231413
12214213121141144313412121123244414233322242143413 22231212414123232141
01 499 24 60 31 53 50 78 67 420 9 5 8 5 7
82411413421444412124124432432144412344313211414312 42444444342411114331112441
43143
JONES COUNTY 668NORTHSIDE HIGH 196 1113 4 222 0 20011987413716
372089 0000
11 518 23 77 90 67 44030 2018 6
12413413324142421234413134214132224343233431313233 12224
518522
11 516 33 83 86 55 72 46040 10121113
31432344432414412213332242232143224131114223411133 411123131211323
516516
11 511 64 70 78 83 540 161820
12414213124214144313322441122242234213342242411221 22223232334423322443
11 518 60 80 69 67 70 89 83 580 121110 7
81024142132443444324331244324332442113414231231144 42424244444423111143312431
1142243
JONES COUNTY 668NORTHSIDE HIGH 196 1113 4 253 0 10041987413724
372090 0000
11 560 86100 95 89 53160 2619 8
32433413324142421234413144324132214341232122313233 12324
560572
11 552 87 92100 95 89 60170 11141916
31431444432414412223332223232143224232114324121131 211133333211242
552549
11 541 95 91 96 92 651 212222
14214213123214144313312321123244234233332242141211 22231212431423322433
11 536 88100 88 73100 89 83 681 15141110
81023143132442444424341214324232414112314231234144 42224244443424131143312432
2114243
JONES COUNTY 668NORTHSIDE HIGH 196 1133 4 23 09051985
496291 0000
2
00000000000000000000000000000000000000000000000000 00000
14 515 30 83 71 70 61 45030 10101411
31334134133414212233332233234143224132114124111131 219333323412433
515516
2
00000000000000000000000000000000000000000000000000 00000000000000000000
2
00000000000000000000000000000000000000000000000000 00000000000000000000000000
0000

Thanks
HC

You have multiple lines which you wish to treat as one lines. So assuming
the new super line is of a fixed format, this an be done. DTS doesn't look
for a row delimiter until it has found all column data, so any CR+Lf
embedded inside a line will be ignored. People normally complain about this,
but it is very useful for data such as this or data with genuine Cr+Lf's
embedded in fields.

I can't work out your format due to the line wraps in the message, but take
this sample file-

ABCDEX
023
ABCDEZ
923


I want one row per table, of two columns, where column 1 contains "ABCDEX"
and column two contains "023". I could of course have multiple columns per
line, but for this example it does not matter.

To setup my task, I could do it in code, but for ease you can still use the
designer, by fooling it to what you want with a dummy file. My dummy file
only has two lines not four and looks like this-

ABCDEF 123
ABCDEF 123


Notice the two added spaces between the data. This is because a Cr+Lf line
break is two characters, so the spaces simulate this. Your file may only use
Cr or Lf so check, and use the correct number of spaces.

In the designer I add a connection and select the dummy file. I then define
my columns at position 6 and 8, so I have three columns in the file format
screen, "ABCDEF", " ", "123", then the line break (red line).

If you have more columns, just add them in. The key part here is that the
spaces that represent the additional line breaks in the middle of the supper
line, become a column in their own right.

Now add your destination connection and DataPump. Map all real columns, and
just ignore the dummy space/Cr+Lf columns we have created.

Test it just to make sure it imports OK at a basic level.

Now change the text file connection to your real file and away you go.


--
Darren Green
http://www.sqldts.com



"HC" <heathchester (AT) hotmail (DOT) com> wrote