How to parse Flat File with varied Delimiters

Question

How to parse Flat File with varied Delimiters

Sid Childers

Ten Centuries

Points: 1079
More actions
June 4, 2015 at 8:21 am

#398541

I have a text file that is comprised of a set of tags which represent a record. TagA is varchar with a CRLF delimiter eg AB 234dfckr where AB is the tag. TagB is similar with a string value. TagC has several rows delimited by semicolon with values separated by underscores e.g. ABC____fghwety________z53987;
I need to import the contents of these tags in a flat file. When the tag is present, the contents are imported. If other tags exist, they are ignored.
I want to use a Script component. How do I formulate my script to accomplish this varied file format?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Could you post a sample of the file? Without real data and just enough rows to understand the different formats.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sid Childers Ten Centuries Points: 1079 More actions · Answer 2

Sure. Here's a few lines with sample data

AA 123456 (AA is the tag which is essentially the column header) CRLF is the delimiter

BB Sample Text String CRLF delimiter

CC Sample Text String; (semicolon delimiter then CRLF)

Header1_______Header2_______Header3______Header4_____Header5_____Header6;

Value1_______Value2______Value3_______Value4________Value5___________Value6;

DD Sample Text String CRLF delimiter

Value7 Value8 Value9 Value10

EE Ignore this Tag

FF Ignore This Tag

AA 789123 Repeated tag with new values for tags BB CC and DD

Hope this helps. Thanks!

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 3

Hi ,

You can simple load your data into temp table

tempo table must have only 3 column.

loadid,col001 and load_date

then process your data from temp table to main table by the help of function.

if you want I can provide you the stored procedure and function of same.

in my projct I have a flat file separeted by "-".

Regards,

Vipin jha