[ruby-talk:444032] Processing a spreadsheet
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
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
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@ml.ruby-lang.org To unsubscribe send an email to ruby-talk-leave@ml.ruby-lang.org ruby-talk info -- https://ml.ruby-lang.org/mailman3/postorius/lists/ruby-talk.ml.ruby-lang.org...
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-files... [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.
participants (3)
-
hmdne -
Matt Lawrence -
Mohit Sindhwani