Windows script to convert a Fixed Length Text file to CSV format

Can you explain further what your Fixed Length Text file is?  Where you wish to place the delimiters in the like?

Cheers

best is to show a sample if possible

Initial File format would be

123      John Smith   34 E. West Plains
1234    Bob  Boozer123 S. 1st Avenue
123456Jen  Jones   3
556      Beth Andes 3 N. First  

After adding a pipe delimiter, it would be:

123      |John Smith   |34 E. West Plains  |
1234    |Bob  Boozer|123 S. 1st Avenue|
123456|Jen  Jones   |3                            |
556      |Beth Andes |3 N. First                |

Notice how each columns has a fixed length.  I left the spaces in the delimited file, just to illustrate the filxed column width.

Thank you in advance for your assistance...

Hi FreightTrain

The file you started with seems to be a Tab-Delimited file, and you want to convert the tab spaces to commas?

Just to verify something, can you open an example file in Notepad, create a new line, and type the same text that would be in the first column of the line above, then tab to ensure that the spaces are Tabs and not a fixed number of single character spaces as would be applied by the spacebar.  Also make sure that the spaces are single tabs rather than more than one tab space.

If the spaces aren't tabs, but single spaces, then we would probably need to know how many spaces there are between each of the columns, especially if they are different for each column.

From that I'm sure we can write a script to convert the spaces to tabs, but does it have to be a Windows or VB Script rather than just a simple batch file?

So when you say fixed length are these actually fixed all do they grow depending on the amount of characters in the number, name, address i.e.

Column1 = 6 Characters
Column2 = ?? Characters
Column3 = ?? Characters

No, they will not grow...for example:

Column1 = Always 8 characters
Column2 = Always 11 characters
Column3 = Always 18 characters

Is there a better more technical name for a file like this, other than fixed length?

my suggestion is to tackle the problem at the source. What type of application generates this file in the first place? Maybe you can add the delimiter at the source.?

I wish I could!!!  :-)

I receive these files in from an external source and have no control over whether or not they are delimited or fixed length.  I unfortunately have to deal with the fixed length file the way it is...

Hi FreightTrain

I copied your text below above into C:\Test\Test.txt added spaces to make up the columns of 8 characters, 11 characters, 18 characters

Then using a AutoIT I pulled this information from C:\Test\Test.txt to C:\Test\Test.csv.  I hope that's correct.

Can you please test:

Download and Install AutoIT http://www.autoitscript.com/autoit3/downloads.shtml

Open Notepad or Scite(AutoIT Editor) and copy and paste the code below, then save the file as C:\Test\Test.au3 (it doesn't really matter where you save it) copy your text file into the same folder and name it test.txt.

Right Click the C:\Test\Test.au3 and click either "Run Script" just to test or Compile to create a file C:\Test\Test.exe

What the script does, basically it opens test.txt and reads each line, it counts 8 characters from the left and then counts 11 characters from 9 character, then it counts 18 characters from the right.  It then appends this information to C:\Test\Test.csv (or where ever you run the script from) if C:\Test\Test.csv doesn't exist it is created.  Also you'll find that if you run this script again it will append this information to the file i.e. not overwrite tthe previous information.

Hope that helps.

Cheers
 
<----------Code Starts Below Here---------->
$file1 = FileOpen("test.txt", 0)
$file2 = FileOpen("test.csv", 1)

If $file1 = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf

While 1

$line = FileReadLine($file1)

$column1 = StringLeft($line, 8)
$column2 = StringMid($line, 9, 11)
$column3 = StringRight($line, 18)

If $column1 = '' Then ExitLoop

FileWrite($file2, $column1 & "," & $column2 & "," & $column3 & @CRLF)

Wend

FileClose($file1)
FileClose($file2)
<----------Code Ends Above Here---------->