Import CSV File Into 2D Array

[zoso51]

Hi All,

I've been surfing these forums for quite a while but never posted, but I've hit a roadblock and can't seem to figure out where to go next. Some background on my project: I have a program that I wrote that reads an excel file, pulls specific information from that file and exports it to a csv file. The file is of unknown number of lines, but unlikely that it will be more than 20.

The csv file will look something like this:

01234,BobsBobShop,Service,Mike,Tim,2019/04/20,2019/05/20,N/A,2019/06/10,YES
05778,MikesMikeShop,Design&Build,John,Paul,2019/04/20,2019/05/20,,2019/06/10,YES
02487,BillsBillShop,Build,Tom,Tim,2019/03/10,N/A,2019/04/08,2019/06/10,NO
02147,TomsTomShop,Design,Eric,Tim,2019/04/20,2019/05/20,2019/06/10,N/A,NO

What I would like to do is import this into an array like so:
array[0][0]=01234
array[0][1]=BobsBobShop
array[0][2]=Service
array[0][3]=Mike,
...
array[1][0]=05778
array[1][1]=MikesMikeShop
...
and so on. So the first dimension of the array determines the row of the csv, the second dimension is the data from each row.

From there I will do some UI magic and make it display what I would like.

I've tried to use https://docs.rainmeter.net/snippets/read-write-file/ to read a full file or even line by line but I can't get it to work. I feel like I'm missing something huge. I also found this: https://forum.rainmeter.net/viewtopic.php?f=5&t=6998 but then tried to modify it but to no avail. Do I need to do all of this data reading through a script as in that example I posted? Or can it be done right in the rainmeter programming?

Any help on this would be appreciated. Also, is hiring someone to program this for me (this would not be resold, it's custom to my needs) allowed on this forum?

Thank you

[jsmorley]

This is really only going to be possible with a Lua script in Rainmeter. Rainmeter itself doesn't have any ability to deal with arrays.

So at a high level, you will want to use Lua to:

1) Read in the .cvs file (be sure it is a UTF-8 encoded file, or ANSI with no Unicode characters in it)
2) Parse the comma delimited fields into your array, or maybe arrays.
3) Use Lua to assign the values from the array(s) to what is displayed in probably a great many Meters in Rainmeter.

We don't allow skin requests or offers of payment for skins on the forums. We will be happy to help out as you progress.

This won't be a trivial effort, but not rocket science either. Lua is pretty good at arrays, what it calls "tables".

http://www.lua.org/pil/2.5.html

[zoso51]

Thank you for your reply. I have done some work in excel VBA and C++. But Lua seems just different enough to be confusing so I'll have to dig into it.

Do you have a recommendation for a debugger for Lua? That way I can just write the code to import and then interface it with Rainmeter after I get that portion working.

Thanks!

[jsmorley]

Maybe this will get you off and running...

Test.ini:

Code: Select all

[Rainmeter]
Update=1000
DynamicWindowSize=1
AccurateText=1

[Variables]

[Lua]
Measure=Script
ScriptFile=Test.lua
UpdateDivider=-1

[MeterDummy]
Meter=String

myCSV.csv:

Code: Select all

01234,BobsBobShop,Service,Mike,Tim,2019/04/20,2019/05/20,N/A,2019/06/10,YES
05778,MikesMikeShop,Design&Build,John,Paul,2019/04/20,2019/05/20,,2019/06/10,YES
02487,BillsBillShop,Build,Tom,Tim,2019/03/10,N/A,2019/04/08,2019/06/10,NO
02147,TomsTomShop,Design,Eric,Tim,2019/04/20,2019/05/20,2019/06/10,N/A,NO

Test.lua:

Code: Select all

