Read delimited file and process task for each line

[wjessee]

I am looking for how to make AutoIt read a flat file line by line that has been delimited by a specified character like a "," or "^" and place each field in a variable to be processed by the script.

Currently what I do is edit the script by hand for each line and run the script.

I would like to make it do a while loop and execute the script line by line with the values from the csv. The csv has numerous lines with different items on each line.

CSV Format:

$NAME,$SIZE,$ID,$PRICE,$COST

$NAME,$SIZE,$ID,$PRICE,$COST

$NAME,$SIZE,$ID,$PRICE,$COST

Script:

WinWaitActive("This window's title")

Send("!n")

Sleep(1000)

Send("{TAB}G{TAB}YL{TAB}$NAME{TAB}{TAB}{TAB}$SIZE{TAB}{TAB}{TAB}{TAB}{TAB}$ID{TAB}{TAB}{TAB}$PRICE{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}$COST{TAB}$COST{TAB}{SPACE}")

Sleep(1000)

Any help would be appreicated.

--W

[tutor2000]

Look up stringsplit in the help file

Rick

[Arctor]

  Quote

I would like to make it do a while loop and execute the script line by line with the values from the csv.

Tis is taken from the helpfile. (FileReadLine)

$file = FileOpen("list.txt", 0)
; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
; Read in lines of text until the EOF is reached
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    MsgBox(0, "Line read:", $line); <---just to control the lines. you can do your stuff with each line instead of the msgbox
Wend
FileClose($file)

arctor

[wjessee]

Thanks, but how do I parse the txt file with the delimiter specified and add each field to a variable?

--W

[Jos]

  wjessee said:

Thanks, but how do I parse the txt file with the delimiter specified and add each field to a variable?

--W

As tutor said... stringsplit will spilt up your record into an array which you then can use... have a look in the helpfile for stringsplit...

[wjessee]

Thanks, I did, but I don't understand it very well as I not really a programmer.

--W

[tutor2000]

  wjessee said:

Thanks, I did, but I don't understand it very well as I not really a programmer.

--W

Arrays still give me a hard time as a newbie as well

You're not starting off with a very easy project that's for sure. It would take me as much time or longer to write it for you than it would for you to do it.

You'll need to lookup For...Next and Ubound to get an idea of what to do with the array once you have used your stringsplit

Hope I helped a tad

Rick

[Jos]

  wjessee said:

Thanks, I did, but I don't understand it very well as I not really a programmer.

--W

Well you do the following:

Assume your record is read into var $Irec:

"NAME,SIZE,ID,PRICE,COST"

Code:

$Rec = StringSplit($irec,",")

This will create an array will the following content:

$Rec[0] = 5 ; the number of fields...

$Rec[1] = "NAME"

$Rec[2] = "SIZE"

$Rec[3] = "ID"

$Rec[4] = "PRICE"

$Rec[5] = "COST"

Ok ?

[wjessee]

Ok, so here is what I have come up with, but it keep printing NAME on each line.

$file = FileOpen("list.txt", 0)

; Check if file opened for reading OK

If $file = -1 Then

MsgBox(0, "Error", "Unable to open file.")

Exit

EndIf

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($file)

If @error = -1 Then ExitLoop

$Rec = StringSplit($line,"^")

$Rec[0] = 5

$Rec[1] = "NAME"

$Rec[2] = "SIZE"

$Rec[3] = "ID"

$Rec[4] = "PRICE"

$Rec[5] = "COST"

WinWaitActive("Untitled - Notepad")

Send("!n")

Sleep(1000)

Send($Rec[1])

Send("{ENTER}")

Sleep(1000)

Wend

FileClose($file)

[Jos]

I guess i did confuse you here...

I meant with $Rec[1] = "NAME" that $Rec[1] will contain the value "NAME"...

So try this and see if that works...

$FILE = FileOpen("list.txt", 0)
; Check if file opened for reading OK
If $FILE = -1 Then
   MsgBox(0, "Error", "Unable to open file.")
   Exit
EndIf
; Read in lines of text until the EOF is reached
While 1
   $LINE = FileReadLine($FILE)
   If @error = -1 Then ExitLoop
   $REC = StringSplit($LINE, "^")
   Send($REC[1])
   Send("{ENTER}")
   Sleep(1000)
Wend
FileClose($FILE)

Edited July 2, 2004 by JdeB

[wjessee]

Thanks, This works well, Here is what I came up with. Is this the best way to do it?

$file = FileOpen("list.txt", 0)

; Check if file opened for reading OK

If $file = -1 Then

MsgBox(0, "Error", "Unable to open file.")

Exit

EndIf

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($file)

If @error = -1 Then ExitLoop

$Rec = StringSplit($line,"^")

WinWaitActive("Untitled - Notepad")

Send("!n")

Sleep(1000)

Send($Rec[1])

Send("{TAB}")

Send($Rec[2])

Send("{TAB}")

Send($Rec[3])

Send("{TAB}")

Send($Rec[4])

Send("{TAB}")

Send($Rec[5])

Send("{TAB}")

Send($Rec[5])

Send("{ENTER}")

Sleep(1000)

Wend

FileClose($file)

--W

[Jos]

Looks fine .... (you do have 2 times $Rec[5] in there )...

If you have a lot more fields in a record you could do the send stuff with a for .. next loop but with this number of fields it isn't an issue.

Edited July 2, 2004 by JdeB

[wjessee]

Yes, the $Rec[5] is correctly specified twice.

These are all the fields there will be. All I have to do is add more {TAB}s.

Thank you all very much.

--W

[Arctor]

Hi wjessee,

just one question after looking at your final script:

Am I see it right? Did you do that whole script just to change a "^" to a TAB?

arctor

[trids]

  wjessee said:

Is this the best way to do it?

.. depends If you especially want to automate notepad, then you could do it that way.

But if you want to just replace the commas with tabs, you could simplify it like this (untested):

;Get the entire file into a string
    $sFile = "list.txt"
    $sFileText = FileRead($sFile , FileGetSize($sFile))
;Replace "," with @TAB
    $sFileText = StringReplace($sFileText, ",", @TAB)
;Rewrite the file
    FileWrite($sFile, $sFileText)

.. if you want to do other things like duplicate the fifth field, you could manipulate $sFileText with StringSplit as explained above .. but you don't need to use notepad to write your file

HTH