Importing Spreadsheets (regardless of format)

This post continues my Mental Health Awareness Week project. Refer to the original post for background information about the goal of this project.

So now I have a very rudimentary dataset designed and ready to be populated with data.  I've also got a spreadsheet from the SAMHSA website, which contains all of the facility's meta-data.  There's just one small hitch for me personally: I don't have Excel, just Google Sheets.  I can upload it to Google Sheets, export it to a Tab Delimited file, and then import that file via DataPort.  

Later on down the track I'll receiving additional spreadsheets from different sources, so this work process actually kind of sucks.  I need a way to import a spreadsheet into Content Manager, but without needing to convert files before importation.  Also, who knows what format the files might be in.... Excel 2007, Excel 2013, OFD, CSV, etc.  

In my first post on this blog I created a custom import data formatter that automatically added a Byte Order Mark to Tab delimited files.  It was crazy easy to create that thing.  The same approach I took there can be taken here: build something within the DataPort framework that solves my problem.  

So I took 30 minutes and created a new spreadsheet data import formatter by using the OpenXml and Spreadsheet Light libraries.  This gives me a new option when using DataPort, as shown in the image below.

2017-09-14_11-44-55.png

After I've selected the newly available File Format of Spreadsheet and picked a file from my computer, DataPort shows me all of the available columns of data (this does require that the file have a header row with column names).  I can then match my columns just like I would for the out-of-the-box Tab Delimited Data Formatter.  Again, this file can be in any spreadsheet format!

2017-09-14_11-44-55.png

Once executed, DataPort will even show me exactly how many rows and columns were in the spreadsheet.  It then processes it just like it would for a tab delimited file.  The image below shows what the output looks like in DataPort.

2017-09-14_11-44-55.png

Yay!  I've not got some facility boxes in my CM instance.  :)

2017-09-14_11-44-55.png