Extracting Data from SQL to Text File

Question

Post reply

Extracting Data from SQL to Text File

SteveEClarke

SSCertifiable

Points: 5161
More actions
June 11, 2012 at 6:37 am

#259232

Hi Guys,
I have the following ( Opportunity ) ;
Using "Sample Data" - I need to create a Fixed format file, that contains data from SQL. I need to end up with a file that contains the following formated data .
Columns 1-4 = Header "PMST"=Master, "DETL"=Details
Columns 5-6 = RecordType - "01" = Partnumber, "02" = Description1, "03" = Description2, "04"=Unit of Measure, "05" = Weight
Columns 7-40 = Details
PMST01PartNumber1
DETL02P1-description1
DETL03P1-description2
DETL04EA
DETL05 4.50
PMST01PartNumber2
DETL02P2-description1
DETL03P2-description2
DETL04KG
DETL05 8.00
etc...
hope this makes sense ? - I need to create the above text file for 25000 records.
use tempdb
go
create table PartMaster
( part_no char( 15 )
, desc1 char( 20 )
, desc2 char( 20 )
, uom char( 2 )
, weight decimal( 8, 2 )
)
insert PartMaster ( part_no, desc1, desc2, uom,weight )
values ( 'PartNumber1', 'P1-description1', 'P1-description2', 'EA', 4.50 )
insert PartMaster ( part_no, desc1, desc2, uom,weight )
values ( 'PartNumber2', 'P2-description1', 'P2-description2', 'KG', 8.00 )
insert PartMaster ( part_no, desc1, desc2, uom,weight )
values ( 'PartNumber3', 'P3-description1', 'P3-description2', 'MM', 12.50 )
insert PartMaster ( part_no, desc1, desc2, uom,weight )
values ( 'PartNumber4', 'P4-description1', 'P4-description2', 'EA', 16.50 )
select * from PartMaster
________________________________________________________________________________________________
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

Andrew G SSChampion Points: 12809 More actions · Answer 1

So you need 5 rows in the text file per row row from the partmaster table?

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

Sorry - Yes that is correct.... BUT if there are only 2 lines of description, then I would miss that line out from the output.

I am looking at maybe an SSIS package ?? - just configuring my test server now.

I have none/little experience with SSIS/BCP/SSAS

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

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

Andrew G SSChampion Points: 12809 More actions · Answer 3

You'll need to write a query first to get the data in a format you can use. Then output it to a flat file destination using SSIS.

I was thinking maybe trying the pivot /unpivot function or select union and adding any static text to those fields in a seperate temp table before output.

Eg. Insert into #temp (id, text)

Select row_number() , 'PMST01' + part_no

Union all

Select row_number() , 'DETL02P'+ row_number + '-' + desc1

Union all

Etc...

Select from #temp order by id

Then SSIS to output to flat file.

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 4

OKay - I understand the logic of creating the multiple lines up front, which I can do - combining the fields to string lengths and having the "temp table" with a field of say ... 128 characters, then position the corresponding fields to the position in the string.

This would be okay - then use SSIS to create the text file ?

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

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