Re: Text Import
From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 06/04/04
- Next message: Darren Green: "Re: Send mail task not sending"
- Previous message: lurc: "Get lost data from backup and merge into live db"
- In reply to: HC: "Text Import"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 4 Jun 2004 09:04:33 +0100
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@hotmail.com> wrote in message news:u$KrqZXSEHA.2780@TK2MSFTNGP09.phx.gbl... > 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 > 3243341332414242123444314422413222431123322211323312324 > 531538 > 11 543 77100 93 85 89 58170 12131716 > 31431144432414412223332233232143234232114223131121211113333211242 > 543540 > 11 505 52100 61 54 500 231413 > 1221421312114114431341212112324441423332224214341322231212414123232141 > 01 499 24 60 31 53 50 78 67 420 9 5 8 5 7 > 8241141342144441212412443243214441234431321141431242444444342411114331112441 > 43143 > JONES COUNTY 668NORTHSIDE HIGH 196 1113 4 222 0 20011987413716 > 372089 0000 > 11 518 23 77 90 67 44030 2018 6 > 1241341332414242123441313421413222434323343131323312224 > 518522 > 11 516 33 83 86 55 72 46040 10121113 > 31432344432414412213332242232143224131114223411133411123131211323 > 516516 > 11 511 64 70 78 83 540 161820 > 1241421312421414431332244112224223421334224241122122223232334423322443 > 11 518 60 80 69 67 70 89 83 580 121110 7 > 8102414213244344432433124432433244211341423123114442424244444423111143312431 > 1142243 > JONES COUNTY 668NORTHSIDE HIGH 196 1113 4 253 0 10041987413724 > 372090 0000 > 11 560 86100 95 89 53160 2619 8 > 3243341332414242123441314432413221434123212231323312324 > 560572 > 11 552 87 92100 95 89 60170 11141916 > 31431444432414412223332223232143224232114324121131211133333211242 > 552549 > 11 541 95 91 96 92 651 212222 > 1421421312321414431331232112324423423333224214121122231212431423322433 > 11 536 88100 88 73100 89 83 681 15141110 > 8102314313244244442434121432423241411231423123414442224244443424131143312432 > 2114243 > JONES COUNTY 668NORTHSIDE HIGH 196 1133 4 23 09051985 > 496291 0000 > 2 > 0000000000000000000000000000000000000000000000000000000 > 14 515 30 83 71 70 61 45030 10101411 > 31334134133414212233332233234143224132114124111131219333323412433 > 515516 > 2 > 0000000000000000000000000000000000000000000000000000000000000000000000 > 2 > 0000000000000000000000000000000000000000000000000000000000000000000000000000 > 0000 > > Thanks > HC > >
- Next message: Darren Green: "Re: Send mail task not sending"
- Previous message: lurc: "Get lost data from backup and merge into live db"
- In reply to: HC: "Text Import"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
