• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Exporting multiple SQL tables to a single text file

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.
0
alpet101
Asked:
alpet101
  • 8
  • 8
  • 4
  • +3
2 Solutions
 
St3veMaxCommented:
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...
0
 
alpet101Author 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?
0
 
St3veMaxCommented:
Hopefully there will be a common field shared between the tables that you could join on ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PedroCGDCommented:
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
0
 
Mark WillsTopic AdvisorCommented:
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....






0
 
MohammedUCommented:
Or you can create 5 text files and merge them into one....
0
 
Mark WillsTopic AdvisorCommented:
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....
0
 
MohammedUCommented:
Command level is easy and simple instead of re-export...
0
 
Anthony PerkinsCommented:
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?

0
 
Mark WillsTopic AdvisorCommented:
good question... maybe the Asker can add that to the sample-bag...
0
 
alpet101Author 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
 
 
0
 
PedroCGDCommented:
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
0
 
alpet101Author Commented:
Thanks Pedro, this sounds possible. If you could give me an example or a link to one that would be great.
Regards
Alec
0
 
alpet101Author 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
0
 
PedroCGDCommented:
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
0
 
alpet101Author Commented:
Hi Pedro,
Would it be similar to using vbscript and ActiveX in DTS to perform the exports.
Thanks
Alec
0
 
PedroCGDCommented:
The logic is different in DTS and SSIS.
0
 
Anthony PerkinsCommented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
alpet101Author 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
0
 
alpet101Author 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.
0
 
Mark WillsTopic AdvisorCommented:
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

0
 
Mark WillsTopic AdvisorCommented:
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
0
 
alpet101Author 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
0
 
Mark WillsTopic AdvisorCommented:
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...
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 8
  • 8
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now