abdulrahmanok

Read file text to excil and bybass line(SOLVED)

20 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#3 ·  Posted

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)

 

Share this post


Link to post
Share on other sites

#4 ·  Posted

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?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#6 ·  Posted

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

Share this post


Link to post
Share on other sites

#7 ·  Posted

Instead of this:

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

try this:

For $i = 1 To $aInput[0]

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#8 ·  Posted

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

Share this post


Link to post
Share on other sites

#9 ·  Posted

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

Share this post


Link to post
Share on other sites

#10 ·  Posted

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#12 ·  Posted

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#14 ·  Posted

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 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted

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

Share this post


Link to post
Share on other sites

#16 ·  Posted

4 hours ago, junkew said:

Use a regular expression to parse the lines you need

I didn't understand what do you mean.

Share this post


Link to post
Share on other sites

#17 ·  Posted

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)

 

Share this post


Link to post
Share on other sites

#18 ·  Posted

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

 

Excel.png

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

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

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