Help with parsing some CSV files and recreating a new file

wjessee · February 2, 2006

Hello,

I am in need of some help. I have several CSV files I would like to open with the FileOpenDialog option and in summary read the contents of them and place them in a new file with their contents appended and the columns in a different order than the original. Each of the 4 files I want to open are formated the same way with the same header.

Each of the 4 existing CSV files I want to open contain a header that I need to skip over and the new file I create will have it's own header row.

Here is the code I have so far.

Thanks for your help.

-Wayne

; ----------------------------------------------------------------------------

;

; AutoIt Version: 3.1.0

; Author: A.N.Other <myemail@nowhere.com>

;

; Script Function:

; Template AutoIt script.

;

; ----------------------------------------------------------------------------

; Script Start - Add your code below here

; Varibles

$message = "Hold down Ctrl or Shift to choose multiple files."

$db = FileOpenDialog($message, "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "CSV (*.csv)", 1 + 4 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

$db = StringReplace($db, "|", @CRLF)

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($db)

If @error = -1 Then ExitLoop

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

Wend

FileClose($db)

EndIf

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($db)

If @error = -1 Then ExitLoop

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

;fileWriteLine

;newheadercolumn1,newheadercolumn2,newheadercolumn3,newheadercolumn4,newheadercolumn5

;$Rec[1],$Rec[2],$Rec[3],$Rec[4],$Rec[5]

Wend

FileClose($db)

$save = FileSaveDialog( "Choose a name.", "C:\", "CSV (*.csv)", 3, "list.csv")

If @error Then

MsgBox(4096,"","Save cancelled.")

Else

MsgBox(4096,"","File" & $save & "saved")

EndIf

; ----------------------------------------------------------------------------

flyingboz · February 2, 2006

Are your headers the same number of lines in each file ?

If so, adjust you For loop to start _after_ the headers. i.e.

$FirstLine = 5
For $i = $FirstLine to ....

Or, use String... or StringRegEx... logic to case around your headers.

Or, concatenate all your files together in one fell swoop using the command processor, and then parse through the resulting file for the headerlines,

copy *.csv bigfile.csv;OR
copy file.a + file.b + file.c file.abc
for $i = 1 to $num_lines
  IF Not $header Then FileWriteLine(....)

Edited February 2, 2006 by flyingboz

billmez · February 2, 2006

Hello,

I am in need of some help. I have several CSV files I would like to open with the FileOpenDialog option and in summary read the contents of them and place them in a new file with their contents appended and the columns in a different order than the original. Each of the 4 files I want to open are formated the same way with the same header.

Each of the 4 existing CSV files I want to open contain a header that I need to skip over and the new file I create will have it's own header row.

Here is the code I have so far.
Thanks for your help.
-Wayne
; ----------------------------------------------------------------------------
;
; AutoIt Version: 3.1.0
; Author: A.N.Other <myemail@nowhere.com>
;
; Script Function:
; Template AutoIt script.
;
; ----------------------------------------------------------------------------

; Script Start - Add your code below here

; Varibles
$message = "Hold down Ctrl or Shift to choose multiple files."
$db = FileOpenDialog($message, "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "CSV (*.csv)", 1 + 4 )

If @error Then
MsgBox(4096,"","No File(s) chosen")
Else
$db = StringReplace($db, "|", @CRLF)

; Read in lines of text until the EOF is reached
While 1
$line = FileReadLine($db)
If @error = -1 Then ExitLoop
$Rec = StringSplit($line,",")
Wend
FileClose($db)

EndIf

; Read in lines of text until the EOF is reached
While 1
$line = FileReadLine($db)
If @error = -1 Then ExitLoop
$Rec = StringSplit($line,",")

;fileWriteLine
;newheadercolumn1,newheadercolumn2,newheadercolumn3,newheadercolumn4,newheadercolumn5
;$Rec[1],$Rec[2],$Rec[3],$Rec[4],$Rec[5]
Wend
FileClose($db)
$save = FileSaveDialog( "Choose a name.", "C:\", "CSV (*.csv)", 3, "list.csv")

If @error Then
MsgBox(4096,"","Save cancelled.")
Else
MsgBox(4096,"","File" & $save & "saved")
EndIf
; ----------------------------------------------------------------------------

This should give you the direction you are looking for. It is untested but should work. It will open each file in succession, strip the first line header out, and write the new header line with the new fields in a temp file in the same directory. You may need to adjust the quotes in the line that writes the values to the temp file depending on whether or not you want the line quoted in your output.

HTH

billmez

Dim $db
$db = FileOpenDialog($message, "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "CSV (*.csv)", 1 + 4 )

If @error Then
MsgBox(4096,"","No File(s) chosen")
Else

Dim $inPath, $folder, $fullPath, $tempPath, $lineCount, $inFile, $line, $Rec
$inPath = StringSplit ($db, "|")
$folder = $inPath[1]
$tempPath = $folder & "\" "csvTEMP.txt"
$tempFile = FileOpen ($tempPath , 1)

FileWriteLine($tempFile, "newheadercolumn1,newheadercolumn2,newheadercolumn3,newheadercolumn4,newheadercolumn5")

for $i=2 to UBound($inPath)-1
    $fullPath = $folder & "\" & $inPath[$i]
    $lineCount = 0
    $inFile = FileOpen ($fullPath,0)
    While 1
        $lineCount += 1
$line = FileReadLine($inFile)
If @error = -1 Then ExitLoop
    If $lineCount > 1 Then; this will skip your header rows
$Rec = StringSplit($line,",")
FileWriteLine($tempFile, """" & $Rec[1] & "," & $Rec[2] & "," & $Rec[3] & "," & $Rec[4] & "," & $Rec[5] & """")

WEnd
FileClose($inFile)
Next
FileClose($tempFile)
EndIf

wjessee · February 2, 2006

Thanks,

This looks like it will get me close. I have modified the code to better match the files. There is only 1 line in the header rows on any file. When I run this code it gives me an error that says "Error: "Wend" statement with no matching "While" statement". I see "while" is present, but I am not spotting the problem. I would also like to be able to choose the filename to save as.

Thanks,

-W

--start--

Dim $db

$db = FileOpenDialog("Select your files", "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "CSV (*.csv)", 1 + 4 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

Dim $inPath, $folder, $fullPath, $tempPath, $lineCount, $inFile, $line, $Rec

$inPath = StringSplit ($db, "|")

$folder = $inPath[1]

$tempPath = $folder & "\" "csvTEMP.txt"

$tempFile = FileOpen ($tempPath , 1)

FileWriteLine($tempFile, "email","firstname","lastname","company","homephone","workphone","address1","address2","city","state","zip/postcode","country","custom1","custom2","custom3","custom4","custom5","custom6","custom7","custom8","custom9","custom10")

for $i=2 to UBound($inPath)-1

$fullPath = $folder & "\" & $inPath[$i]

$lineCount = 0

$inFile = FileOpen ($fullPath,0)

while 1

$lineCount += 1

$line = FileReadLine($inFile)

If @error = -1 Then ExitLoop

If $lineCount > 1 Then; this will skip your header rows

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

FileWriteLine($tempFile, """" & $Rec[3] & "," & $Rec[1] & "," & $Rec[2] & "," & $Rec[20] & "," & $Rec[8] & "," & $Rec[21] & "," & $Rec[4] & "," & $Rec[22] & "," & $Rec[5] & "," & $Rec[6] & "," & $Rec[7] & "," & $Rec[16] & "," & $Rec[9] & "," & $Rec[10] & "," & $Rec[11] & "," & $Rec[12] & "," & $Rec[13] & "," & $Rec[14] & "," & $Rec[15] & "," & $Rec[17] & "," & $Rec[18] & "," & $Rec[19] & """")

WEnd

FileClose($inFile)

EndIf

---end---

randallc · February 2, 2006

endif after filewriteline.....

wjessee · February 2, 2006

thx randallc for the tip.

Here is what I have got so far. This writes a new header that is formatted the way I want it into a new file, but no additional data is appended from the files I selected in the open dialog box and it prompts for a location and filename to save to, but it won't write to my file if I replace csvTEMP.txt with $saveAs.

Dim $db, $saveAs

$db = FileOpenDialog("Select your files", "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "CSV (*.csv)", 1 + 4 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

$saveAs = FileSaveDialog( "Save As", "$folder", "TXT (*.txt)", 3)

Dim $inPath, $folder, $fullPath, $tempPath, $lineCount, $inFile, $line, $Rec

$inPath = StringSplit ($db, "|")

$folder = $inPath[1]

$tempPath = $folder & "\" & "csvTEMP.txt"

$tempFile = FileOpen ($tempPath , 1)

FileWriteLine($tempFile, chr(34) & "email" & chr(34) & "," & chr(34) & "firstname" & chr(34) & "," & chr(34) & "lastname" & chr(34) & "," & chr(34) & "company" & chr(34) & "," & chr(34) & "homephone" & chr(34) & "," & chr(34) & "workphone" & chr(34) & "," & chr(34) & "address1" & chr(34) & "," & chr(34) & "address2" & chr(34) & "," & chr(34) & "city" & chr(34) & "," & chr(34) & "state" & chr(34) & "," & chr(34) & "zip/postcode" & chr(34) & "," & chr(34) & "country" & chr(34) & "," & chr(34) & "custom1" & chr(34) & "," & chr(34) & "custom2" & chr(34) & "," & chr(34) & "custom3" & chr(34) & "," & chr(34) & "custom4" & chr(34) & "," & chr(34) & "custom5" & chr(34) & "," & chr(34) & "custom6" & chr(34) & "," & chr(34) & "custom7" & chr(34) & "," & chr(34) & "custom8" & chr(34) & "," & chr(34) & "custom9" & chr(34) & "," & chr(34) & "custom10" & chr(34))

for $i=2 to UBound($inPath)-1

$fullPath = $folder & "\" & $inPath[$i]

$lineCount = 0

$inFile = FileOpen ($fullPath,0)

while 1

$lineCount = 1

$line = FileReadLine($inFile)

If @error = -1 Then ExitLoop

If $lineCount > 1 Then; this will skip your header rows

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

FileWriteLine($tempFile, """" & $Rec[3] & "," & $Rec[1] & "," & $Rec[2] & "," & $Rec[20] & "," & $Rec[8] & "," & $Rec[21] & "," & $Rec[4] & "," & $Rec[22] & "," & $Rec[5] & "," & $Rec[6] & "," & $Rec[7] & "," & $Rec[16] & "," & $Rec[9] & "," & $Rec[10] & "," & $Rec[11] & "," & $Rec[12] & "," & $Rec[13] & "," & $Rec[14] & "," & $Rec[15] & "," & $Rec[17] & "," & $Rec[18] & "," & $Rec[19] & """")

EndIf

WEnd

FileClose($inFile)

EndIf

MHz · February 2, 2006

FileWriteLine($tempFile, chr(34) & "email" & chr(34) & "," & chr(34) & "firstname" & chr(34) & "," & chr(34) & "lastname" & chr(34) & "," & chr(34) & "company" & chr(34) & "," & chr(34) & "homephone" & chr(34) & "," & chr(34) & "workphone" & chr(34) & "," & chr(34) & "address1" & chr(34) & "," & chr(34) & "address2" & chr(34) & "," & chr(34) & "city" & chr(34) & "," & chr(34) & "state" & chr(34) & "," & chr(34) & "zip/postcode" & chr(34) & "," & chr(34) & "country" & chr(34) & "," & chr(34) & "custom1" & chr(34) & "," & chr(34) & "custom2" & chr(34) & "," & chr(34) & "custom3" & chr(34) & "," & chr(34) & "custom4" & chr(34) & "," & chr(34) & "custom5" & chr(34) & "," & chr(34) & "custom6" & chr(34) & "," & chr(34) & "custom7" & chr(34) & "," & chr(34) & "custom8" & chr(34) & "," & chr(34) & "custom9" & chr(34) & "," & chr(34) & "custom10" & chr(34))

You can use single quotes to wrap the double quotes as shown below. Also added some line continuations to fit better on width. You do not need to use Chr(34) as like in VBScript.

FileWriteLine($tempFile, '"email","firstname","lastname","company","homephone","workphone","address1",' & _
         '"address2","city","state","zip/postcode","country","custom1","custom2",' & _
         '"custom3","custom4","custom5","custom6","custom7","custom8","custom9","custom10"')

Edited February 2, 2006 by MHz

randallc · February 2, 2006

$saveAs = FileSaveDialog( "Save As", "$folder", "TXT (*.txt)", 3)

OK

$tempPath = $folder & "\" & "csvTEMP.txt"

$tempPath = $saveAs

You don't need the folder here; you will need to parse out the filename if you want it to go to a different folder

Randall

billmez · February 2, 2006

endif after filewriteline.....

wjessee,

:"> Sorry about missing this, but it was late and I wanted to get the code posted for you and didn't have a chance to run it.

The important concept here is in the loops. I hope this works the way you want it to.

billmez

wjessee · February 2, 2006

Thanks everyone for your help. We are so close. SaveAs works, but still no files are being appended.

-W

Dim $db, $saveAs

$db = FileOpenDialog("Select your files", "::{450D8FBA-AD25-11D0-98A8-0800361B1103}", "CSV (*.csv)", 1 + 4 )

If @error Then

MsgBox(4096,"","No File(s) chosen")

Else

$saveAs = FileSaveDialog( "Save As", "$folder", "TXT (*.txt)", 3)

Dim $inPath, $folder, $fullPath, $tempPath, $lineCount, $inFile, $line, $Rec

$inPath = StringSplit ($db, "|")

$folder = $inPath[1]

;$tempPath = $folder & "\" & "csvTEMP.txt"

$tempPath = $saveAs & ".txt"

$tempFile = FileOpen ($tempPath , 1)

FileWriteLine($tempFile, '"email","firstname","lastname","company","homephone","workphone","address1","address2","city","state","zip/postcode","country","custom1","custom2","custom3","custom4","custom5","custom6","custom7","custom8","custom9","custom10"')

EndIf

for $i=2 to UBound($inPath)-1

$fullPath = $folder & "\" & $inPath[$i]

$lineCount = 0

$inFile = FileOpen ($fullPath,0)

while 1

$lineCount = 1

$line = FileReadLine($inFile)

If @error = -1 Then ExitLoop

If $lineCount > 1 Then; this will skip your header rows

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

FileWriteLine($tempFile, """" & $Rec[3] & "," & $Rec[1] & "," & $Rec[2] & "," & $Rec[20] & "," & $Rec[8] & "," & $Rec[21] & "," & $Rec[4] & "," & $Rec[22] & "," & $Rec[5] & "," & $Rec[6] & "," & $Rec[7] & "," & $Rec[16] & "," & $Rec[9] & "," & $Rec[10] & "," & $Rec[11] & "," & $Rec[12] & "," & $Rec[13] & "," & $Rec[14] & "," & $Rec[15] & "," & $Rec[17] & "," & $Rec[18] & "," & $Rec[19] & """")

EndIf

WEnd

FileClose($inFile)

Edited February 2, 2006 by wjessee

randallc · February 2, 2006

$tempPath = $saveAs ;& ".txt"

$tempFile = FileOpen ($tempPath , 2)

Try some message boxes t check your script; or write to console

MsgBox(0,"","$fullPath="&$fullPath)
Exit

Randall

wjessee · February 3, 2006

hmm,

I am just not spotting the problem yet. Must be my newbee hat.

-W

Sign In

Help with parsing some CSV files and recreating a new file

Recommended Posts

wjessee

flyingboz

billmez

wjessee

randallc

wjessee

MHz

randallc

billmez

wjessee

randallc

wjessee

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta