Sign in to follow this  
Followers 0
PerryRaptor

Filling Arrays from files

19 posts in this topic

Trying to fill an Array from an Excel .csv file. So far no luck. Anyone done this before?

Share this post


Link to post
Share on other sites



This matter what sort of array you want to use. In this example I suppose that is two-dimentional array, where first dim - rows, second - columns. It will ignore empty lines and not have any error checks :)

#include <File.au3>
$in_filename = "test.csv"

Dim $lines

_FileReadToArray($in_filename, $lines)

$temp = StringSplit($lines[1], ";"); Found columns number

Dim $array[ $lines[0] ][ $temp[0] ]

For $i = 1 To $lines[0]
    $temp = StringSplit($lines[$i], ";")
    If $temp[0] = 1 Then Continueloop
    For $j = 1 To $temp[0]
        $array[$i-1][$j-1] = $temp[$j]
    Next
Next

Share this post


Link to post
Share on other sites

I tried that command _FileReadToArray()...It returns a 1 which means that there is an error opening the file.

I'd like to populate an an array using Microsoft Excel. I've tried comma delimited and tabed delimited format with no luck.

Share this post


Link to post
Share on other sites

Is _FileReadtoArray() only good for single dimension arrays and files?

Share this post


Link to post
Share on other sites

This script does work, if you replace the ";" with "," both times;

;----------------------------------------------------------------------

;LazyCat script; modified Randall (StringSplit typo needs comma)

;----------------------------------------------------------------------

#include <File.au3>

;$in_filename = "test.csv"

$in_filename = FileOpenDialog("hi",@ScriptDir&"\SciTe","Files (*.csv)",5)

Dim $lines,$Display, $NumCols

_FileReadToArray($in_filename, $lines)

$temp = StringSplit($lines[1], ","); Found columns number

$NumCols=$temp[0]

Dim $array[ $lines[0] ][ $temp[0] ]

For $i = 1 To $lines[0]

    $temp = StringSplit($lines[$i], ",")

    If $temp[0] = 1 Then Continueloop

    For $j = 1 To $temp[0]

        $array[$i-1][$j-1] = $temp[$j]

    Next

Next

For $i = 1 To $lines[0]-1

;----------------------------------------------------------------------

;Display Array

;----------------------------------------------------------------------

    For $j = 1 To $NumCols

  $Display  = $Display&"array["&String($i-1)&"]["&String($j-1)&"]="&chr(9)&$array[$i-1][$j-1]&@CRLF

    Next

Next

MsgBox(4096, "Here it is",$Display)

Randall

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

What happens if one of the cells values contains a , or a " ?

My opinion is that reading from a '.csv file' is not the best approach when communicating with Excel.

I recommend Excel VBA or COM.

Edited by bshoenhair

Share this post


Link to post
Share on other sites

My opinion is that reading from a '.csv file' is not the best approach when communicating with Excel.

I recommend Excel VBA or COM.

<{POST_SNAPBACK}>

using COM or vba is best; can you show me your scrip to get the array?

Thanks, Randall

(I see the ";", just was not sure if the poster was still having trouble understanding you could still use arrays....)

Share this post


Link to post
Share on other sites

Yes the updated loop works!!!

A COM example would be nice to see; however, I'm good for now.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

using COM or vba is best; can you show me your scrip to get the array?

Thanks, Randall

<{POST_SNAPBACK}>

I like to think of a spreadsheet as a super multidimensional array so really there is no need to create an array since it is already there. I will give an example on how to read from the values column by column with COM but you will need AutoIts latest beta and Excel to run it.

$Excel = ObjCreate("Excel.Application"); Create an Excel Object
if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif
if not IsObj($Excel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif
$Excel.Visible = 1   ; Let the guy show himself - Set to 0 to run excel invisible
$NewBook = $Excel.workbooks.add   ; Add a new workbook
; Example: Fast Fill some cells with there address
Msgbox (0,"","Click 'ok' to fastfill some cells")
For $c In $NewBook.Sheets("Sheet1").Range("a1:n1").Cells
    $c.Value = $c.Address
    For $i = 1 To 8
        $c.Offset($i,0).Value = $c.Offset($i,0).Address
    Next
Next

msgbox(0,"ExcelTest","Click 'ok' to read the cells in each column.")
$Values = ""
For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells
    $Values = $Values & $c.Text & @TAB
Next
msgbox(0,"Values Read",StringStripWS($Values,3))
$Excel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel
$Excel.quit        ; Get rid of him.
$Excel = 0         ; Loose this object.
               ; Object will also be automatically discarded when you exit the script
exit
Edited by bshoenhair

Share this post


Link to post
Share on other sites

Reading up on AU COM; I think I'll wait til the DEV Team gets further with it. This COM feature will speed up and shrink my scripts considerably. I'm thinking COM will put and end to VBScript. :)

Share this post


Link to post
Share on other sites

This script does work, if you replace the ";" with "," both times;

Randall

<{POST_SNAPBACK}>

I tried this script on a log file I have I get an error message

line 17

$array[$i-1][$j-1] = $temp[$j]

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

Whats wrong here

Share this post


Link to post
Share on other sites

Is your CSV file comma delimited or does it use a semicolon, space, or another charactor?

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

I tried this script on a log file I have I get an error message

line 17

$array[$i-1][$j-1] = $temp[$j]

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

Whats wrong here

I had same error message on 1 machine only; this used to work; I am still not sure what happened

Randall

Edited by randallc

Share this post


Link to post
Share on other sites

I'm still using version 3.1.1 and it's working

Share this post


Link to post
Share on other sites

Is your CSV file comma delimited or does it use a semicolon, space, or another charactor?

<{POST_SNAPBACK}>

it's tab delimited

Share this post


Link to post
Share on other sites

You will have to change two lines. Change these two lines that start with $temp=StringSplit($lines... where ";" to " ". Also, you need to save the Excel file as a tab-delimited file (*.txt) to make it work.

$temp = StringSplit($lines[1], ";"); Found columns number

Dim $array[ $lines[0] ][ $temp[0] ]

For $i = 1 To $lines[0]
    $temp = StringSplit($lines[$i], ";")

Last, I had trouble initialy trying to use tabs. The trouble was empty cells in the Excel file would cause data to move to the left.

Share this post


Link to post
Share on other sites

To be a tabbed-delimited file, change the semicolon to Chr(9) without quotes. Chr(9) is the tab space.

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