Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-10-04
11
Medium Priority
?
4,948 Views
Last Modified: 2010-05-18
I am looking for a way to write a dynamic Windows Script to convert a Fixed Length Text File to CSV format...
0
Comment
Question by:FreightTrain
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 29

Expert Comment

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

Cheers
0
 
LVL 9

Expert Comment

by:ghostdog74
ID: 20019739
best is to show a sample if possible
0
 

Author Comment

by:FreightTrain
ID: 20019764
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...

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

Expert Comment

by:BillDL
ID: 20020061
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?
0
 
LVL 29

Expert Comment

by:matrixnz
ID: 20020092
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

0
 

Author Comment

by:FreightTrain
ID: 20021889
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?
0
 
LVL 9

Expert Comment

by:ghostdog74
ID: 20022760
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.?
0
 

Author Comment

by:FreightTrain
ID: 20024523
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...
0
 
LVL 29

Expert Comment

by:matrixnz
ID: 20026022
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---------->
0
 
LVL 29

Accepted Solution

by:
matrixnz earned 1200 total points
ID: 20026051
Sorry real late night, just read my post and it's dribble :-)  Hopefully this is more understandable (but I wouldn't hold your breath) :-).

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

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

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

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

Is this correct if so 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 the 9th 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 the 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---------->
0
 
LVL 9

Assisted Solution

by:ghostdog74
ghostdog74 earned 800 total points
ID: 20026132
you can try this vbscript


Set objFSO = CreateObject("Scripting.FileSystemObject")
myFile = "c:\temp\input.txt"
Set objFile = objFSO.OpenTextFile(myFile,1)
Do Until objFile.AtEndOfStream
      line=objFile.ReadLine
      WScript.Echo Mid(line,1,8) &"|"& Mid(line,9,11)&"|"& Mid(line,12,20)
Loop

usage: cscript /nologo myscript.vbs > newfile
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Migration of Exchange mailbox can be done with the ExProfre.exe tool. But at times, when the ExProfre.exe tool migrates the Exchange Server user profile, it results in numerous synchronization problems. Synchronization error messages appear in the e…
We have adopted the strategy to use Computers in Student Labs as the bulletin boards. The same target can be achieved by using a Login Notice feature in Group policy but it’s not as attractive as graphical wallpapers with message which grabs the att…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Screencast - Getting to Know the Pipeline
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question