Sign in to follow this  
Followers 0
radeonorama

Filling in the blanks

6 posts in this topic

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.

Share this post


Link to post
Share on other sites



#2 ·  Posted (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 by Manjish

[font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com

Share this post


Link to post
Share on other sites

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.

Local $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)

Share this post


Link to post
Share on other sites

Try this.

It duplicates the csv file adding the missing entries.

Local $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!!!

Share this post


Link to post
Share on other sites

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.

Local $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)

Share this post


Link to post
Share on other sites

You do make it challenging.

Local $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!

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