We help IT Professionals succeed at work.

Exporting multiple SQL tables to a single text file

Medium Priority
286 Views
Last Modified: 2013-11-30
I have 5 SQL tables set up that capture data that I need to export to a single text file.
The format of the file is as follows:
1st line is a File Header
2nd line is a Batch Header
3rd and subsequent lines are the transactional data lines
2nd Last line is a Batch Trailer
Last line is a File Trailer

Each of the line types have different fields with different vales.
Any clues would be much appreciated.
Comment
Watch Question

Commented:
You would need to create a SQL Statement which links all tables together to pull all records and then use that to form the export...

Author

Commented:
Thanks St3veMax,
What would the SQL statement look like? Each table has a different number of fields so one can not use a union and what form would the export take?

Commented:
Hopefully there will be a common field shared between the tables that you could join on ?

Commented:
You have data from the 5 tables to insert only in one textfile, correct?
You can do that using dataflow and script component destination to append data.
Give more details.
regards,
Pedro
www.pedrocgs.blogspot.com
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Need a union all type query where you can have headers, footers etc being generated, but you will also need to sequence them.
It is also possible to incorporate char(13)+char(10) to identify a new line, but generally speaking most data does not lend itself to that type of construct.
The easiest / most success I have had in this type of structured output has been to use a cursor through the joined tables. The cursor can be inside a stored procedure and use that in your BCP command to queryout to a text file.

If you can show tha main columns from your 5 tables, or at least some example, then can show you the different (or most appropriate) techniques above....






Or you can create 5 text files and merge them into one....
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Yep, sure can using either command level copy part1.txt+part2.txt+part3.txt+part4.txt+part5.txt mynewtxt.txt  or could load and re-export I guess....
Command level is easy and simple instead of re-export...
CERTIFIED EXPERT
Top Expert 2012

Commented:
Actually the question that has not been asked (or I have not seen) and will make a fundemental difference to the approach you take is as follows:
Is it a delimited file or is it fixed length?

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
good question... maybe the Asker can add that to the sample-bag...

Author

Commented:
Thanks Guys for your comments.
The text file is a fixed length file
Tables are as follows:

Create table [Table1] (
[RecordType] [nvarchar](2) NOT NULL,
[SEQNO] [nvarchar](6) NOT NULL,
[Address1] [nvarchar](24) NOT NULL,
[Address2] [nvarchar](24) NOT NULL,
[Address3] [nvarchar](24) NOT NULL,
[Phone] [nvarchar](10) NOT NULL,
[Submitter] [nvarchar](6) NOT NULL,
[CreatDate] [nvarchar](6) NOT NULL,
[TestFlag] [nvarchar](1) NOT NULL,
[Reserved] [nvarchar](223) NOT NULL)
 
 
Create table [Table2] (
[RecordType] [nvarchar](2) NOT NULL,
[SEQNO] [nvarchar](6) NOT NULL,
[SubRef] [nvarchar](6) NOT NULL,
[MNo] [nvarchar](10) NOT NULL,
[MName] [nvarchar](30) NOT NULL,
[IndCode] [nvarchar](2) NOT NULL,
[SubIndCode] [nvarchar](3) NOT NULL,
[Reserved] [nvarchar](291) NOT NULL
)

Create table [Table3 - transactions] (
[RecordType] [nvarchar](2) NOT NULL,
[SEQNO] [nvarchar](6) NOT NULL,
[CardNo] [nvarchar](15) NOT NULL,
[CardName] [nvarchar](26) NOT NULL,
[ChargeAmt] [nvarchar](10) NOT NULL,
[ChargeSign] [nvarchar](1) NOT NULL,
[ChargeDate] [nvarchar](6) NOT NULL,
[CardExpiry] [nvarchar](4) NOT NULL,
[AuthCode] [nvarchar](3) NOT NULL,
[TranType] [nvarchar](2) NOT NULL,
[TranRefCode] [nvarchar](7) NOT NULL,
[SpecPCode] [nvarchar](2) NOT NULL,
[TransDesc] [nvarchar](40) NOT NULL,
[AddnTransDesc] [nvarchar](226) NOT NULL
)

Create table [Table4_BTrailer] (
[RecordType] [nvarchar](2) NOT NULL,
[SEQNO] [nvarchar](6) NOT NULL,
[BatchTotTrans] [nvarchar](6) NOT NULL,
[BatchTotDr] [nvarchar](10) NOT NULL,
[BatchTotCr] [nvarchar](10) NOT NULL,
[Filler] [nvarchar](316) NOT NULL
)


Create table [Table5_FTrailer] (
[RecordType] [nvarchar](2) NOT NULL,
[SEQNO] [nvarchar](6) NOT NULL,
[FileTotTrans] [nvarchar](6) NOT NULL,
[FileTotDr] [nvarchar](10) NOT NULL,
[FileTotCr] [nvarchar](10) NOT NULL,
[Filler] [nvarchar](316) NOT NULL
)
Thanks for your assistance
 
 

Commented:
you need 5 sequential dataflows to append the destination file each time you execute each of them.
each dataflow as OELDB Source to get the corresponding section of the textfile structure and append to destination file.
Do you have doubts?
Need an example?!
Regards,
Pedro
www.pedrocgd.blogspot.com

Author

Commented:
Thanks Pedro, this sounds possible. If you could give me an example or a link to one that would be great.
Regards
Alec

Author

Commented:
Pedro,
Further to my last post, I am using SQL2000 as I noticed on your blog these was some info using SSIS (SQL2005).
Regards
Alec

