Extract Data from SQL table ( single record ) to text file - ( multiple lines )

Question

Extract Data from SQL table ( single record ) to text file - ( multiple lines )

SteveEClarke

SSCertifiable

Points: 5161
More actions
May 31, 2012 at 6:03 am

#256799

I need to extract data from SQL and place into a text file, the text file will contain multiple lines per single line of the table record ( EDI format’ish! )
So the table has ;
“Part1, “Each”, “2012-05-31”, “Description1”, “Description2”, “Description3”
“Part2, “Lot", “2012-03-17”, “Description1”, “Description2”, “Description3”
The text file needs to be;
Axx Part1
Bxx Description
Cxx 2012-05-31
Dxx Each
E01 Description2
E02 Description3
Axx Part2
Bxx Description
Cxx 2012-03-17
Dxx Lot
E01 Description2
E02 Description3
The 3 characters at the beginning of each line are line identifiers, depending on the “field” that is selected, the identifiers represent the category – so multiple lines of description are “E01, E02, E03” etc.
The only other important thing is … the field needs to be fixed positions – so StockCode is in column 8-44, identifier is columns 1-3.
Hope the above is clear ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You can use SSIS, or write custom query and export to file using BCP.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

is there a simple "how to guide" that I could follow ?

Regards

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

"How to" what?

Use SSIS? - BoL would be your first point

Use BCP? - BoL would be your first point

Writing the query? - Read this first: http://www.sqlservercentral.com/articles/Best+Practices/61537/ and supply mentioned there details, so we can help you.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 4

sorry - having an off day.

Thank you for your help - will read and experiment.

Thanks again

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.