Help with opening a *.csv file

[bb01]

ok, i've got a script running, but need it to open *.csv

The reason is, the program creates a date.csv & as i have the end of the script move the file *.csv to a backups folder.

There is always only the 1 date.csv (date = todays date)

I've looked all over the place & cannot find anything to do with "*"

so once i know how to open a "*.csv" i then can use the same style for it moving the "*.csv" to the backups folder

Any help would be greatly appreciated

BB01

[Melba23]

bb01,

If the *.csv file is the only such file in your folder, you can move it to your backup folder like this:

FileMove("My_Folder_Path\*.csv", "My_BackUp_Folder_Path\*.csv")

It keeps the same name and tthere is no need to open it first.

M23

[bb01]

ok but got another problem, i already have the following & the filepath0 is opened in excel to do some copying of data.

Global $sFilePath0 = @ScriptDir & "date.csv"
Global $sFilePath1 = @ScriptDir & "backups1\date.csv"

But putting *.csv there causes it to error out. is there a way to add the *.csv to the global & not having it error out, stating cannot find the file?

Edited September 8, 2010 by bb01

[AdmiralAlkex]

Excel wants a file, not wildcards. Get the actual filename from FileFindFirstFile/FileFindNextFile first and give that to Excel.

[Varian]

If you "add" all the files in the directory with FindFirstFile/FindNextFile can't you send that to Excel?

Global $sFilePath0 = '"' & @ScriptDir & '\1.csv" "' & @ScriptDir & '\2.csv" "' & @ScriptDir & '\3.csv" "' & @ScriptDir & '\date.csv"'

You could also easily add all the files with _FileListToArray Edited September 8, 2010 by Varian

[bb01]

ok this is confusing, as can see how i want it to work, but cant get it to work.

Global $sFilePath = @ScriptDir & "$sFoundFile.csv"

Excel reads $sFilePath

Then when finished, it moves the *.csv to \backups

so theres always only 1 .csv in the main folder

the problem i'm having is how to get the $sFilePath from the following.. i can see how it would be, but always getting errors when trying it.. :-

;~ ; New Script to find *.csv (For the Date eg. 01-08-2010.csv)

; Shows the filenames of all files in the current directory
$search = FileFindFirstFile(@ScriptDir & "\*.csv")  

; Check if the search was successful
If $search = -1 Then
    MsgBox(0, "Error", "No Phonetree files found")
    Exit
EndIf

While 1
    $sFoundFile = FileFindNextFile($search) 
    If @error Then ExitLoop
    
WEnd

; Close the search handle
FileClose($search)

;End Script to find *.csv

[MHz]

You are almost there. Just a little change to the syntax and some comments to get better understanding of the task.

Perhaps this may clear the issue:

;~ ; New Script to find *.csv (For the Date eg. 01-08-2010.csv)

; Opens a search handle to find csv files in the current directory
$hSearch = FileFindFirstFile(@ScriptDir & "\*.csv")

; Check if the search handle is valid
If $hSearch = -1 Then
    MsgBox(0, "Error", "No Phonetree csv files found")
    Exit
EndIf

While 1
    ; Find the next file
    ; i.e. returns a string containing the value of 01-08-2010.csv
    $sFoundFile = FileFindNextFile($hSearch)
    If @error Then ExitLoop
    ; Create a full path using @ScriptDir and the found file
    $sFilePath = @ScriptDir & '\' & $sFoundFile
    ; The found file and the filepath are now available for use
    ;
    ; Show result for demonstration
    MsgBox(0x40000, @ScriptName, _
        '$sFoundFile = ' & $sFoundFile & @CRLF & _
        '$sFilePath = ' & $sFilePath)
    ; Exit the loop as for demonstration only
    ExitLoop
WEnd

; Close the search handle
FileClose($hSearch)

;End Script to find *.csv

Now with 01-08-2010.csv in the script directory, I get the output of

$sFoundFile = 01-08-2010.csv
$sFilePath = C:\Temp\01-08-2010.csv

That should give you something to work with

[bb01]

Thank you MHZ

I'm using this global now..

Global $sFilePath = @ScriptDir & '\' & $sFoundFile

& seems to be working fine..

i do have 1 q now though, the search only works for *.csv, how can i get it to pull just the name, not the .csv, so i can then use that part of the name to create the zip..

