How to convert from STRS Application Repository database in SharePoint to web page
Usage
The CSV2HTML script is used to create a consistent table showing the STRS application repository contents in a SharePoint database and a web page. The data displayed is the publicly searchable portion of the metadata.
Note: The CSV2HTML procedure/script is helpful to manipulate CSV files to/from the STRS Application Repository SharePoint database. Mr. Handler experimented with importing and exporting to determine the difficulties for making an external web page consistent with a SharePoint database for the STRS Application Repository. Both have difficulties at different stages. The difficulty in importing metadata from a web page into an SharePoint database is updating the original web page before converting it to the correct CSV format. The difficulty in exporting a database from SharePoint is converting the CSV file to a web page. If Excel is used to convert from CSV to a web page, the exported web page will try to maintain Excel’s look and feel using Active X and displaying an unnecessary tab at the bottom. Mr. Handler found a Python script, CSV2HTML, that he largely rewrote to work.
Setup
- Install Python. See Python download site. The script was tested against Python 2.7 and Python 3.2. Since there were residual errors when handling special characters in Python 2.7, it is recommended that the user install and use Python 3.2 or above.
- Create/find a working directory to store scripts and execution results for upload and download.
- Save the CSV2HTML.python.txt script (from SharePoint) to that working directory.
Convert
- Export from the STRS application repository public metadata SharePoint database into a comma separated value (CSV) file. May need to download the Excel version into the download directory first before doing a SaveAs, changing the file type to CSV and changing the location to the appropriate directory.
- Copy the original CSV file to the STRS web site as “STRSApplicationRepositoryPublicMetadata.csv”. In WordPress, this appears in the Media Library.
- Execute the CSV2HTML.contents.python.txt script to create a web page with scrollable contents as described below and include it in the contents web page. The Contents page is editable in WordPress by an admin. The usage is:
usage: CSV2HTML.contents.python.txt [-h|--help] fileIn.csv [fileOut.html] Convert Comma Separated Value (CSV) file to HTML web page. positional arguments: fileIn.csv CSV file to be converted. fileOut.html HTML web page file to be created. STRSApplicationRepositoryList.date.html is used if none is specified. Optional. If none is specified, use STRSApplicationRepositoryContents.<date>.html optional arguments: -h, --help show this help message and exit
- It is easier to cd to the download directory
- In a PC command window, execute python script CSV2HTML.contents.python.txt with input being the file just saved and the output being a new file; for example:
cd C:\path\SoftwareDefinedRadio\STRS Repository
C:\python27\python CSV2HTML.contents.python.txt STRSApplicationRepositoryLMHdlwoHTMLa.csv test.html - In a Linux command window, execute python script CSV2HTML.contents.python.txt with input being the file just saved and the output being a new file; for example:
cd $HOME/strs/20120207
python CSV2HTML.contents.python.txt STRSApplicationRepositoryLMHdlwoHTMLa.csv test.html
or
python27 CSV2HTML.contents.python.txt STRSApplicationRepositoryLMHdlwoHTMLa.csv test.html - Note that the CSV2HTML procedure/script has only been tested using “Python 2.7.3” and “Python 3.2.3”. If you have an older version, it may not work. To determine which version of python you are using, type python (or equivalent) on a command line. It will return the version in an interactive mode. To exit, type “exit()” or “quit()” or Ctrl-Z plus Return.
- Note that if you install python, python must be started so that it can find the corresponding python-related files. On Linux, for example, the PATH variable needed to be changed and the easiest way to do this was to create a Bourne shell script. For example, to execute python in $HOME/python-2.7.3, I created $HOME/bin/python27 (where $HOME/bin is already in the PATH variable) containing:
#!/bin/sh # python27 ... PATH=$HOME/Python-2.7.3:$PATH python $*
Then I made $HOME/bin/python27 executable (chmod +x $HOME/bin/python27) and executed the following, after reinitializing the command window shell:
python27 CSV2HTML.contents.python.txt input.csv output.html
which worked fine.
- Check the resulting HTML file using a browser to verify that it matches the original SharePointdatabase.
- Copy the table in the resulting HTML file (delimited by “<table” and “</table>”) to the STRS web site as . In WordPress, this table appears in the snippet named ContentsScroll.