Filling Arrays from files

[PerryRaptor]

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

[Lazycat]

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

[PerryRaptor]

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.

[PerryRaptor]

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

[Lazycat]

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

Yes, it's only read file into array: one element of array is one line of file.

[randallc]

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

[bshoenhair]

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 April 8, 2005 by bshoenhair

[Lazycat]

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

Randall

<{POST_SNAPBACK}>

You right... but not actually typo, this was Excel exported file, with ; as delimiter...

[randallc]

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

[PerryRaptor]

Yes the updated loop works!!!

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

[bshoenhair]

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 April 9, 2005 by bshoenhair

[PerryRaptor]

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.

[Skorn]

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

[PerryRaptor]

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

[randallc]

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 April 18, 2005 by randallc

[PerryRaptor]

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

[Skorn]

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

<{POST_SNAPBACK}>

it's tab delimited

[PerryRaptor]

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.

[PerryRaptor]

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