I got to admit, this project i'm working on is working out to be the hardest i've done, as theres loops, search functions, etc.. & even now the loops is only working using multiple copys of its self...

these people that come up with the Idea that because i know a few lines of scripting & that i can build & fix the computers etc here, that i can make life easier by creating this script for them... lol

[Juvigy]

If you have the filename like "test.csv" or "c:\test\test.csv" you can just :

$result = StringTrimRight("c:\test\test.csv", 3)
MsgBox(0, "String without rightmost 3 characters is:", $result)

[GEOSoft]

$sFileName = StringRegExpReplace($sFoundFile, "(.+)\.csv", "$1")

[bb01]

ahhh thanks guys.. that should help out..

[bb01]

ok guys, got the search & the zip section all working.. thank you..

I ended up using the string replace, see below.. & now that all works..

Now if only i can get the loop worked out... those suckers are hard to work out...

expandcollapse popup

; Opens a search handle to find csv files in the current directory
$hSearch = FileFindFirstFile(@ScriptDir & "\*.csv")

; Check if the search handle is valid
If $hSearch = -1 Then
    MsgBox(0, "Error", "No Phonetree csv files found")
    Exit
EndIf

While 1
    ; Find the next file
    ; i.e. returns a string containing the value of 01-08-2010.csv
    $sFoundFile = FileFindNextFile($hSearch)
    If @error Then ExitLoop
    ; Create a full path using @ScriptDir and the found file
    $sFilePath = @ScriptDir & '\' & $sFoundFile

    ; The found file and the filepath are now available for use
    ;
    ; The next line shows what the found file is without the csv
    $replacer = StringReplace ( $sFoundFile, "csv", "zip")

    ; Show result for demonstration - Use this only whilst working on the script
    MsgBox(4096, "This is 4 info 4 testing", _
        'The File Were Using Is = ' & $sFoundFile & @CRLF & _
        'At This Path = ' & $sFilePath)
    ; Exit the loop as for demonstration only
    ExitLoop
WEnd

; Close the search handle
FileClose($hSearch)

;~ ; ************ END OF THE SEARCH SECTION, NOW YOU CAN EDIT BELOW THIS LINE ********************

;End Script to find *.csv
Global $sFilePath = @ScriptDir & '\' & $sFoundFile

;%%%%%% GLOBAL VARS %%%%%%%
;Global $sFilePath = @ScriptDir & "\phonetree.csv"
Global $sFileName=@ScriptDir & '\Backups\' & $replacer
;Global $sFileName=@ScriptDir & "\Backups\phonetree.zip"
Global $iOverwrite=1
Global $sZipFile=@ScriptDir & '\Backups\' & $replacer
Global $sFileAddName= @ScriptDir & '\' & $sFoundFile
Global $sDestDir=""
Global $iFlag=76

[bb01]

hi got another q..

I've got a outside script that needs to be run..

But how do i merge all the outside scripts into just 1 script..

Eg..

expandcollapse popup

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <ProgressConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <GuiConstantsEx.au3>
#include <Date.au3>
#include <WindowsConstants.au3>
#include <_Zip.au3>

; Opens a search handle to find csv files in the current directory
$hSearch = FileFindFirstFile(@ScriptDir & "\*.csv")

; Check if the search handle is valid
If $hSearch = -1 Then
    MsgBox(0, "Error", "No Phonetree csv files found")
    Exit
EndIf

While 1
    ; Find the next file
    ; i.e. returns a string containing the value of 01-08-2010.csv
    $sFoundFile = FileFindNextFile($hSearch)
    If @error Then ExitLoop
    ; Create a full path using @ScriptDir and the found file
    $sFilePath = @ScriptDir & '\' & $sFoundFile

    ; The found file and the filepath are now available for use
    ;
    ; The next line shows what the found file is without the csv
