How to generate ASCII Fixed Length file
-
Hi Guys, let me jump in here...
How are the headers supposed to be presented in the first line? Are they to be Character separated (using a comma, space, anything)? If not, is there any idea on how they will determine where one column header ends and the next one starts?
Say you have long headers in a file that supposed to be:
column1 CHAR(10)
column2 CHAR(20)
column3 CHAR(20)
... and those headers are CustomerSalutation (18 characters long) CustomerForename (16 char), CustomerSurname (15 char)... Obviously the Forename, surname is no issue as they are shorter than their field length. So let's look at the CustomerSalutation:
Are the file headings supposed to look like:
CustomerSaCustomerForename CustomerSurname
Or Character Separated...
CustomerSalutation CustomerForename CustomerSurname
Or ???
Have you got the file spec required available? It is true that whoever designed the system needs to be, ummm, retrained (cleaned up for the kids).
There will be a way to fulfil these crazy requirements, it may not be pretty, but there's always a way!!
Ade
-
They just gave me the specs.
HEADER
NAME POS LENGTH
-------------------------------------------
DATE 1-4 4
PRODUCTION DATE 5-10 6
SYSTEM TIME 10-16 6
DATA SET NAME 17-21 5
FILLER 22-212
So now it's all clear.
They are gonna have 4 layouts in one file:
HEADER
FIELD NAMES
RECORDS
TRAILER
It's just gonna be more work to generate different lines in different formats.
Thanks guys.
Very special thanks to Ed!
Robert
-
Robert,
Using a DTS package you could have 4 steps that create the 4 different format requirements and write them to files. You could then use an ActiveX script and the FileSystemObject to combine those files in to one.
Example statement of data to output:
CREATE TABLE ##export(customerID CHAR(11), companyName CHAR(50), city CHAR(14), postalCode CHAR(10), country CHAR(20))
INSERT INTO ##export
SELECT TOP 10 CustomerID, companyName, city, postalcode, country FROM northwind.dbo.Customers
Step 1 - Create Header
Details of which unknown at present
Step 2 - Create Field names
If you write the data to a Global temporary table you could use a statement like:
DECLARE @column_name_line VARCHAR(4000)
SELECT @column_name_line = ''
SELECT @column_name_line = @column_name_line + sc.name + ' ' -- Insert whatever separtor here
FROM tempdb.dbo.sysobjects so
INNER JOIN tempdb.dbo.syscolumns sc
ON so.id = sc.id
WHERE so.name LIKE '%#export%'
...to create a line of column headers. You can store this in a GlobalVariable for now or output to a file using xp_cmdshell and ECHO or BCP.
Step 3 - Create Data
Have the Global Temporary Table columns all as length CHAR at the required length. BCP can then be used to output the data to a file.
EXEC xp_cmdshell 'bcp "##export" out "\\myserver\mydir\myfile.txt" -c -SSERVER -E -t""'
Note the -t"" to remove all field terminators (as tab is default I think)
Step 4 - Create Footer
Details Unkown
After the 4 data production steps, have an ActiveX script like:
Dim fso, headerFile, fieldsFile, dataFile, footerFile, finalFile
Dim fileContent
Set fso = CreateObject("Scripting.FileSystemObject")
' Input Files
Set headerFile = fso.OpenTextFile(myheaderFile, 1)
Set fieldsFile = fso.OpenTextFile(myfieldsFile, 1)
Set dataFile = fso.OpenTextFile(mydataFile, 1)
Set footerFile = fso.OpenTextFile(myfooterFile, 1)
' Output File
Set finalFile = fso.OpenTextFile(myfinalFile, 2)
' Read in files and write final files
fileContent = headerFile.ReadAll
finalFile.Write fileContent
fileContent = fieldsFile.ReadAll
finalFile.Write fileContent
fileContent = dataFile.ReadAll
finalFile.Write fileContent
fileContent = footerFile.ReadAll
finalFile.Write fileContent
Does this make sense? Any q's, fire away!
Ade
-
Well Veteran,
It's some really interesting advaned approach.
Let me absorb it.
HEADER and FOOOTER specs are known. Look
at my previous message.
I actually liked Ed's approach
to have everything in one SELECT statement.
First I'try to implement it using this method.
If it doesn't work I'll try your approcah, Veteran.
Thanks a lot.
(there are some advanced guys. we just need to find them..)
-
Ed,
Just a quick question.
Why do I need to use SELECT LEFT(Field1+' ',3)
Not just SELECT Field1?
Is this what determines POS 1-3 for the output?
Thanks,
Robert
-
If you know field1 is defined as a char(3) field then "select field1" is the same as "select left(field1,3)" The "+' '" simply pads spaces on the end if it is short and the left function ensures that it is no more than 3 positions long.
So the question is if you know the field is char(3) why go to the trouble.
You are now at the frontier where modern data base approaches come into contact with old style mainframe approach. The data base approach says the field specs are in the data base layout and the code is independent of them. If the field size is changed, appropriate table defs are changed and life goes on. But for your code to guarantee the exact layout you have been ask to create, you have to enforce the field never being longer than 3 positions, otherwise everything downstream in the layout is wrong.
As for you question what determines POS 1-3 of the output, It is the first 3 positions of the final "mega-field" that you are creating by concatenating all the individual fields together.
-
Makes sense.
Ed,
I didn't catch it last time.
What's the purpose of Literal # in your example?
Thanks,
Robert
-
Just an example of putting a literal into the layout. Lots of old fixed format specs have fixed values in particular positions. It goes back to punch cards, if you don't know what they are don't ask. I haven't seen one in 35 years
-
Hi Ed,
I just produced a file
and then wondered how I can check if
Column Positions were set correctly.
So I created a Text File Data Source in DTS,
pointed it to the newly generated file "gh_Ed.txt",
selected Fxed-length, clicked Next and....
the positions are wrong. I mean those arrows that indicate
column positions in DTS are not wehe I expect them to be.
For example,
field1 (BENEFICIARY-NUMBER)
should be at pos 1-9.
Instead in DTS it shows pos 1-12
(do you see a picture?)
Here is my SQL.
---------------------------------------
SELECT
LEFT(dbo.IPBeneNo.BeneficiaryNo, 9) AS [BENEFICIARY-NUMBER],
LEFT('P', 3) AS [ADDRESS-ID-NUMBER]
LEFT(dbo.Address.Country + '', 3) AS [RESIDENCE-CODE],
LEFT(dbo.Address.StreetNo + ' ' + dbo.Address.StrName + ' ', 30) AS [STREET-LINE1]
---------------------------------------
Maybe something is wrong with it?
Thanks,
Robert
-
Ed,
Just forgot to mention to you this.
I created a SQL Server view and then in DTS package I do a transformation
from that view into fixed-length file.
This is how I generate a file.
Robert
-
Adrian Nichols,
Since "Format all in SQL" method is on hold now
can I ask you a question?
I'm referring to your comment:
Step 3 - Create Data
Have the Global Temporary Table columns all as length CHAR at the required length. BCP can then be used to output the data to a file.
EXEC xp_cmdshell 'bcp "##export" out "\\myserver\mydir\myfile.txt" -c -SSERVER -E -t""'
Note the -t"" to remove all field terminators (as tab is default I think)
Adrian,
This step should be inside DTS as a command? Right?
Then I doubt ##export will be accessible. I actually never tried it.
I'll try it now..
Robert
-
Hi Adrian,
So I created step 3 in DTS
that executes a batch file "exec_bcp_from_##export.bat"
Here is the content of this file:
EXEC xp_cmdshell bcp "##export" out "D:\gh\2files\test\adrian.txt" -c -SCSSAN\TNV00PCDSQL -t"" -Urobert -P13456807
I ran DTS and this step failed with the error:
...Process returned code 1, which does not match the specified SuccessReturnCode of 0
Too bad I can't test this bcp command because it's using temp table.
Adrian!
Is there any way to check if ##export was populated successfully?
Any suggestions?
Robert
-
Hi, with reference to the BCP command:
The temporary table is a global temporary table (## prefix, not #). This type of table can be used between processes. To check the correct creation and population of the global temporary table you can use query analyzer to SELECT * FROM ##export. The table should also appear in the object-tree in the Object-Explorer under Tempdb > User tables; you may have to refresh the tree to see it.
I always use xp_cmdshell in a SQL task to execute BCP commands; this is so I can trap errors easily.
-
Hi Adrian,
I created an "Execute SQL" Step in DTS.
It does the following:
CREATE TABLE ##export
(
[BENEFICIARY-NUMBER] char(9),
[ADDRESS-ID-NUMBER] char(1),
[RESIDENCE-CODE] char(3),
[STREET-LINE1] char(30),
[STREET-LINE2] char(30),
[DOMICILE-TYPE] char(1),
[UNIT-NUMBER] char(5),
[CITY] char(28),
[PROVINCE-STATE] char(3),
[POSTAL-CODE-ZIP] char(10),
[FOREIGN-ADDRESS-TEXT] char(30),
[FILLER] char(59)
)
INSERT INTO ##export
SELECT * from dbo.vw_Ed_FINAL
select top 1 [BENEFICIARY-NUMBER] from ##export
In Disconnect Edit I assign the value from the last statement to a GlobalVariable gvTest.
Step gets executed without errors but gvTest is not populated.
In Query Analyzer it says
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '##export'
I don't think ##export is visible in Query Analyzer.
Looks like its scope is DTS session/connection.
Query Analyzer has a different connection.
Try command sp_who2 and you'll see process id's are different so i guess connections are different.
Thanks,
Robert
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply