Deprecated: Array and string offset access syntax with curly braces is deprecated in /homepages/21/d38531796/htdocs/jose/smfforum/Sources/Subs.php on line 3825
read csv/xls file in array

Author Topic: read csv/xls file in array  (Read 405 times)

0 Members and 1 Guest are viewing this topic.

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
read csv/xls file in array
« on: November 19, 2021, 12:02:38 PM »
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]
   
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
« Last Edit: November 20, 2021, 11:56:04 PM by Nicola_Piano »

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #1 on: November 21, 2021, 02:19:50 PM »
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.

Offline Charles Pegge

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 699
  • User-Rate: +27/-1
    • Charles Pegge
Re: read csv/xls file in array
« Reply #2 on: November 21, 2021, 06:09:53 PM »
Hi Nicola,

No need to open and close files in o2:

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

Online Zlatko Vid

  • Full Member
  • ***
  • Posts: 159
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #3 on: November 21, 2021, 06:48:04 PM »
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 ....

Online Zlatko Vid

  • Full Member
  • ***
  • Posts: 159
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #4 on: November 21, 2021, 06:51:48 PM »
...ahhh CSV is
Quote
? A Comma Separated Values (CSV) file is a plain text file that contains a list of data.


ok so you need comma delimiter ","

Online Zlatko Vid

  • Full Member
  • ***
  • Posts: 159
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #5 on: November 21, 2021, 08:30:55 PM »
Hi
is that what you need ..or something similar
heh i little bit rust with programming ..but work for me
Code: [Select]
'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

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #6 on: November 21, 2021, 08:42:55 PM »
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")

« Last Edit: November 21, 2021, 11:26:37 PM by Nicola_Piano »

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #7 on: November 22, 2021, 11:25:22 PM »
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]
'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


« Last Edit: November 22, 2021, 11:27:49 PM by Nicola_Piano »

Online Zlatko Vid

  • Full Member
  • ***
  • Posts: 159
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #8 on: November 23, 2021, 01:54:06 PM »
Quote
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 ?

« Last Edit: November 23, 2021, 01:56:17 PM by Zlatko Vid »

Online Zlatko Vid

  • Full Member
  • ***
  • Posts: 159
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #9 on: November 23, 2021, 02:06:17 PM »
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.

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #10 on: November 23, 2021, 09:22:00 PM »
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.

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #11 on: November 23, 2021, 10:00:16 PM »
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]
'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


« Last Edit: November 23, 2021, 10:12:33 PM by Nicola_Piano »

Offline Nicola_Piano

  • Newbie
  • *
  • Posts: 43
  • User-Rate: +0/-0
Re: read csv/xls file in array
« Reply #12 on: December 08, 2021, 11:16:39 PM »
Hi,
for xls Are there any suggestions?

Hello
« Last Edit: December 20, 2021, 10:26:36 AM by Nicola_Piano »

Offline Charles Pegge

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 699
  • User-Rate: +27/-1
    • Charles Pegge
Re: read csv/xls file in array
« Reply #13 on: January 05, 2022, 06:45:55 PM »
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.