Help w/Opening & Editing Multiple Files

[AITNewbie]

All,

I'm sure this is extremely basic but it's alluding me. I'm simply trying to open a bunch of .CSV files and remove the header row - the first line. I can't hard code the file names in because the naming and amount of files is variable. The constant is the directory where they are stored.

Here's what I have but it's not working. Any help would be most appreciated! (Also, my apologies as my code below is not formatted - not sure how to do that in this tool)

#include <File.au3>

$search = FileFindFirstFile("F:\CSV Files\filemergetest\*.csv")

; Check if the search was successful

If $search = -1 Then

MsgBox(0, "Error", "No files/directories matched the search pattern")

Exit

EndIf

While 1

$sfile = FileFindNextFile($search)

If @error Then ExitLoop

_FileWriteToLine($sfile, 1, "", 1)

WEnd

; Close the search handle

FileClose($search)

[ngskicker]

not testet but try this

#Include <File.au3>
#Include <Array.au3>

$FileList=_FileListToArray("F:\CSV Files\filemergetest", "*.csv" , 1)
If @Error=4 Then
    MsgBox (0,"","No Files Found.")
    Exit
EndIf
_ArrayDisplay($FileList)
For $i = 1 To $FileList[0]
    $sfile = FileOpen("F:\CSV Files\filemergetest"&$FileList[$i], 1)
    ;do whatever you like to file here
    FileClose($FileList[1])
Next

Edited August 27, 2010 by ngskicker

[AITNewbie]

Hi,

Thanks for the suggestion. I assumed that you could do it with an array but wasn't sure how to code it. However, after I add the line of code where I try to delete the header row, nothing happens. It displays the file names in the array message box but doesn't delete the header row.

Any additional help would be appreciated. See the edited script below:

#Include <File.au3>

#Include <Array.au3>

$FileList=_FileListToArray("K:\CSV Files\filemergetest", "*.csv" , 1)

If @Error=4 Then

MsgBox (0,"","No Files Found.")

Exit

EndIf

ArrayDisplay($FileList)

For $i = 1 To $FileList[0]

$sfile = FileOpen("K:\CSV Files\filemergetest"&$FileList[$i], 1)

;do whatever you like to file here

_FileWriteToLine ($sFile, 1, "", 1)

FileClose($FileList[1])

Next

[Varian]

2 issues:

(1) You need a trailing "\" where you defined $sFile..The path would come up as something like "K:\CSV Files\filemergetestSAMPLE.csv" instead of "K:\CSV Files\filemergetest\SAMPLE.csv"

(2) If you are using _FileWriteToLine, you do not need to use "FileOpen", and particularly not with the 1 option (append to end of file)..replace with this:

For $i = 1 To $FileList[0]
    $Sfile = "K:\CSV Files\filemergetest\" & $FileList[$i]
    _FileWriteToLine($Sfile, 1, "", 1)
    If @error Then
        Switch @error
            Case 1
                Local $Reason = 'File has less lines than $iLine'
            Case 2
                Local $Reason = 'File does not exist'
            Case 3
                Local $Reason = 'Error when opening file'
            Case 4
                Local $Reason = '$iLine is invalid'
            Case 5
                Local $Reason = '$fOverWrite is invalid'
            Case 6
                Local $Reason = '$sText is invalid'
        EndSwitch
        MsgBox(32, 'Error: ' & $Reason, 'File is: ' & $Sfile)
    EndIf
Next

Edited August 30, 2010 by Varian

[AITNewbie]

Yea!!!...Sort of.

Thanks so much. The script now works to strip the header off of all all the .CSV files in the directory. The next step I was going to take was to merge/append the files together into one CSV. Not so fast...

I fatally realized that the files are unique to a batch and I can't just merge them together without wrecking my batch import. After the header is removed, I would need to determine which files belong together and then copy them into their respective sub-directory for processing. Each line of the CSV file contains a code (string) that defines the batch (e.g. 0T4,) I think I would need the script to search the body of the file for each unique string (I'm okay hardcoding this if necessary as there are not that many codes) and then copy all files that match into a unique directory.

I started toying with StringRegExp but didn't get very far.

As always, thanks in advance for your suggestions and help!

[Varian]

Here is a start you can use for a regular expression search (after you have deleted the first line):

$FileList = _FileListToArray("K:\CSV Files\filemergetest", "*.csv", 1)
If @error Then Exit MsgBox(32, 'Error', 'Error is: ' & @error)
Local $JoinedText
For $i = 1 To $FileList[0]
    Local $Text = FileRead("K:\CSV Files\filemergetest\" & $FileList[$i])
    If StringRegExp($Text, "YourPatternMatchingHere", 0) Then $JoinedText &= $Text
Next
$oFile = FileOpen("PathToYourJoinedFile", 2)
FileWrite($oFile, $JoinedText)
FileClose($oFile)

If you need help testing/creating your regular expression queries, I suggest you look into RegEx Buddy. If you need any further help, post your patterns here and I can help you. I use Arrays to simplify multiple pattern queries.

[Varian]

Are you saying that within each file, certain lines would need to be added to different files? e.g. SAMPLE.csv has 10 lines, and the first value in each line determines to which batch it should be included. Or, the entire file has a unique stamp and it can then be moved into it's respective subdirectory and the merged?