Jump to content
Sign in to follow this  
JohnRichard

Help on inserting array to excel

Recommended Posts

JohnRichard

i have this problem recently on how to write to data from text file into excel. that good guys named AceGuy, Big Daddy and PicAxe helped me to do it. Thanks for you guys. now i have this problem trying to write the data starting on the 2nd row of the excel. in the helpfile i found _excelwritearray which does writing the data to which row number you want. but i can't seem to write the data as an array. all of the data are written to the 2nd row to which i wanted but all of the data were inserted only on the first column of the excel...

can someone check and help...appreciate greatly your help...

here is what i have on the script....

txt file is this...

192.168.1.25 | ACER1 | user1|Denver McCain|12:24:51 PM|10/05/2009
192.168.1.26 | ACER2 | user1|Denver McCain|12:25:19 PM|10/05/2009
192.168.1.35 | ACER3 | user1|Denver McCain|2:14:33 PM|10/05/2009
192.168.1.37 | ACER4 | user1|Denver McCain|2:16:32 PM|10/05/2009
192.168.1.38 | ACER5 | user1|Denver McCain|2:17:38 PM|10/05/2009
192.168.1.40 | ACER6 | user1|Denver McCain|2:19:25 PM|10/05/2009

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

$sFilePath = @DesktopDir & "\Test.xls"


If Not _FileCreate($sFilePath) Then
   MsgBox(4096, "Error", " Error Creating File - " & @error)
EndIf

$oExcel=_excelbooknew()

dim $var
_FileReadToArray(@DesktopDir & "\log.txt",$var)


For $i = 1 to $var[0] - 1
    $stringsplit=StringSplit($var[$i], @CRLF)
    For $i2 = 1 to $stringsplit[0]
        _ExcelWriteArray($oExcel, 2, 1, $var, $i, $i2)
    ;_ExcelWriteCell($oExcel,$stringsplit[$i2],$i,$i2)
    Next
Next

Share this post


Link to post
Share on other sites
Malkey

i have this problem recently on how to write to data from text file into excel. that good guys named AceGuy, Big Daddy and PicAxe helped me to do it. Thanks for you guys. now i have this problem trying to write the data starting on the 2nd row of the excel. in the helpfile i found _excelwritearray which does writing the data to which row number you want. but i can't seem to write the data as an array. all of the data are written to the 2nd row to which i wanted but all of the data were inserted only on the first column of the excel...

can someone check and help...appreciate greatly your help...

here is what i have on the script....

txt file is this...

192.168.1.25 | ACER1 | user1|Denver McCain|12:24:51 PM|10/05/2009
192.168.1.26 | ACER2 | user1|Denver McCain|12:25:19 PM|10/05/2009
192.168.1.35 | ACER3 | user1|Denver McCain|2:14:33 PM|10/05/2009
192.168.1.37 | ACER4 | user1|Denver McCain|2:16:32 PM|10/05/2009
192.168.1.38 | ACER5 | user1|Denver McCain|2:17:38 PM|10/05/2009
192.168.1.40 | ACER6 | user1|Denver McCain|2:19:25 PM|10/05/2009

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

$sFilePath = @DesktopDir & "\Test.xls"


If Not _FileCreate($sFilePath) Then
   MsgBox(4096, "Error", " Error Creating File - " & @error)
EndIf

$oExcel=_excelbooknew()

dim $var
_FileReadToArray(@DesktopDir & "\log.txt",$var)


For $i = 1 to $var[0] - 1
    $stringsplit=StringSplit($var[$i], @CRLF)
    For $i2 = 1 to $stringsplit[0]
        _ExcelWriteArray($oExcel, 2, 1, $var, $i, $i2)
;_ExcelWriteCell($oExcel,$stringsplit[$i2],$i,$i2)
    Next
Next
I believe this gives the result you are after.

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

Local $sFilePath = @DesktopDir & "\Test.xls"

If Not _FileCreate($sFilePath) Then
    MsgBox(4096, "Error", " Error Creating File - " & @error)
EndIf

$oExcel = _excelbooknew()

Dim $var
_FileReadToArray(@DesktopDir & "\log.txt", $var)

For $i = 1 To $var[0]
    $stringsplit = StringSplit($var[$i], "|")
    _ExcelWriteArray($oExcel, $i + 1, 1, $stringsplit, 0, 1)
Next
;

Share this post


Link to post
Share on other sites
JohnRichard

thanks malkey. this is what i wanted to happen. :party: it takes me a day to think how will it work,

works great. :)

greatly appreciate it malkey

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  

×