Export Fixed Length with a Tab between 80 and 81

  • Thread starter Thread starter Eduardo Leaderez
  • Start date Start date
E

Eduardo Leaderez

I need to be able to export to a mixed fixed text/tab delimited format because our vendor requires that a tab be entered between the 80th character and the 81st character of the text delimited file.

Can anybody help me with the code I would need in order to do this? Assume that the data has been exported and I have a button linked to a VBA app that opens the .txt file and inserts the tab at pos 81.

Unless there is an easier way.

Here is an example of what needs to be output. Only I need a Tab between the 80th and 81st char.

12345 1 3/12/02 HHOC THIS IS THE DESCRIPTION X.TIF
12345678901234567 2 3/16/04 HHOC ANOTHER DESCRIPTION Y.TIF
12345678901234567890 3 3/12/01 HHOC YET ANOTHER XYZ.TIF
12345678 4 1/16/59 HHOC 1 123.TIF
123456789012 1 2/4/78 HHOC 2 BBB.TIF
1234567890123456789012 1 HHOC 12 WWW.TIF
123456789 3 2 HHOC 13 ZZZ.TIF
12345678901234567 4 2/4/78 HHOC THIS IS EVEN YET ANOTHER DESCRIPTION A1.tif
12345678901234901 5 3 HHOC GOING ALL THE WAY TO THE END OF THE FI2.tif
1234567890123456789016 1234 HHOC THIS IS EVEN YET ANOTHER DESCRIPTION A6.tif
123456789012 7 1 HHOC ANOTHER DESCRIPTION 12345.tif
12345678902345 8 2/4/78 HHOC ANOTHER DESCRIPTION 20.tif
123456789012345678 9 12/14/03HHOC ANOTHER DESCRIPTION 4rf.tif
12345678901234567890110 1 V 123456789012345678901234567 xxxxx.tif
1231 1 2/4/78 HHOC ANOTHER DESCRIPTION yyyyy.tif
123459012345678901 2 2/4/78 VHHOC ANOTHER DESCRIPTION 212121.tif
123456745678901 3 12/14/03HHOC 123456789012323456789012345678 222222.tif
1234567890123456789014 2/4/78 HHOC ANOTHER DESCRIPTION 123123123.ti
1234565678901 5 12/14/03HHOC ANOTHER DESCRIPTION 676767.tif
123456712345678901 6 12/14/03HHOC 12345678901234567890123456789 232342345.ti

What I already can create easily is the file without the tab.

I have pretty much given up on this being a simple task not requiring VBA coding.

I have tried to insert a 1 char text field containing the ascii char chr$(9) and that does not seem to work.

Any help would be greatly appreciated.

Thanks

Ed
 
Eduardo,

If you have already created the file and the only thing missing is the tab, then it's quite easy to insert it by means of a few lines of VBA code which loop through each line of the file and insert the tab character. Here's some example code (which puts the new lines in a new file):

Sub Insert_Tabs()
Dim inputline As String, outputline As String

fixfile = "PathAndFileName.txt" 'file to read from
tabfile = "AnotherPathAndFileName.txt" 'file to write to

Open fixfile for Inout As #1
Open tabfile for Output As #2

Do Until EOF(1)
Line Input #1, inputline
outputline = Left(inputline, 80) & chr(9) & Right(inputline, len(inputline)-80)
Print #2, outputline
Loop

Close #1
Close #2

End Sub

HTH,
Nikos
I need to be able to export to a mixed fixed text/tab delimited format because our vendor requires that a tab be entered between the 80th character and the 81st character of the text delimited file.

Can anybody help me with the code I would need in order to do this? Assume that the data has been exported and I have a button linked to a VBA app that opens the .txt file and inserts the tab at pos 81.

Unless there is an easier way.

Here is an example of what needs to be output. Only I need a Tab between the 80th and 81st char.

12345 1 3/12/02 HHOC THIS IS THE DESCRIPTION X.TIF
12345678901234567 2 3/16/04 HHOC ANOTHER DESCRIPTION Y.TIF
12345678901234567890 3 3/12/01 HHOC YET ANOTHER XYZ.TIF
12345678 4 1/16/59 HHOC 1 123.TIF
123456789012 1 2/4/78 HHOC 2 BBB.TIF
1234567890123456789012 1 HHOC 12 WWW.TIF
123456789 3 2 HHOC 13 ZZZ.TIF
12345678901234567 4 2/4/78 HHOC THIS IS EVEN YET ANOTHER DESCRIPTION A1.tif
12345678901234901 5 3 HHOC GOING ALL THE WAY TO THE END OF THE FI2.tif
1234567890123456789016 1234 HHOC THIS IS EVEN YET ANOTHER DESCRIPTION A6.tif
123456789012 7 1 HHOC ANOTHER DESCRIPTION 12345.tif
12345678902345 8 2/4/78 HHOC ANOTHER DESCRIPTION 20.tif
123456789012345678 9 12/14/03HHOC ANOTHER DESCRIPTION 4rf.tif
12345678901234567890110 1 V 123456789012345678901234567 xxxxx.tif
1231 1 2/4/78 HHOC ANOTHER DESCRIPTION yyyyy.tif
123459012345678901 2 2/4/78 VHHOC ANOTHER DESCRIPTION 212121.tif
123456745678901 3 12/14/03HHOC 123456789012323456789012345678 222222.tif
1234567890123456789014 2/4/78 HHOC ANOTHER DESCRIPTION 123123123.ti
1234565678901 5 12/14/03HHOC ANOTHER DESCRIPTION 676767.tif
123456712345678901 6 12/14/03HHOC 12345678901234567890123456789 232342345.ti

What I already can create easily is the file without the tab.

I have pretty much given up on this being a simple task not requiring VBA coding.

I have tried to insert a 1 char text field containing the ascii char chr$(9) and that does not seem to work.

Any help would be greatly appreciated.

Thanks

Ed
 
Back
Top