Script to Convert TAB Delimited File to Fixed Width Format

[NickC111]

I have an export file that is created in TAB delimited format. Record example below:

8000004449 5910.00 0.00 5910.00 XX 2012 1 5594.90 315.10 Extract
8000006341 6732.00 0.00 6732.00 XX 2012 1 6372.93 359.07 Extract
8000009894 5082.00 0.00 5082.00 XX 2012 1 4810.86 271.14 Extract
8000018985 10854.00 0.00 10854.00 XX 2012 1 10275.09 578.91 Extract
8000022166 11334.00 0.00 11334.00 XX 2012 1 10729.44 604.56 Extract
8000024096 29952.00 0.00 29952.00 XX 2012 1 28354.50 1597.50 Extract
8000025690 37650.00 0.00 37650.00 XX 2012 1 35642.13 2007.87 Extract
8000025850 858.00 0.00 858.00 XX 2012 1 812.19 45.81 Extract
8000028875 39846.00 0.00 39846.00 XX 2012 1 37720.88 2125.12 Extract

I need a script that will copy the data from the above file and use it to create a new file called import in Fixed Width format.

Field Lengths are all 15

Any help would be great

 

[jges]

As a general strategy, I would:[ol 1]
[li]open the file and read it line by line[/li]
[li]use the split function on each line to break it into individual values (split(line, vbtab)[/li]
[li]use the space function to pad each value[/li]
[li]combine the array of values back into a single line[/li]
[li]write the line to the new file[/li]
[/ol]

 

[NickC111]

Thank You - can you show me how this can be done?

 

[jges]

What code do you have so far?

 

[SkipVought]

Please post the specifications for the FIXED WIDTH of each column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Please post the specifications for the FIXED WIDTH of each column.

That would be:

Field Lengths are all 15

Cheers
Paul Edstein
[MS MVP - Word]

 

[Swi]

Something like this should work for you.

Option Explicit
Dim fso
Dim InStream
Dim OutStream
Dim InputData
Dim aryTemp
Dim i

Set fso = CreateObject("Scripting.FileSystemObject")
Set InStream = fso.OpenTextFile("\\secpykpw01\RedirectedFolders\sdelp\Desktop\Test.txt", 1, True)
Set OutStream = fso.CreateTextFile("\\secpykpw01\RedirectedFolders\sdelp\Desktop\Test2.txt", True)

Do
    InputData = InStream.ReadLine
    aryTemp = Split(InputData, vbTab)
    For i = LBound(aryTemp) To UBound(aryTemp)
        OutStream.Write aryTemp(i) & Space(15 - Len(Trim(aryTemp(i))))
    Next
    OutStream.WriteLine
Loop Until InStream.AtEndOfStream
InStream.Close
OutStream.Close
Set fso = Nothing
MsgBox "Done!"

Swi