Read file text to excil and bybass line(SOLVED)

[abdulrahmanok]

Welcome all,

Firstly that's my code:
 

#include <Excel.au3>
#include<file.au3>

    ProcessClose("Excel.exe")
            ;;;;;;;;;;;;;; Compare Between Calc And DaysArray And Add "None"
            $aArray = FileReadToArray(@scriptdir & "\Calc.txt")
            If @error Then
            Else
                $oExcel = ObjCreate("Excel.Application")
                $oExcel.Visible = 1
                $file2 = @scriptdir & "\calc.txt"
            Global   $TextFromCalc 
                If Not _FileReadToArray($file2, $TextFromCalc, 0) Then
                    MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the Calc : " & @error) ; An error occurred reading the current script file.
                EndIf

            
                Local $oWorkbook = _Excel_BookOpen($oExcel, @scriptdir & "\Excel.xlsx")
                Sleep(100)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $TextFromCalc+1, "C2")

            EndIf

"Calc.txt" contains:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

I want to make excel write in C2 raw  the "Calc" file but bypass lines  like this:

2
4
6
....

I will be here for any declaration

Edited August 28, 2017 by abdulrahmanok

[JLogan3o13]

Is the calc.txt always going to have sequential numbers? Rather than reading a file into one array, and then trying to extract all even/odd numbers, you could simply add only those that meet the criteria to the array in the first place:

#include <Array.au3>

Local $aArray[0]
    For $a = 1 To 10
        If Mod($a, 2) = 0 Then _ArrayAdd($aArray, $a)
    Next

_ArrayDisplay($aArray)

If that is not what you are after, please explain the criteria for what lines you would want to skip

[abdulrahmanok]

thanks for reply and about " please explain the criteria for what lines you would want to skip "

I want to start reading lines from Line 2 and then skip line each time like this:
Line 2
"skip line 3"
Line 4
"skip line 5"

Line 6

skip line 7

...

and after then put the final array values to Excel (C2 raw)

 

[JLogan3o13]

Ok, now to the other piece of my question - will the text file actually contain numbers on each line, or was this just an example?

[abdulrahmanok]

yes it was example and this is some edit on your code :

$aArray = FileReadToArray(@scriptdir & "\Calc.txt")
            If @error Then
            Else
                $oExcel = ObjCreate("Excel.Application")
                $oExcel.Visible = 1
                $file2 = @scriptdir & "\calc.txt"
            Local $aInput
$file = "calc.qu"

_FileReadToArray($file, $aInput)
For $i = 1 to UBound($aInput) -1
    if Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i)
            ConsoleWrite(@CRLF&$aInput[$i])
        Next
_ArrayDelete($aInput,$aInput) ;Empty pervius values
_ArrayDisplay($aInput)
            
                Local $oWorkbook = _Excel_BookOpen($oExcel, @scriptdir & "\Excel.xlsx")
                Sleep(100)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aInput, "C2")
            EndIf

but for some reason Excel output like this:

16 
2 ( I want Start from here and ignore previous line)
4
6
8
10
12
14
16

 

Edited August 25, 2017 by abdulrahmanok

[abdulrahmanok]

is there is anyway to ignore reading array[0] value ?

[JLogan3o13]

Instead of this:

For $i = 1 To Ubound($aInput) -1

try this:

For $i = 1 To $aInput[0]

 

[abdulrahmanok]

after some search I ended up with this:

#include <Excel.au3>
#include<file.au3>

    ProcessClose("Excel.exe")
            ;;;;;;;;;;;;;; Compare Between Calc And DaysArray And Add "None"
            $aArray = FileReadToArray(@scriptdir & "calc.txt")
            If @error Then
            Else
                $oExcel = ObjCreate("Excel.Application")
                $oExcel.Visible = 1
                $file2 = @scriptdir & "\calc.txt"
            Local $aInput
$file = "calc.txt"

_FileReadToArray($file, $aInput)
For $i = 1 to UBound($aInput) -1
    if Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i)
            ConsoleWrite(@CRLF&$aInput[$i])
        Next
_ArrayDelete($aInput,$aInput) ;Empty pervius values
_ArrayDelete($aInput,0) ;Delete First Value
_ArrayDisplay($aInput)
            
                Local $oWorkbook = _Excel_BookOpen($oExcel, @scriptdir & "\Excel.xlsx")
                Sleep(100)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aInput, "C2")
            EndIf

and it's worked perfectly , I will try your methods now

[abdulrahmanok]

3 minutes ago, JLogan3o13 said:

Instead of this:

For $i = 1 To Ubound($aInput) -1

try this:

For $i = 1 To $aInput[0]

 

Unfortunately still getting (16) value

[JLogan3o13]

Why are you reading the file to an array twice? Once here:

$aArray = FileReadToArray(@scriptdir & "calc.txt")

And then again here:

_FileReadToArray($file, $aInput)

Why not read it to array once and then manipulate?

I also see that you are declaring your calc.txt file multiple times, both inside variables and out:

FileReadToArray(@scriptdir & "calc.txt")

$file2 = @scriptdir & "\calc.txt"

$file = "calc.txt"

Makes it a lot easier if you simply declare to a variable once and then use that throughout.

[abdulrahmanok]

