Exporting to fixed width text file

D

Debbie

Hello -
Can anyone tell me the best way to export data to a fixed
width text file? I have created a string of spaces and
then placed the text in it using the Mid function but I
can't get the fields to line up as they would in fixed
width fields. (I don't understand why, I specify where
each field is supposed to begin.) Any advise is
appreciated. Thanks in advance,
Debbie
 
J

Joe Fallon

1. Use TransferText and an Export Spec.
Build the spec manually once by exporting the table using right click
Export.
Be sure to click Advanced and save the spec.

2. Use code:
Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(![key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(![Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(![TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(![Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(![Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(![Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(![Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(![Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub
 
G

Guest

-----Original Message-----
1. Use TransferText and an Export Spec.
Build the spec manually once by exporting the table using right click
Export.
Be sure to click Advanced and save the spec.

2. Use code:
Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset
Click to expand...
("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(! [key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(! [Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(!
Click to expand...
[TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(!
Click to expand...
[Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(! [Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(!
Click to expand...
[Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(! [Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(! [Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub


--
Joe Fallon
Access MVP



Debbie said:
Hello -
Can anyone tell me the best way to export data to a fixed
width text file? I have created a string of spaces and
then placed the text in it using the Mid function but I
can't get the fields to line up as they would in fixed
width fields. (I don't understand why, I specify where
each field is supposed to begin.) Any advise is
appreciated. Thanks in advance,
Debbie
Click to expand...


.
Click to expand...
 
G

GRayL

Debbie: Don't forget that when viewing a "fixed width"
text file you must not use a proportional font. Depending
on what you have as a default font in your text editor,
your system may be working correctly but you can't "SEE"
it.

Ray
-----Original Message-----
1. Use TransferText and an Export Spec.
Build the spec manually once by exporting the table using right click
Export.
Be sure to click Advanced and save the spec.

2. Use code:
Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset
Click to expand...
("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(! [key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(! [Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(!
Click to expand...
[TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(!
Click to expand...
[Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(! [Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(!
Click to expand...
[Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(! [Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(! [Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub


--
Joe Fallon
Access MVP



Debbie said:
Hello -
Can anyone tell me the best way to export data to a fixed
width text file? I have created a string of spaces and
then placed the text in it using the Mid function but I
can't get the fields to line up as they would in fixed
width fields. (I don't understand why, I specify where
each field is supposed to begin.) Any advise is
appreciated. Thanks in advance,
Debbie
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting Fixed Width Text 3
Exporting Fixed Width Overlapping Fields 1
Fixed-Width text file export 1
Fixed Width - No delimiters - ASCII Text File 7
Exporting Fixed Width Text File 4
Importing Fixed Length File into Access 2007 1
Text columns exporting to currency columns 5
Exporting fixed width text files 3

Top