[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

···

______________________________________________
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 -- Info | ruby-talk@ml.ruby-lang.org - ml.ruby-lang.org

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: GitHub - rubycdp/ferrum: Headless Chrome Ruby API

Other than that, there are a couple of Sharepoint gems for Ruby (most do it via a REST API as I see): search | RubyGems.org | your community gem host - 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: GitHub - roo-rb/roo: Roo provides an interface to spreadsheets of several sorts.

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/

···

On 12/13/22 03:13, Matt Lawrence wrote:

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 -- Info | ruby-talk@ml.ruby-lang.org - ml.ruby-lang.org

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: GitHub - rubycdp/ferrum: Headless Chrome Ruby API

Other than that, there are a couple of Sharepoint gems for Ruby (most do it via a REST API as I see): search | RubyGems.org | your community gem host - 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: GitHub - roo-rb/roo: Roo provides an interface to spreadsheets of several sorts.

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 -- Info | ruby-talk@ml.ruby-lang.org - ml.ruby-lang.org

______________________________________________
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 -- Info | ruby-talk@ml.ruby-lang.org - ml.ruby-lang.org

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: JRuby on Windows: Day 3 - Using Apache POI (Java) to create 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 :slight_smile: If you explore that side, see: Ruby 3.1 on Windows: Day 0 - install and Hello World

Good luck!

Cheers,
Mohit.

···

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: GitHub - roo-rb/roo: Roo provides an interface to spreadsheets of several sorts.

______________________________________________
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 -- Info | ruby-talk@ml.ruby-lang.org - ml.ruby-lang.org