Reading .TXT File

Question

Post reply

Reading .TXT File

hasan.askari

Mr or Mrs. 500

Points: 535
More actions
June 3, 2010 at 2:49 pm

#157002

I have a text file with the following format that are generated after every few hours. I want to have anything before (=) sign as a column names and after (=) will be the rows.
Line=001
Machine=3333B
Description=Machine System
PartNumber=H00000
NominalCycle=30.00
Line=002
Machine=3333C
Description=System
PartNumber=HF0939
NominalCycle=20.00

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: 246993 More actions · Answer 1

What is your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hasan.askari Mr or Mrs. 500 Points: 535 More actions · Answer 2

How would you have everything before (=) sign as name of the column and after the sign would be the rows.

here is an example:

LineMachineDescriptionPartNumerNomincalCycle
0013333BMachine SystemH0000030.00
00245333BSystemJ0000020.00

LutzM SSC Guru Points: 107049 More actions · Answer 3

To get the data into SQL server, look into bcp or OPENROWSET, maybe even linked server (depends on the -unknown- scenario).

You could use CHARINDEX() and LEFT/RIGHT/STUFF to separate the two values. You could also use a split string function to separate the data.

Once you have your data separated you could either use PIVOT or the CrossTab solution (see the link in my signature) to transform it into columns and rows.

I hope that helps to get you started...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Since this was posted in an SSIS forum I am assuming you want to do this in SSIS..

The built-in data sources CANNOT easily do this, you might be able to build a command that MIGHT be able to pull it off but I think you will have trouble with that.

Also, I see some additional problems with doing this in SSIS, the column meta-data such as column name and type MUST be set at design-time, it CANNOT change at run-time. So if the file structures are highly variable this is going to be problematic, you would need a way to pick out which file has which structure and send it to the right data-flow.

I *believe* that a CozyRoc product does allow meta-data changes at run-time, you will have to look at that. http://www.cozyroc.com/.

You will still need to find a good way to get the data into a usable structure.

CEWII

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

A script component would also be able to do the job (read the lines, form a single record, then output it).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SSIS_NewBi Right there with Babe Points: 776 More actions · Answer 6

I was able to solve your issue.

As mentioned in this thread you can use the Pivot transformation to convert the file. But for Pivot you would need 3 columns else if will give you Pivot has duplicate rows error.

To overcome this I have givena unique row number using script component in data flow task.

Attached is the code for the same.

Team,

Can you please review the code and evaluate it in terms of Performance and optimization.

thanks

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 7

SSIS_NewBi (6/5/2010)
Team,
Can you please review the code and evaluate it in terms of Performance and optimization.
thanks

:ermm:

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246993 More actions · Answer 8

da-zero (6/7/2010)
SSIS_NewBi (6/5/2010)
Team,
Can you please review the code and evaluate it in terms of Performance and optimization.
thanks
:ermm:

Wow, terse evaluation :w00t:

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hasan.askari Mr or Mrs. 500 Points: 535 More actions · Answer 9

SSIS_NewBi (6/5/2010)
I was able to solve your issue.
As mentioned in this thread you can use the Pivot transformation to convert the file. But for Pivot you would need 3 columns else if will give you Pivot has duplicate rows error.
To overcome this I have givena unique row number using script component in data flow task.
Attached is the code for the same.
Team,
Can you please review the code and evaluate it in terms of Performance and optimization.
thanks

You are really amazing!!!

That really solved the problem that I have. Thank you very much.

One more question, is this possible if we replace the source text file with different name after every hour?

SSIS_NewBi Right there with Babe Points: 776 More actions · Answer 10

Hi

Yes you can do that. There are two scenarios

1. There is only 1 file in the folder at a specific time

Please follow my posting

http://www.sqlservercentral.com/Forums/Topic933534-148-1.aspx

2. If you have multiple files in the location and you would like to pcikup the latest file based on time stamp then you can use WMI Task .

For scheduling you could use SQLServer Agent or scheduling tool like Autosys.

I hope this helps