read csv/xls file in array

[Nicola_Piano]

Hi,
how can i read a whole xls or csv file in an array?

For csv I generally use a read cycle until the end of the file.

Code Select Expand

   
i=0
Open filein$ For Input As fnum
Do While Not EOF(fnum)
   Line Input #fnum, ar$
   array$(i)=ar$
   i=i+1
Loop
Close fnum


But is there already a function that does this job?

Thanks

[Nicola_Piano]

An example in Python (seen on the internet)

f = open('my_file.txt', 'r+')
my_file_data = f.read()
f.close()

The above code opens 'my_file.txt' in read mode then stores the data it reads from my_file.txt in my_file_data and closes the file. The read function reads the whole file at once.

[Charles Pegge]

Hi Nicola,

No need to open and close files in o2:

string MyData
MyData=getfile("MyFile.txt")

[Zlatko Vid]

Yes that is true ..and hello Charles !
He asking how to read data into array ..hmm
i think that csv need some sort of delimiter char to load each data into array index
so he need some sort of toknizer
given example load whole file into string buffer
Well delimiter can be a CRLF string at end of each line ?
What is data in csv?
lot of questions ....

[Zlatko Vid]

...ahhh CSV is

? A Comma Separated Values (CSV) file is a plain text file that contains a list of data.

ok so you need comma delimiter ","

[Zlatko Vid]

Hi
is that what you need ..or something similar
heh i little bit rust with programming ..but work for me

Code Select Expand

'csv reader
string mycsvData,ch,cbuff
string crlf = chr(13)+chr(10)
mycsvData = getFile("MyCSVFile.txt")
print mycsvData

string cArr[1024],temp,out
int i,index,flen
flen = len(mycsvData)

for i = 1 to flen
  'get char
  ch = mid(mycsvData,i,1)

  if asc(ch) <> 44
    temp = temp + ch         ' put char into temp

  end if
   
  if asc(ch) = 44     
     index = index+1         ' incr index
     cArr[index] = temp     ' add temp to array
     'print "INDEX:" + cArr[index]
     temp = ""              'clear cbuff
     out = out + cArr[index] + crlf   'put array elementin out string     
  end if


next i

print out



data are presented in message
file is simple .txt created in Notepad and is in same folder where example is
file :

one,two,three,four,five,six,seven

you may noted that last element is not in output so yo

[Nicola_Piano]

Hi Charles, thanks for the answer.
I noticed that myData is not an array, but a variable that contains the contents of the file. So, as Zlatko showed, you need to turn that string into an array ... right?

Charles, is there also a solution for excel files?


@Zlatko
CSV are files with data separated by commas or by semicolons.

Thanks for your example.

A dear greeting


p.s.
Is there a function that splits a string?
like: array = strsplit (string $, "delimiter")

[Nicola_Piano]

Hello
This is a development by bringing together some information that I have found and processed.
The whole "file" is read and inserted into the 2-dimensional array.

What do you think?
cheers

Code (o2) Select Expand


'csv reader
use console
string mycsvData,ch
string temp
int i,j,x,y,flen,my

'-----------------------
  'MULTIDIMENSIONAL ARRAYS
  '=======================
  macro cari(x,y) av(y*1024+x)
  dim string av[1024*1024]
'--------------------------
string txt="0746;200849;0746200849;200849;Collaudo KO
0746;201563;0746201563;201563;Collaudo OK
0746;202038;0746202038;202038;Collaudo KO
0746;203174;0746203174;203174;Collaudo KO
0746;203584;0746203584;203584;Collaudo KO
0746;203729;0746203729;203729;Collaudo OK
0746;204520;0746204520;204520;Collaudo KO
0746;204522;0746204522;204522;Collaudo KO
0746;204612;0746204612;204612;Collaudo KO
0746;205206;0746205206;205206;Collaudo KO
0746;220035;0746220035;220035;Collaudo KO
0746;270497;0746270497;270497;Collaudo KO
0746;270561;0746270561;270561;Collaudo KO
0746;270625;0746270625;270625;Collaudo KO
0746;274176;0746274176;274176;Collaudo KO
0746;274524;0746274524;274524;Collaudo KO
0746;296913;0746296913;296913;Collaudo KO
0746;578473;0746578473;578473;Collaudo KO
0746;706572;0746706572;706572;Collaudo KO
0746;825461;0746825461;825461;Collaudo KO
0746;922203;0746922203;922203;Collaudo KO
0761;220193;0761220193;220193;Collaudo KO
0761;220472;0761220472;220472;Collaudo KO
0761;250186;0761250186;250186;Collaudo KO
0746;200849;0746200849;200849;Collaudo KO
"

