radeonorama Posted February 17, 2009 Share Posted February 17, 2009 Hello,I have been looking into a way to enable me to even out a CSV file I have been given.Basically, the file contains 1st and 2nd applicants as separate rows; however, sometimes there is only a single applicant so the file will look like this1,0,734,3.891,0,734,3.893,0,736,4.593,0,736,4.595,0,738,5.55,0,738,5.56,0,739,4.497,0,740,4.348,0,741,3.79what i need to do is fill in the missing blanks so it would look something like this...1,0,734,3.891,0,734,3.892,,,2,,,3,0,736,4.593,0,736,4.595,0,738,5.55,0,738,5.56,0,739,4.496,,,7,0,740,4.347,,,8,0,741,3.79I have created a script where by i read each line and do a string split into an array so I can read the first column and do a compare but I am really struggling to then add the blanks in between. Its a little over my head but I have 17000 to go through and i could really do with a script to help me before I revert to manually doing it and my eyes falling out!Any help would be appreciated. http://www.autoitscript.com/forum/style_im...icons/icon9.gifThanks in advance. Link to comment Share on other sites More sharing options...
Manjish Posted February 17, 2009 Share Posted February 17, 2009 (edited) Here's an idea.. 1) Read the csv file as a notepad.. 2) Start a for loop from $i=1 to the last applicant no. 3) read file line.. string split with "," as seperator.. store it in $array 4) do a filereadline twice in the loop.. see if the 1st value.. i.e array[1] is equal to $i.. if not add a line $i,0,0,0 Edited February 17, 2009 by Manjish [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
Malkey Posted February 17, 2009 Share Posted February 17, 2009 Hello, I have been looking into a way to enable me to even out a CSV file I have been given. Basically, the file contains 1st and 2nd applicants as separate rows; however, sometimes there is only a single applicant so the file will look like this 1,0,734,3.89 1,0,734,3.89 3,0,736,4.59 3,0,736,4.59 5,0,738,5.5 5,0,738,5.5 6,0,739,4.49 7,0,740,4.34 8,0,741,3.79 what i need to do is fill in the missing blanks so it would look something like this... 1,0,734,3.89 1,0,734,3.89 2,,, 2,,, 3,0,736,4.59 3,0,736,4.59 5,0,738,5.5 5,0,738,5.5 6,0,739,4.49 6,,, 7,0,740,4.34 7,,, 8,0,741,3.79 I have created a script where by i read each line and do a string split into an array so I can read the first column and do a compare but I am really struggling to then add the blanks in between. Its a little over my head but I have 17000 to go through and i could really do with a script to help me before I revert to manually doing it and my eyes falling out! Any help would be appreciated. http://www.autoitscript.com/forum/style_im...icons/icon9.gif Thanks in advance.Try this. It duplicates the csv file adding the missing entries. expandcollapse popupLocal $iOldNum, $LineNum = 0, $itemp $file = FileOpen("C:\FullPath\FileName.csv", 0) If FileExists("new,csv") Then FileDelete("New,csv") ;Otherwise will append same data on 2nd run. $file2 = FileOpen("new,csv", 1) ; Will be created in this script directory. ; Check if file opened for reading OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf ; Read in lines of text until the EOF is reached While 1 $LineNum += 1 $line = FileReadLine($file, $LineNum) If @error = -1 Then ExitLoop $aLin = StringSplit($line, ",") If IsArray($aLin) Then $num = $aLin[1] Else $num = $iOldNum EndIf ;ConsoleWrite($num & @CRLF) Select Case $LineNum = 1 FileWriteLine($file2, $line) $itemp = $num Case $iOldNum = $num Or $iOldNum + 1 = $num FileWriteLine($file2, $line) $itemp = $num Case $iOldNum + 1 < $num FileWriteLine($file2, $iOldNum + 1 & ",,,") $itemp = $iOldNum + 1 $LineNum -= 1 Case GUIGetMsg() = -3 ExitLoop EndSelect $iOldNum = $itemp Sleep(10) WEnd FileClose($file) FileClose($file2) Link to comment Share on other sites More sharing options...
radeonorama Posted February 19, 2009 Author Share Posted February 19, 2009 Try this. It duplicates the csv file adding the missing entries. expandcollapse popupLocal $iOldNum, $LineNum = 0, $itemp $file = FileOpen("C:\FullPath\FileName.csv", 0) If FileExists("new,csv") Then FileDelete("New,csv") ;Otherwise will append same data on 2nd run. $file2 = FileOpen("new,csv", 1) ; Will be created in this script directory. ; Check if file opened for reading OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf ; Read in lines of text until the EOF is reached While 1 $LineNum += 1 $line = FileReadLine($file, $LineNum) If @error = -1 Then ExitLoop $aLin = StringSplit($line, ",") If IsArray($aLin) Then $num = $aLin[1] Else $num = $iOldNum EndIf ;ConsoleWrite($num & @CRLF) Select Case $LineNum = 1 FileWriteLine($file2, $line) $itemp = $num Case $iOldNum = $num Or $iOldNum + 1 = $num FileWriteLine($file2, $line) $itemp = $num Case $iOldNum + 1 < $num FileWriteLine($file2, $iOldNum + 1 & ",,,") $itemp = $iOldNum + 1 $LineNum -= 1 Case GUIGetMsg() = -3 ExitLoop EndSelect $iOldNum = $itemp Sleep(10) WEnd FileClose($file) FileClose($file2) Ah wow thats great! My only problem is that i need it to create double blanks when it creates them. and also, where there is only a single line instead of a double, it needs to create a second blank line with the same number. just looking at it and trying to work it out now. Thanks for all fo your help!!! Link to comment Share on other sites More sharing options...
Malkey Posted February 19, 2009 Share Posted February 19, 2009 Ah wow thats great! My only problem is that i need it to create double blanks when it creates them. and also, where there is only a single line instead of a double, it needs to create a second blank line with the same number. just looking at it and trying to work it out now. Thanks for all fo your help!!!You do make it challenging. expandcollapse popupLocal $iOldNum, $LineNum = 0, $itemp $file = FileOpen("C:\FullPath\FileName.csv", 0) If FileExists("new,csv") Then FileDelete("New,csv");Otherwise will append same data on 2nd run. $file2 = FileOpen("new,csv", 1); Will be created in this script directory. ; Check if file opened for reading OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf ; Read in lines of text until the EOF is reached While 1 $LineNum += 1 $line = FileReadLine($file, $LineNum) Local $err = @error $aLin = StringSplit($line, ",") If IsArray($aLin) Then $num = $aLin[1] Else $num = $iOldNum EndIf ;ConsoleWrite($num & @CRLF) Select Case $LineNum = 1 FileWriteLine($file2, $line) $itemp = $num Local $NumeNum = 1 Case $iOldNum = $num And $NumeNum = 1 FileWriteLine($file2, $line) $itemp = $num $NumeNum = 2 Case $iOldNum + 1 = $num And $NumeNum = 2 FileWriteLine($file2, $line) $itemp = $num $NumeNum = 1 Case $iOldNum + 1 And $NumeNum = 1 $NumeNum = 2 $LineNum -= 1 FileWriteLine($file2, FileReadLine($file, $LineNum)) $itemp = $num - 1 Case $iOldNum + 1 < $num FileWriteLine($file2, $iOldNum + 1 & ",,,") FileWriteLine($file2, $iOldNum + 1 & ",,,") $itemp = $iOldNum + 1 $LineNum -= 1 Case GUIGetMsg() = -3 ExitLoop EndSelect $iOldNum = $itemp If $err = -1 Then ExitLoop Sleep(10) WEnd FileClose($file) FileClose($file2) Link to comment Share on other sites More sharing options...
radeonorama Posted February 19, 2009 Author Share Posted February 19, 2009 You do make it challenging. expandcollapse popupLocal $iOldNum, $LineNum = 0, $itemp $file = FileOpen("C:\FullPath\FileName.csv", 0) If FileExists("new,csv") Then FileDelete("New,csv");Otherwise will append same data on 2nd run. $file2 = FileOpen("new,csv", 1); Will be created in this script directory. ; Check if file opened for reading OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf ; Read in lines of text until the EOF is reached While 1 $LineNum += 1 $line = FileReadLine($file, $LineNum) Local $err = @error $aLin = StringSplit($line, ",") If IsArray($aLin) Then $num = $aLin[1] Else $num = $iOldNum EndIf ;ConsoleWrite($num & @CRLF) Select Case $LineNum = 1 FileWriteLine($file2, $line) $itemp = $num Local $NumeNum = 1 Case $iOldNum = $num And $NumeNum = 1 FileWriteLine($file2, $line) $itemp = $num $NumeNum = 2 Case $iOldNum + 1 = $num And $NumeNum = 2 FileWriteLine($file2, $line) $itemp = $num $NumeNum = 1 Case $iOldNum + 1 And $NumeNum = 1 $NumeNum = 2 $LineNum -= 1 FileWriteLine($file2, FileReadLine($file, $LineNum)) $itemp = $num - 1 Case $iOldNum + 1 < $num FileWriteLine($file2, $iOldNum + 1 & ",,,") FileWriteLine($file2, $iOldNum + 1 & ",,,") $itemp = $iOldNum + 1 $LineNum -= 1 Case GUIGetMsg() = -3 ExitLoop EndSelect $iOldNum = $itemp If $err = -1 Then ExitLoop Sleep(10) WEnd FileClose($file) FileClose($file2) You are a complete and utter legend!!!!! B**dy fast too!!! Thanks ever so much for your help! 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