;    $result = StringTrimRight(@ScriptDir & '\' & $sFoundFile, 3)
    $replacer = StringReplace ( $sFoundFile, "csv", "zip")

        ; Exit the loop as for demonstration only
    ExitLoop
WEnd

; Close the search handle
FileClose($hSearch)

;~ ; ************ END OF THE SEARCH SECTION, NOW YOU CAN EDIT BELOW THIS LINE ********************


$find = "9 Not Confirmed"
$replace = "Unable to deliver message."

Global $filename = @ScriptDir & '\' & $sFoundFile

$msg = $find

FileWrite($filename, $msg )
    
;msgbox(0,"BEFORE",$msg)

$retval = _ReplaceStringInFile($filename,$find,$replace)
if $retval = -1 then
    ;msgbox(0, "ERROR", "The pattern could not be replaced in file: " & $filename & " Error: " & @error)
    exit
else
;    msgbox(0, "INFO", "Found " & $retval & " occurances of the pattern: " & $find & " in the file: " & $filename)
endif

$msg = FileRead($filename, 1000)
;msgbox(0,"AFTER",$msg)
exit

and also this script:-

expandcollapse popup

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <ProgressConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <GuiConstantsEx.au3>
#include <Date.au3>
#include <WindowsConstants.au3>
#include <_Zip.au3>

; Opens a search handle to find csv files in the current directory
$hSearch = FileFindFirstFile(@ScriptDir & "\*.csv")

; Check if the search handle is valid
If $hSearch = -1 Then
    MsgBox(0, "Error", "No Phonetree csv files found")
    Exit
EndIf

While 1
    ; Find the next file
    ; i.e. returns a string containing the value of 01-08-2010.csv
    $sFoundFile = FileFindNextFile($hSearch)
    If @error Then ExitLoop
    ; Create a full path using @ScriptDir and the found file
    $sFilePath = @ScriptDir & '\' & $sFoundFile

    ; The found file and the filepath are now available for use
    ;
    ; The next line shows what the found file is without the csv
;    $result = StringTrimRight(@ScriptDir & '\' & $sFoundFile, 3)
    $replacer = StringReplace ( $sFoundFile, "csv", "zip")

        ; Exit the loop as for demonstration only
    ExitLoop
WEnd

; Close the search handle
FileClose($hSearch)

;~ ; ************ END OF THE SEARCH SECTION, NOW YOU CAN EDIT BELOW THIS LINE ********************


$find = "Ans by +Machine"
$replace = "Message was delivered."

Global $filename = @ScriptDir & '\' & $sFoundFile

$msg = $find

FileWrite($filename, $msg )
    
;msgbox(0,"BEFORE",$msg)

$retval = _ReplaceStringInFile($filename,$find,$replace)
if $retval = -1 then
    ;msgbox(0, "ERROR", "The pattern could not be replaced in file: " & $filename & " Error: " & @error)
    exit
else
;    msgbox(0, "INFO", "Found " & $retval & " occurances of the pattern: " & $find & " in the file: " & $filename)
endif

$msg = FileRead($filename, 1000)
;msgbox(0,"AFTER",$msg)
exit

into just 1 script.. Have multiple search's that it needs to do, so got 21 scripts, but they are all very similar, but trying to work out how to merge into just 1 script..

[bb01]

ok, worked it out.. see below how i fixed it...

However at the bottom of the excel sheet theres this line.. How do i fix that??

Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Unable to deliver message.Message was delivered.Message was delivered.Message was delivered.Unable to deliver message.Message was delivered.Unable to deliver message.Unable to deliver message.

Script:-

expandcollapse popup

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <ProgressConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <GuiConstantsEx.au3>
#include <Date.au3>
#include <WindowsConstants.au3>
#include <_Zip.au3>

; Opens a search handle to find csv files in the current directory
$hSearch = FileFindFirstFile(@ScriptDir & "\*.csv")

; Check if the search handle is valid
If $hSearch = -1 Then
    MsgBox(0, "Error", "No Phonetree csv files found")
    Exit
EndIf

While 1
    ; Find the next file
    ; i.e. returns a string containing the value of 01-08-2010.csv
    $sFoundFile = FileFindNextFile($hSearch)
    If @error Then ExitLoop
    ; Create a full path using @ScriptDir and the found file
    $sFilePath = @ScriptDir & '\' & $sFoundFile

    ; The found file and the filepath are now available for use

    ExitLoop
WEnd

; Close the search handle
FileClose($hSearch)

;~ ; ************ END OF THE SEARCH SECTION, NOW YOU CAN EDIT BELOW THIS LINE ********************


Global $filename = @ScriptDir & '\' & $sFoundFile

$find1 = "Telco Message"
$replace1 = "Unable to deliver message."

$find2 = "Not Selected"
$replace2 = "Unable to deliver message."

$find3 = "Not Called Yet"
$replace3 = "Unable to deliver message."

$find4 = "Not Accepted"
$replace4 = "Unable to deliver message."

$find5 = "No Connect"
$replace5 = "Unable to deliver message."

$find7 = "Max No Answers"
$replace7 = "Unable to deliver message."

$find8 = "Hung Up Early"
$replace8 = "Unable to deliver message."

$find9 = "Learned Machine"
$replace9 = "Unable to deliver message."

$find10 = "Fax or Modem"
$replace10 = "Unable to deliver message."

$find11 = "Call Failed!"
$replace11 = "Unable to deliver message."

$find12 = "Busy After Voice"
$replace12 = "Unable to deliver message."

$find14 = "Blocked Number"
$replace14 = "Unable to deliver message."

$find15 = "Ans by Person"
$replace15 = "Message was delivered."

$find16 = "Ans by Pager"
$replace16 = "Message was delivered."

$find17 = "Ans by Machine"
$replace17 = "Message was delivered."

$find18 = "Telco Message"
$replace18 = "Unable to deliver message."

$find19 = "Ans by +Machine"
$replace19 = "Message was delivered."

$find20 = "9 Not Confirmed"
$replace20 = "Unable to deliver message."

$find6 = "OGM Too Long"
$replace6 = "Unable to deliver message."

$find21 = "Busy"
$replace21 = "Unable to deliver message."

$find13 = "No Answer"
$replace13 = "Unable to deliver message."



$msg1 = $find1

FileWrite($filename, $msg1 )
    


$retval1 = _ReplaceStringInFile($filename,$find1,$replace1)

if $retval1 = -1 then
;    msgbox(0, "ERROR", "The pattern could not be replaced in file: " & $filename & " Error: " & @error)
    exit
else

endif

; ------------------ next replace ----------------------------
$msg2 = $find2

FileWrite($filename, $msg2 )
    


$retval2 = _ReplaceStringInFile($filename,$find2,$replace2)

if $retval2 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg3 = $find3

FileWrite($filename, $msg3 )
    


$retval3 = _ReplaceStringInFile($filename,$find3,$replace3)

if $retval3 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg4 = $find4

FileWrite($filename, $msg4 )
    


$retval4 = _ReplaceStringInFile($filename,$find4,$replace4)

if $retval4 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg5 = $find5

FileWrite($filename, $msg5 )
    


$retval5 = _ReplaceStringInFile($filename,$find5,$replace5)

if $retval5 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg6 = $find6

FileWrite($filename, $msg6 )
    


$retval6 = _ReplaceStringInFile($filename,$find6,$replace6)

if $retval6 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg7 = $find7

FileWrite($filename, $msg7 )
    


$retval7 = _ReplaceStringInFile($filename,$find7,$replace7)

if $retval7 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg8 = $find8

FileWrite($filename, $msg8 )
    


$retval8 = _ReplaceStringInFile($filename,$find8,$replace8)

if $retval8 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg9 = $find9

FileWrite($filename, $msg9 )
    


$retval9 = _ReplaceStringInFile($filename,$find9,$replace9)

if $retval9 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg10 = $find10

FileWrite($filename, $msg10 )
    


$retval10 = _ReplaceStringInFile($filename,$find10,$replace10)

if $retval10 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg11 = $find11

FileWrite($filename, $msg11 )
    


$retval11 = _ReplaceStringInFile($filename,$find11,$replace11)

if $retval11 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg12 = $find12

FileWrite($filename, $msg12 )
    


$retval12 = _ReplaceStringInFile($filename,$find12,$replace12)

if $retval12 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg13 = $find13

FileWrite($filename, $msg13 )
    


$retval13 = _ReplaceStringInFile($filename,$find13,$replace13)

if $retval13 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg14 = $find14

FileWrite($filename, $msg14 )
    


$retval14 = _ReplaceStringInFile($filename,$find14,$replace14)

if $retval14 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg15 = $find15

FileWrite($filename, $msg15 )
    


$retval15 = _ReplaceStringInFile($filename,$find15,$replace15)

if $retval15 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg16 = $find16

FileWrite($filename, $msg16 )
    


$retval16 = _ReplaceStringInFile($filename,$find16,$replace16)

if $retval16 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg17 = $find17

FileWrite($filename, $msg17 )
    


$retval17 = _ReplaceStringInFile($filename,$find17,$replace17)

if $retval17 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg18 = $find18

FileWrite($filename, $msg18 )
    


$retval18 = _ReplaceStringInFile($filename,$find18,$replace18)

if $retval18 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg19 = $find19

FileWrite($filename, $msg19 )
    


$retval19 = _ReplaceStringInFile($filename,$find19,$replace19)

if $retval19 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg20 = $find20

FileWrite($filename, $msg20 )
    


$retval20 = _ReplaceStringInFile($filename,$find20,$replace20)

if $retval20 = -1 then
    exit
else
endif

; ------------------ next replace ----------------------------
$msg21 = $find21

FileWrite($filename, $msg21 )
    


$retval21 = _ReplaceStringInFile($filename,$find21,$replace21)

if $retval21 = -1 then
    exit
else
endif

; ------------------ next replace ----------------------------



$msg = FileRead($filename, 1000)
;msgbox(0,"AFTER",$msg)
exit

[GEOSoft]

You have made that far more complicated than it needs to be. Read up on using arrays and For/Next loops.

Dim $aFind[21] = ["Telco Message", "Not Selected", "Not Called Yet", "Not Accepted", "No Connect", "OGM Too Long", "Max No Answers", "Hung Up Early", _
        "Learned Machine", "Fax or Modem", "Call Failed!", "Busy After Voice", "No Answer", "Blocked Number", "Ans by Person", "Ans by Pager", "Ans by Machine", _
        "Ans by +Machine", "Telco Message", "9 Not Confirmed", "Busy"]

For $i = 0 To UBound($aFind) -1
    $sFind = $aFind[$i]
    Switch $i
        Case 14 To 17
            $sReplace = "Message was delivered."
        Case Else
            $sReplace = "Unable to deliver message."
    EndSwitch
;;  Now you handle your search and replace code here.
Next

Warning, I didn't test to verify the results, that part is up to you. If you have a problem, post back.

[bb01]

ok guys got a q..

I have the script set up for hotkeys for terminating, & when it terminates it creates a backup of the .csv(Excel Sheet) into a zip, all this works fine.. however, how do i get it to terminate on a blank line in the .csv

For example, i have 50 lines in the .csv & want it to run the terminate when it hits a blank line, so in this case line 51..

Other times it might be line 19 or others..

I'm using the hotkeyset & thats working..

HotKeySet("{ESC}", "Terminate")

Ok been looking around to see if i can solve this, & havent seen anything to do this..

But was wondering would i be able to use an array or something that can check the 2nd column & when it reaches a blank cell in the 2nd column, it then starts the terminate process..

Thanks in advance

Graham

Edited September 20, 2010 by bb01

[bb01]

ok, a way to get round this is to have the script continue as normal, however if it has to wait more than 5mins on any 1 screen, then it exits..

Now how do i put that into Autoit script, Any Ideas?

Edit:-

if i got this right, the following will wait 30 seconds, And the window does not become active, it will shut down the script?

;WinWaitNotActive("N-FOCUS - Detail Master Case - \\Remote, 128-bit SSL/TLS.")
;WinWaitNotActive("N-FOCUS - Detail Master Case - \\Remote, 128-bit SSL/TLS.", "", 30)

Edited September 21, 2010 by bb01

[bb01]

hi dont know if anyone can help with this..

But i have a replacer script that replaces txt in an excel sheet

Like this:-

$find1 = "Telco Message"
$replace1 = "Unable to deliver message."

However the excel sheet shows

"Unable to deliver message."

How can i remove the " "

As its meant to be part of a full line of txt, the rest is typed in threw the script..

For example i really need it to be:-

$find19 = ""Ans by +Machine""
$replace19 = "Message was delivered."

But then it cant run, and errors out.

Edited September 28, 2010 by bb01

[GEOSoft]

Show us how you are trying to put it into the full string. In fact show the full string that you expect to end up with.

[bb01]

well its part of a replacer script, & what it is to replace is..

"Ans by +Machine"

with

Message was delivered.

The main message does have Speech Quotes, however the end message we dont want having Speech Quotes.. & thats the problem how to replace or remove them?