Commented:
Sorry.. I can help you only in SSIS.. not DTS... I dont have SQL 2000 machine anymore! :-(
Only SQL 2005 and SQL 2008
SSIS is the new version of DTS!
regards,
pedro

Author

Commented:
Hi Pedro,
Would it be similar to using vbscript and ActiveX in DTS to perform the exports.
Thanks
Alec

Commented:
The logic is different in DTS and SSIS.
CERTIFIED EXPERT
Top Expert 2012
Commented:
In my view the best way to do this is with a UNION statement.  It will contain 2 columns:
1.  The record type.
2.  The rest of the columns concatenated using CONVERT to get the correct length.

Let me know if this is something you would like to pursue.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Yep, might even include a third which is the batch number which ties the batch details together...

So, is it meant to look like :

table1, Group header
table2 batch header batch1
table3 batch details  batch1
table3 batch details  batch1
table3 batch details  batch1
table4 batch footer  batch1
table2 batch header batch2
table3 batch details  batch2
table3 batch details  batch2
table3 batch details  batch2
table4 batch footer  batch2
table5 Group Footer


and do we export seperate files by "group", or do the contents simply append, or is there only one at a time ?
Are the lengths to correspond to those in the database ?
What about numerics, leading zeros ? Implied or explicit decimal points ?
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Or, does seqno determine the correct row number across the entire group ? Doesn't seem to have numbers / fields that link / logically group pieces of information.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>might even include a third which is the batch number which ties the batch details together<<
Good point, but I think SEQNO is the common denominator.

Author

Commented:
Hi Guys,
Thanks for your comments. The SEQNO is effectively the row number. There are no decimal points. Just assumed that last 2 numbers are decimals.  I have that covered. Good Q about leading zeros. I will need to confirm but expect there will be leading zeros to pad it  out. There should be one export per day and all contents append to create one file.
I will try the union as suggested and advise.
Regards
Alec

Author

Commented:
Solution was as follows: Create a Union with 3 columns - 1 RecordType, 2 SEQNO and 3 was a combination of the other columns using CAST to concatinate the columns to get a common length. I then created a View of the data and using DTS and exported the data to a flat file.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You mean like this ...

Gosh for a 100 point assist, I am glad I didn't do too much, nor suggest a union query from the get go...



Select Export_Line from (
select left((convert(char(2),[RecordType]) + convert(char(6),[SEQNO]) + convert(char(24),[Address1]) + convert(char(24),[Address2]) + convert(char(24),[Address3]) + convert(char(10),[Phone]) + convert(char(6),[Submitter]) + convert(char(6),[CreatDate]) + convert(char(1),[TestFlag]) + convert(char(223),[Reserved]) + replicate(' ',350)),350) as Export_Line from table1
union 
select left((convert(char(2),[RecordType]) + convert(char(6),[SEQNO]) + convert(char(6),[SubRef]) + convert(char(10),[MNo]) + convert(char(30),[MName]) + convert(char(2),[IndCode]) + convert(char(3),[SubIndCode]) + convert(char(291),[Reserved]) + replicate(' ',350)),350) from table2
union 
select left((convert(char(2),[RecordType]) + convert(char(6),[SEQNO]) + convert(char(6),[SubRef]) + convert(char(10),[MNo]) + convert(char(30),[MName]) + convert(char(2),[IndCode]) + convert(char(3),[SubIndCode]) + convert(char(291),[Reserved]) + replicate(' ',350)),350) from table2
union 
select left((convert(char(2),RecordType) + convert(char(6),SEQNO) + convert(char(15),CardNo) + convert(char(26),CardName) + convert(char(10),ChargeAmt) + convert(char(1),ChargeSign) + convert(char(6),ChargeDate) + convert(char(4),CardExpiry) + convert(char(3),AuthCode) + convert(char(2),TranType) + convert(char(7),TranRefCode) + convert(char(2),SpecPCode) + convert(char(40),TransDesc) + convert(char(226),AddnTransDesc) + replicate(' ',350)),350) from [Table3 - transactions]
union 
select left((convert(char(2),RecordType) + convert(char(6),SEQNO) + convert(char(6),BatchTotTrans) + convert(char(10),BatchTotDr) + convert(char(10),BatchTotCr) + convert(char(316),Filler) + replicate(' ',350)),350) from [Table4_BTrailer]
union 
select left((convert(char(2),RecordType) + convert(char(6),SEQNO) + convert(char(6),FileTotTrans) + convert(char(10),FileTotDr) + convert(char(10),FileTotCr) + convert(char(316),Filler) + replicate(' ',350)),350) from [Table5_FTrailer]
) batches

Open in new window

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Just realised - didn't hit submit ealier - had this window still open - in immediate response to acperkins 23698096. Hence my previous comment.


Yep, What is in RecordType and SEQNO ?  And no need to worry about numerics - it has all been converted to NVARCHAR anyway - just hope that numerics or SEQNO has been right aligned otherwise will need to convert to integer for sorting purposes.



Might even be worth (and consider seqno is unique hence union, not union all) :

select recordtype, seqno, export_line
from
(
tabe1 + recordtype + seqno + columns_strung_together_fixed_lenth as export_line
union                                                                            
tabe2 + recordtype + seqno + columns_strung_together_fixed_lenth
union
tabe3 + recordtype + seqno + columns_strung_together_fixed_lenth
union
tabe4 + recordtype + seqno + columns_strung_together_fixed_lenth
union
tabe5 + recordtype + seqno + columns_strung_together_fixed_lenth
) batches
order by seqno

Author

Commented:
Hi Mark,
Your post 23698829 was almost identicle to what I used. Did not see the post until after I had allocated the points. Record type is always 01,02,03,04,05 depending on the table. SeqNo is a 000001,000002 etc and each line is sequential and unique.
Regards
Alec
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Yeah the rotton thing was still sitting in a window waiting to be submitted... Must have been too distracted by writing the actual query. Cheers...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.