'mycsvData = getFile("readfile.csv")
mycsvData = txt
printl mycsvData

flen = len(mycsvData)
x=1
for i = 1 to flen
ch = mid(mycsvData,i,1)
print ch
select asc(ch)
case 13,0
y=y+1
cari(x,y)=temp
x=x+1
my=y
y=0
printl "temp=" temp
temp=""

case 59,10           '44=,  59= ;
y=y+1
cari(x,y)=temp
'print cari(x,y) " "
temp=""

case else
temp = temp + ch         ' put char into temp

   end select

next i

printl "--------------------"
printl
for i=1 to x
for j=1 to my
print cari(i,j)
next j
printl
next i

waitkey




[Zlatko Vid]

Is there a function that splits a string?

as far as i know NO

you sayed 2-dimensional..hmm do you mean this one :

dim string av[1024*1024]

hmm .. i am not sure that this is 2 dimensional
do you get idea from o2 ..Charles examples ?

[Zlatko Vid]

Nicola
I am not sure what kind of format you wish to get
first is number
0746...which is in all printL the same
then following other datas
so i guess 1 dim must hold that number then other 2dim must hold data ??
or something else ..i really have no idea what might be that ...
Charles probably know  more or better to explain.

[Nicola_Piano]

Hi Zlatko,
actually it is a pseudo-two-dimensional array, but in this test it works. (An example written by Charles in his help)

The numbers are repeated as it is a csv file that contains, for example, a list of telephone numbers with area code and number ...
The goal is to directly access a certain position of the pseudo-array, and this seems to be possible.

[Nicola_Piano]

Much better now.
I have corrected some errors.
However the pseudo-2 array works.
I also tried writing the TXT data to a CSV file, getting the same results.

Code (o2) Select Expand


'csv reader
use console
string mycsvData,ch
string temp
int i,j,x,y,flen,my,mx

'-----------------------
  'MULTIDIMENSIONAL ARRAYS
  '=======================
  macro cari(x,y) av(y*1024+x)
  dim string av[1024*1024]
'-------------------------------------------

string txt="0746;200849;0746200849;200849;Collaudo KO
0746;201563;0746201563;201563;Collaudo OK
0746;202038;0746202038;202038;Collaudo KO
0746;203174;0746203174;203174;Collaudo KO
0746;203584;0746203584;203584;Collaudo KO
0746;203729;0746203729;203729;Collaudo OK
0746;204520;0746204520;204520;Collaudo KO
0746;204522;0746204522;204522;Collaudo KO
0746;204612;0746204612;204612;Collaudo KO
0746;205206;0746205206;205206;Collaudo KO
0746;220035;0746220035;220035;Collaudo KO
0746;270497;0746270497;270497;Collaudo KO
0746;270561;0746270561;270561;Collaudo KO
0746;270625;0746270625;270625;Collaudo KO
0746;274176;0746274176;274176;Collaudo KO
0746;274524;0746274524;274524;Collaudo KO
0746;296913;0746296913;296913;Collaudo KO
0746;578473;0746578473;578473;Collaudo KO
0746;706572;0746706572;706572;Collaudo KO
0746;825461;0746825461;825461;Collaudo KO
0746;922203;0746922203;922203;Collaudo KO
0761;220193;0761220193;220193;Collaudo KO
0761;220472;0761220472;220472;Collaudo KO
0761;250186;0761250186;250186;Collaudo KO
0746;200849;0746200849;200849;Collaudo KO
"

'mycsvData = getFile("readfile.csv")
mycsvData = txt
printl mycsvData

flen = len(mycsvData)
x=1
for i = 1 to flen
ch = mid(mycsvData,i,1)
print asc(ch) "."
select asc(ch)
case 13
y=y+1
cari(x,y)=temp
x=x+1
my=y
y=0
printl '"temp=" temp
temp=""

case 59          '44=,  59= ;
y=y+1
cari(x,y)=temp
'print cari(x,y) " "
temp=""

case 10
'noting to do

case else
temp = temp + ch         ' put char into temp

   end select
mx=x-1
next i

printl "--------------------"
printl
for i=1 to mx
for j=1 to my
print "[" i ","j "]" cari(i,j)
next j
printl
next i
printl
printl cari(2,3)           'goes directly to the location.
printl cari(5,5)

waitkey




[Nicola_Piano]

Hi,
for xls Are there any suggestions?

Hello

[Charles Pegge]

Hi Nicola,

You will have to Google for XLS format details. It's quite a major project, so you will need to break it down into small testable steps.