13 Dec
2022
13 Dec
'22
2:13 a.m.
Here's the task: I need to take an Excel spreadsheet from Sharepoint,
slice & dice it, and write the output into a database
Tools I have: Windows 10 laptop with WSL2 installed and I have admin
rights, so I can install whatever.
If I can't read from Sharepoint, then I can manually save a copy
locally. Automation would be nice
If I can't read the Excel spreadsheet, then I can manually open it and
write a csv
I can use what ever database I want that I can install, sqlite, mysql,
mariadb, postgresql or even MongoDB, so not a problem
I've done lots of slicing & dicing text files in a multitude of
languages, I just plan to use Ruby because it's my favorite
It's the first couple of steps that I would like to automate. Any
suggestions?
-- Matt
13 Dec
13 Dec
2:26 a.m.
New subject: [ruby-talk:444033] Re: Processing a spreadsheet
On 12/13/22 03:13, Matt Lawrence wrote:
Here's the task: I need to take an Excel
spreadsheet from Sharepoint,
slice & dice it, and write the output into a database
Tools I have: Windows 10 laptop with WSL2 installed and I have admin
rights, so I can install whatever.
If I can't read from Sharepoint, then I can manually save a copy
locally. Automation would be nice
I am not familiar with Sharepoint, but if you can access it as a web
application, you can use Ferrum, which automates Chromium:
https://github.com/rubycdp/ferrum
Other than that, there are a couple of Sharepoint gems for Ruby (most do
it via a REST API as I see):
https://rubygems.org/search?query=sharepoint - the first one on that
list seems to be the most maintained
If I can't read the Excel spreadsheet, then I can
manually open it and
write a csv
You can use Roo to parse any common spreadsheet format:
https://github.com/roo-rb/roo
I can use what ever database I want that I can
install, sqlite, mysql,
mariadb, postgresql or even MongoDB, so not a problem
To interface with an SQL
database, I recommend Sequel:
https://sequel.jeremyevans.net/
I've done lots of slicing & dicing text files
in a multitude of
languages, I just plan to use Ruby because it's my favorite
It's the first couple of steps that I would like to automate. Any
suggestions?
-- Matt
2:41 a.m.
New subject: [ruby-talk:444034] Re: Processing a spreadsheet
Thanks for the info.
-- Matt
On 12/12/22 20:26, hmdne wrote:
On 12/13/22 03:13, Matt Lawrence wrote:
Here's the task: I need to take an Excel
spreadsheet from
Sharepoint, slice & dice it, and write the output into a database
Tools I have: Windows 10 laptop with WSL2 installed and I have admin
rights, so I can install whatever.
If I can't read from Sharepoint, then I can manually save a copy
locally. Automation would be nice
I am not familiar with Sharepoint, but if you can access it as a web
application, you can use Ferrum, which automates Chromium:
https://github.com/rubycdp/ferrum
Other than that, there are a couple of Sharepoint gems for Ruby (most
do it via a REST API as I see):
https://rubygems.org/search?query=sharepoint - the first one on that
list seems to be the most maintained
If I can't read the Excel spreadsheet, then I
can manually open it
and write a csv
You can use Roo to parse any common spreadsheet format:
https://github.com/roo-rb/roo
I can use what ever database I want that I can
install, sqlite,
mysql, mariadb, postgresql or even MongoDB, so not a problem
To interface with an
SQL database, I recommend Sequel:
https://sequel.jeremyevans.net/
I've done lots of slicing & dicing text
files in a multitude of
languages, I just plan to use Ruby because it's my favorite
It's the first couple of steps that I would like to automate. Any
suggestions?
-- Matt
______________________________________________
ruby-talk mailing list -- ruby-talk(a)ml.ruby-lang.org
To unsubscribe send an email to ruby-talk-leave(a)ml.ruby-lang.org
ruby-talk info --
https://ml.ruby-lang.org/mailman3/postorius/lists/ruby-talk.ml.ruby-lang.or… 
2:19 p.m.
New subject: [ruby-talk:444037] Re: Processing a spreadsheet
On 2022-12-13 10:26 am, hmdne wrote:
If I can't read the Excel spreadsheet, then I can
manually open it and
write a csv
You can use Roo to parse any common spreadsheet format:
https://github.com/roo-rb/roo
In general, I agree with Roo as a recommendation because it has worked
fine for us when using XLSX files. Just a couple of notes more:
[1] If you find that reading/ writing an Excel file becomes too onerous
and/ or slow, there are some options in the Java world that can be used
from JRuby. I have a small post about connecting to a Java library from
JRuby/ Ruby code. See:
https://notepad.onghu.com/2021/jruby-using-apache-poi-to-generate-xlsx-file…
[2] Also, Ruby works great using RubyInstaller2 natively on Windows.
There is no real need to use WSL2 unless you prefer it for some reason
:) If you explore that side, see:
https://notepad.onghu.com/2022/ruby3.1-windows-day0-install-hello_world/
Good luck!
Cheers,
Mohit.
523
days inactive
523
days old
3 comments
3 participants
participants (3)
-
hmdne -
Matt Lawrence -
Mohit Sindhwani