Parsing text file is sloooooow. Is my script unoptimized?

[Jud420 0]

Hey there, I'm writing a script that takes an input of a tab delimited text file and splits it into two. These files will eventually be imported into a SQL database, but I need to split the files because they go to two different tables. Each line that is exported will represent one record in the ending table, and I want to keep the data together, so I am creating a 30 digit random ID number and placing it at the beginning of each line. First I take out the header data from line 2 of my input file, then i take the info from lines 4-endOfFile and put them into their respective output files.

My script works, but for a 21mb file its taking 3 minutes to process. Does anybody know a faster way of doing this?

Here is my code:

#include <Array.au3>

Dim $number[30]
$i = 0
Do
    $number[$i] = Round(Random(1,9),0)
    $i = $i + 1
Until $i = 30

$newNumber = _ArrayToString($number, "")

consoleWrite(@CR & $newNumber)

$fileIn = FileOpen("example.txt",0)

$headerLine = FileReadLine($fileIn, 2)
FileWrite("header_" & $newNumber & ".txt", $newNumber & @TAB & $headerLine)

FileWrite("info_" & $newNumber & ".txt", $newNumber & @TAB & FileReadLine($fileIn, 4))
$infoFile = FileOpen("info_" & $newNumber & ".txt", 1)

$i = 1 
While 1
    $line = FileReadLine($fileIn)
    If @error = -1 Then ExitLoop
    if $line = "" Then ExitLoop
    If $line = @TAB Then ExitLoop
    FileWrite($infoFile, $newNumber & @TAB & $i & @TAB & $line & @CRLF)
    $i = $i + 1
    
    
Wend

FileClose($infoFile)
FileClose($fileIn)

attached is the example file, which is only 62kb, but is in the same format as the 21mb file.

THanks.

example.txt

Edited December 13, 2007 by Jud420

[weaponx 15]

Try this instead:

#include <Array.au3>
#include <File.au3>

Dim $fileInArray

$newNumber = ""
For $X = 1 to 30
    $newNumber &= Random(1,9,1)
Next

ConsoleWrite($newNumber & @CRLF)

_FileReadToArray ("example.txt", $fileInArray )

$headerLine = $fileInArray[2]
FileWrite("header_" & $newNumber & ".txt", $newNumber & @TAB & $headerLine)

$infoFile = FileOpen("info_" & $newNumber & ".txt", 1)
FileWrite($infoFile, $newNumber & @TAB & $fileInArray[4])

;Append generated number to every array element
;Start at array element 4 (Line 4)
For $X = 4 to $fileInArray[0]
    If $fileInArray[$X] = @TAB Then ExitLoop
    $fileInArray[$X] = $newNumber & @TAB & $X & @TAB & $fileInArray[$X]
Next

;Output array to file
;Start at array element 4 (Line 4)
_FileWriteFromArray($infoFile,$fileInArray,4)
FileClose($infoFile)

Edited December 13, 2007 by weaponx

[SmOke_N 208]

See how close this is without opening and closing the file to read and write to it every loop.

Func _FileCreateOutput($hInFile, $hFileHeaderOut, $hFileInfoOut)
    Local $sNum
    While StringLen($sNum) < 30
        $sNum &= Random(1, 9, 0)
    WEnd
    
    Local $aFileRead = StringSplit(StringStripCR(FileRead($hInFile)), @LF)
    FileWrite($hFileHeaderOut, $sNum & @TAB & $aFileRead[2] & @CRLF)
    FileWrite($hFileInfoOut, $sNum & @TAB & $aFileRead[4] & @CRLF)
    
    Local $sHoldString
    For $i = 5 To $aFileRead[0]
        $sHoldString &= $sNum & @TAB & ($i - 4) & @TAB & $aFileRead[$i] & @CRLF
    Next
    Return FileWrite($hFileInfoOut, $sHoldString)
EndFunc

**Note, not tested.

Edited December 13, 2007 by SmOke_N

[Jud420 0]

See how close this is without opening and closing the file to read and write to it every loop.

Func _FileCreateOutput($hInFile, $hFileHeaderOut, $hFileInfoOut)
    Local $sNum
    While StringLen($sNum) < 30
        $sNum &= Random(1, 9, 0)
    WEnd
    
    Local $aFileRead = StringSplit(StringStripCR(FileRead($hInFile)), @LF)
    FileWrite($hFileHeaderOut, $sNum & @TAB & $aFileRead[2] & @CRLF)
    FileWrite($hFileInfoOut, $sNum & @TAB & $aFileRead[4] & @CRLF)
    
    Local $sHoldString
    For $i = 5 To $aFileRead[0]
        $sHoldString &= $sNum & @TAB & ($i - 4) & @TAB & $aFileRead[$i] & @CRLF
    Next
    Return FileWrite($hFileInfoOut, $sHoldString)
EndFunc

**Note, not tested.

Had to change a few things around to get the random number into the filename, but that got it down to 12 seconds instead of 3 mins. Thanks a bunch.

[SiteMaze 0]

Use Perl

1. Copy the Active Perl binary Perl.exe and Perl.dll

2. Write a perl script to do the file manipulation

3. Run the perl from AutoIT

$String_Perlcommand = " Library\example.pl "
    $Location_Perlexecutor = @ScriptDir & "\Library\perl.exe"
    $fullpath_arguments = $f_arg1 & " "" "" " & $f_arg2
    $temp_command = """" & $Location_Perlexecutor & """ " & $String_Perlcommand & " """ & $fullpath_arguments & """ "

    $PID = RunWait($temp_command, @ScriptDir&"\", @SW_HIDE)

Make use of both worlds. Perl is very fast at file manipulation. If you search my previous post, I had problem with the speed of 100,000 line file.

[randallc 0]

Use Perl

1. Copy the Active Perl binary Perl.exe and Perl.dll

2. Write a perl script to do the file manipulation

3. Run the perl from AutoIT

$String_Perlcommand = " Library\example.pl "
    $Location_Perlexecutor = @ScriptDir & "\Library\perl.exe"
    $fullpath_arguments = $f_arg1 & " "" "" " & $f_arg2
    $temp_command = """" & $Location_Perlexecutor & """ " & $String_Perlcommand & " """ & $fullpath_arguments & """ "

    $PID = RunWait($temp_command, @ScriptDir&"\", @SW_HIDE)

Make use of both worlds. Perl is very fast at file manipulation. If you search my previous post, I had problem with the speed of 100,000 line file.

Hi,

Looks good; is that faster than vbscript?

We can use scriptObject for vbs

Best, Randall