Thanks for your patience , Unfortunately after copy all of this to my real program it doesn't work as expected :
Code:

ProcessClose("Excel.exe")
;;;;;;;;;;;;;; Compare Between DaysArray And DaysArray And Add "None"
If @error Then
Else
    $oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = 1
    Local $aInput
    $file = "DaysArray.txt"
    _FileReadToArray($file, $aInput)
    For $i = 1 To UBound($aInput) - 1
        If Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i)
        ConsoleWrite(@CRLF & $aInput[$i])
    Next
    _ArrayDelete($aInput, $aInput) ;Empty pervius values
;   _ArrayDelete($aInput, 0) ;Delete First Value
    _ArrayDisplay($aInput)
    
    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel.xlsx")
    Sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aInput, "C2")
EndIf

"DaysArray.txt" contains:

Your Hours For 01-Aug-17 Is :
4.5
Your Hours For 03-Aug-17 Is :
2.5
Your Hours For 06-Aug-17 Is :
4
Your Hours For 08-Aug-17 Is :
3
Your Hours For 09-Aug-17 Is :
5
Your Hours For 10-Aug-17 Is :
7

Should I put this in new thread?

Edited August 25, 2017 by abdulrahmanok

[JLogan3o13]

No stick to a single thread. How about posting a real example of the DaysArray.txt, and what exactly you expect to be written to Excel? All of the "just for example" data makes it a bit confusing.

[junkew]

Use a regular expression to parse the lines you need

[water]

Some additional questions:

Why do you manually start up Excel and then use the Excel UDF functions?

11 hours ago, abdulrahmanok said:

$oExcel = ObjCreate("Excel.Application")

I would stick to the Excel UDF which has additonal error handling.

Why do you close the Excel process? _Excel_Open simply connects to a running instance or start a new one if needed.

Just my 2 cents to improve your script

[abdulrahmanok]

the real "DaysArray" attached .
I expect to see in Excel "C2" raw:

4.5
2.5
4
3
5
7

Why do you manually start up Excel and then use the Excel UDF functions?
because this is part of my main program  and it give user details in Excel when call Excel Func so must open file first and make it visible.

Why do you close the Excel process?

because I got some error's when keep it running and try to save overwrite it.

DaysArray.txt

[abdulrahmanok]

4 hours ago, junkew said:

Use a regular expression to parse the lines you need

I didn't understand what do you mean.

[junkew]

Something like this

• Attention point is the last line, not sure if you have CRLF there
  try also $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*?\r\n),"$2,") 

#include <Excel.au3>
#include<file.au3>

$fileToRead="C:\autoit_test\DaysArray.txt"

; Read the contents of the file using the handle returned by FileOpen.
Local $strFileRead = FileRead($fileToRead)

consolewrite("Initial file contents" & @CRLF)
consolewrite("=====================" & @CRLF)
consolewrite($strFileRead & @CRLF)

$strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?","$2,")

consolewrite("Only every 2nd line" & @CRLF)
consolewrite("=====================" & @CRLF)
consolewrite($strFileRead & @CRLF)

 

[abdulrahmanok]

thanks @junkew actually I got the true values  but the last issue that I can't convert ($strFileRead) to Array because excel putting all values in 1 cell like Image.

 

I tried to add @crlf

$strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?",@crlf&"$2")

still doesn't work.

The whole code:
 

#include <Excel.au3>
#include <file.au3>

ProcessClose("Excel.exe")
;;;;;;;;;;;;;; Compare Between DaysArray And DaysArray And Add "None"
If @error Then
Else
    $fileToRead="DaysArray.txt"
    $strFileRead = FileRead($fileToRead)
    $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?",@crlf&"$2")
consolewrite("Only every 2nd line" & @CRLF)
consolewrite("=====================" & @CRLF)
consolewrite($strFileRead & @CRLF)
    $oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible = 1
    Local $aInput
    $file = "calc.qu"
    _FileReadToArray($file, $aInput)
    For $i = 1 To UBound($aInput) - 1
        If Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i)
        ConsoleWrite(@CRLF & $aInput[$i])
    Next
    _ArrayDelete($aInput, $aInput) ;Empty pervius values
    _ArrayDelete($aInput, 0) ;Delete First Value
    ;_ArrayDisplay($aInput)
    
    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel.xlsx")
    Sleep(100)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $strFileRead& @CRLF, "C2")
EndIf

 

[junkew]

You can use stringsplit to make it an array see helpfile for examples

[abdulrahmanok]

Ty very much I Appreciated  your help, Final code:
 

#include <Excel.au3>
#include<file.au3>

$fileToRead="DaysArray.txt"

; Read the contents of the file using the handle returned by FileOpen.
Local $strFileRead = FileRead($fileToRead)

;~ consolewrite("Initial file contents" & @CRLF)
;~ consolewrite("=====================" & @CRLF)
;~ consolewrite($strFileRead & @CRLF)

$strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?","$2,")
$Array=stringsplit ($strFileRead,",")

_ArrayDisplay(  $Array,"Four matches, split apart, as expected")
consolewrite("Only every 2nd line" & @CRLF)
consolewrite("=====================" & @CRLF)
consolewrite($strFileRead & @CRLF)

 

Edited August 27, 2017 by abdulrahmanok