wjessee Posted February 2, 2006 Share Posted 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 ; ---------------------------------------------------------------------------- Link to comment Share on other sites More sharing options...
flyingboz Posted February 2, 2006 Share Posted February 2, 2006 (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 February 2, 2006 by flyingboz Reading the help file before you post... Not only will it make you look smarter, it will make you smarter. Link to comment Share on other sites More sharing options...
billmez Posted February 2, 2006 Share Posted 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 Link to comment Share on other sites More sharing options...
wjessee Posted February 2, 2006 Author Share Posted 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) Next FileClose($tempFile) EndIf ---end--- Link to comment Share on other sites More sharing options...
randallc Posted February 2, 2006 Share Posted February 2, 2006 endif after filewriteline..... ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
wjessee Posted February 2, 2006 Author Share Posted 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) Next FileClose($tempFile) EndIf Link to comment Share on other sites More sharing options...
MHz Posted February 2, 2006 Share Posted February 2, 2006 (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 February 2, 2006 by MHz Link to comment Share on other sites More sharing options...
randallc Posted February 2, 2006 Share Posted February 2, 2006 $saveAs = FileSaveDialog( "Save As", "$folder", "TXT (*.txt)", 3)OK$tempPath = $folder & "\" & "csvTEMP.txt"$tempPath = $saveAsYou don't need the folder here; you will need to parse out the filename if you want it to go to a different folderRandall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
billmez Posted February 2, 2006 Share Posted 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 Link to comment Share on other sites More sharing options...
wjessee Posted February 2, 2006 Author Share Posted February 2, 2006 (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 February 2, 2006 by wjessee Link to comment Share on other sites More sharing options...
randallc Posted February 2, 2006 Share Posted February 2, 2006 $tempPath = $saveAs ;& ".txt"$tempFile = FileOpen ($tempPath , 2)Try some message boxes t check your script; or write to consoleMsgBox(0,"","$fullPath="&$fullPath) ExitRandall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
wjessee Posted February 3, 2006 Author Share Posted February 3, 2006 hmm, I am just not spotting the problem yet. Must be my newbee hat. -W Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now