Read a non-delimited file and insert into table

Question

Read a non-delimited file and insert into table

touchmeknot123

Right there with Babe

Points: 735
More actions
July 16, 2009 at 8:02 am

#208198

Hi,
I want to read a file and insert data into a table. The file has no delimiters or spaces.
Its a fixed length file.
For example
001Riya#234abcstreetAtlantaGA60020
002Rina#876defstreetAltantaGA60020
According to my knowlegde, we have to use a script task. Can anyone help me with the script.
or any other idea is also most welcome.
Thanks in advance.

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

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

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

You should not need a script.

Configure a flat file connection, setting the format to 'fixed width' and then set all your start positions and lengths ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

touchmeknot123 Right there with Babe Points: 735 More actions · Answer 2

Hi Phil,

Thanks for the quick response.

I realized that I will have to use a conditional spilt task, since the records length is fixed for certain records only, which I did not mention in my previos post.

Now, I need to split the single record into column of the table.

for example

H1234NRina

H2345A123abcstreet

H3456Z23456

so above the records are split on the basis of N(Name),A(Address) and Z(zip)

Please help me with the script.

Thanks in advance

touchmeknot123 Right there with Babe Points: 735 More actions · Answer 3

Another update is that, the 3 records from the flat file will form a single row at the destination (table)

Thanks.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

It's not possible with the current information because there's nothing to identify the difference between the street and the city.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

So N is a Name record, A an Address record, etc?

.. the records length is fixed for certain records only, ...

Please explain what you mean by this and give an example of records which do not have a fixed length.

Other than by proximity in the source file, is there anything in the file which links the associated records? A unique ID, perhaps.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

touchmeknot123 Right there with Babe Points: 735 More actions · Answer 6

Hi Phil,

Thanks for the reply.

I figured it out.

I had to use merge join task and implement the same.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

touchmeknot123 (7/20/2009)
Hi Phil,
Thanks for the reply.
I figured it out.
I had to use merge join task and implement the same.

How were you able to split the street name from the city name with no delimiter? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

touchmeknot123 Right there with Babe Points: 735 More actions · Answer 8

hey Jeff,

Yes, As I had written earlier that certain records have fixed length.

Like say

H1234NRina

H2345A123abcstreet

H3456Z23456

Above, 1st record "type" i.e. type N will have columns of length 5,1,4 only

Similarly, 2nd one, type A will have 5,1,6,5 only.

I did that with the help of conditional split.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

touchmeknot123 (7/21/2009)
hey Jeff,
Yes, As I had written earlier that certain records have fixed length.
Like say
H1234NRina
H2345A123abcstreet
H3456Z23456
Above, 1st record "type" i.e. type N will have columns of length 5,1,4 only
Similarly, 2nd one, type A will have 5,1,6,5 only.
I did that with the help of conditional split.

Undrerstood on those... what I was referring to is how did you split the items in your original post? Specifically, the following items...

001Riya#234abcstreetAtlantaGA60020

002Rina#876defstreetAltantaGA60020

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

touchmeknot123 Right there with Babe Points: 735 More actions · Answer 10

Its possible with the help of 'ragged right' option in the connection manager of the file in general tab. Then switch over to the Columns tab and decide on your column length ( by clicking on the area where you see the columns).