Sign in to follow this  
Followers 0
wjessee

Help with parsing some CSV files and recreating a new file

12 posts in this topic

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

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by flyingboz

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Next

FileClose($tempFile)

EndIf

---end---

Share this post


Link to post
Share on other sites

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)

Next

FileClose($tempFile)

EndIf

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by MHz

Share this post


Link to post
Share on other sites

$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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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)

Next

FileClose($tempFile)

Edited by wjessee

Share this post


Link to post
Share on other sites

hmm,

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

-W

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0