function Update()

	FilePath = SKIN:MakePathAbsolute('myCSV.csv')
	
	local arrayList = {}

	for line in io.lines(FilePath) do
		local numberField1, nameField1, typeField1, nameField2, nameField3, dateField1, dateField2, dateField3, dateField4, answerField1 = line:match('%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-)$')
		arrayList[#arrayList + 1] = { numberField1=numberField1, nameField1=nameField1, typeField1=typeField1, nameField2=nameField2, nameField3=nameField3, dateField1=dateField1, dateField2=dateField2, dateField3=dateField3, dateField4=dateField4, answerField1=answerField1 }
	end
	
	print(arrayList[1]['nameField1'])
	print(arrayList[3]['nameField2'])
	print(arrayList[2]['dateField1'])

end

1.jpg

I have no idea what your data is all about, so I just gave some generic field names.

Note that in Lua, EVERYTHING is case-sensitive.

[jsmorley]

If we take that one level more...

Test.ini:

Code: Select all

[Rainmeter]
Update=1000
DynamicWindowSize=1
AccurateText=1

[Variables]

[Lua]
Measure=Script
ScriptFile=Test.lua
Disabled=1

[MeterField1]
Meter=String
FontSize=11
FontWeight=400
FontColor=255,255,255,255
SolidColor=47,47,47,255
Padding=5,5,5,5
AntiAlias=1
DynamicVariables=1
Text=[&Lua:GetField(1,'nameField1')]

Test.lua:

Code: Select all

function Initialize()

	FilePath = SKIN:MakePathAbsolute("myCSV.csv")
	
	arrayList = {}

	for line in io.lines(FilePath) do
		numberField1, nameField1, typeField1, nameField2, nameField3, dateField1, dateField2, dateField3, dateField4, answerField1 = line:match('%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-)$')
		arrayList[#arrayList + 1] = { numberField1=numberField1, nameField1=nameField1, typeField1=typeField1, nameField2=nameField2, nameField3=nameField3, dateField1=dateField1, dateField2=dateField2, dateField3=dateField3, dateField4=dateField4, answerField1=answerField1 }
	end
	
end

function GetField(indexArg, fieldArg)

	return arrayList[indexArg][fieldArg]

end

1.jpg

https://docs.rainmeter.net/manual/lua-scripting/inline-lua/

[zoso51]

Thank you so much for your help! I haven't had a chance to really dig into the code you posted yet but I will take a look soon (deadlines got pushed up on other projects ) and see if I can figure out and let you know. I appreciate it

[zoso51]

Hey jsmorely,

So I spent some time on this and your code has been a huge help, thanks!

I do have a question though, I'm going to have an unknown number of rows in my csv file, is there an easy way to pull the number of rows back into my .ini file? Can I have two different return functions? I was trying something like below but it couldn't get it working. the first function is yours (this is from your original Test.lua code) and works well, the second function I'm trying to return the total number of lines.

Code: Select all

function GetField(indexArg, fieldArg)

	return arrayList[indexArg][fieldArg]

end

function Getrows()

	return(io.lines(FilePath))
end

then in my .ini file I tried to declare that function like:

Code: Select all

Job1Num=&Lua:Getrows()

Any idea what I'm doing wrong with that?

My second question is, if I have an unknown number of rows, I will need to increase the size of my meter as the rows increase, is there an elegant way to do that? Or do I simply have to hard code as many meters as necessary and then use/don't use them based on how many rows I have?

Thanks again!

[jsmorley]

Hey jsmorely,

So I spent some time on this and your code has been a huge help, thanks!

I do have a question though, I'm going to have an unknown number of rows in my csv file, is there an easy way to pull the number of rows back into my .ini file? Can I have two different return functions? I was trying something like below but it couldn't get it working. the first function is yours (this is from your original Test.lua code) and works well, the second function I'm trying to return the total number of lines.

Code: Select all

function GetField(indexArg, fieldArg)

	return arrayList[indexArg][fieldArg]

end

function Getrows()

	return(io.lines(FilePath))
end

then in my .ini file I tried to declare that function like:

Code: Select all

Job1Num=&Lua:Getrows()

Any idea what I'm doing wrong with that?

My second question is, if I have an unknown number of rows, I will need to increase the size of my meter as the rows increase, is there an elegant way to do that? Or do I simply have to hard code as many meters as necessary and then use/don't use them based on how many rows I have?

Thanks again!

Code: Select all

function GetField(indexArg, fieldArg)

	return arrayList[indexArg][fieldArg]

end

function Getrows()

	return(#arrayList)
	
end

Code: Select all

Job1Num=[&Lua:Getrows()]
DynamicVariables=1

The "#" prefix on a table name means "size", or in this context, "count".

It will be tricky to return any unknown and unlimited number of elements. While a meter will grow to accommodate what is in it, and you can insert '\r\n' between values in what you return to have each be on a separate line, if you want multiple meters, you are just going to have to hard-code some set number of them.

[jsmorley]

Be careful how and where you use that last bit of your code.

Code: Select all

Job1Num=[&Lua:Getrows()]
DynamicVariables=1

That actually can't be in [Variables], and really can't work as is, as [Variables] does not support DynamicVariables.

You will need a Calc measure or something to hold that value.

Code: Select all

[Job1Num]
Measure=Calc
Formula=[&Lua:Getrows()]
DynamicVariables=1
UpdateDivider=-1

Then use the [Job1Num] or [&Job1Num] section variable wherever you want to use that value.

Either that, or have a SKIN:Bang('!SetVariable', 'Job1Num', #arrayList) in the Initialize() portion of your Lua code, sending that any time after you collect the array. I would vote for this approach, as the number of rows is reasonably static at any given time, and this is probably the most simple. Then you don't need the Getrows() function at all.

[jsmorley]

what is wrong here ?

Code: Select all

[Get]
Measure=Script
ScriptFile=Get.lua
Disabled=1

[GetRows]
Measure=Calc
Formula=[&Lua:Getrows()]
DynamicVariables=1
UpdateDivider=-1


[MeterGet]
X=100
Y=0
Meter=String
AntiAlias=1
Text=[&GetRows]
DynamicVariables=1

Formula=[&Lua:Getrows()] : Where is the Script measure called [Lua]? I don't see one.