Read Excel File in GUI and write back to a File

Question

Short Summary:

Open Excel file in GUI with one row and it's records, analyze it and write back to a file.

I am working on a project that has lots of records in Excel file. The data has web URL that I have to analyze and write appropriate comments about it.

Copying and pasting can be hectic as there are hundreds and hundreds of records.

So, I am thinking to automate the process.

What I'd like to do is have a GUI that would populate one record at a time in the GUI. Open the URL in IE. It will have some extra fields (drop down, input box) in addition to its original columns so I can record the analysis data.

Based on the drop down option, it will create a document (or append if it already exists) that record. Once clicked save, it will populate the next record.

What would be a best way to go? I thought of using Visual Basic because of its GUI, but everyone knows about VB and why I should avoid it.

I'm also thinking about web app, so it will not be OS dependent, but I am not sure how Excel files work with PHP, other web scripting languages.

Any input would be greatly appreciated. If you know any tutorial that can give some insight, will also help.

You might want to consider saving the Excel file as a CSV (comma separated values) file; that will be much easier to parse in your automated solution. — Matt, Jul 09 '15 at 16:47

score 0 · Answer 1 · edited May 23 '17 at 12:06

I would do this with some javascript on an html page. Here's an outline of a general strategy with a few examples, none have been tested. There's probably better ways to do some of this stuff.

Step 1 - Convert spreadsheet to CSV

File->Save As->Save as Type->CSV

Step 2 - An HTML page to act as a viewer for the URLs

Example:

<html>
<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
    <script src="myAutomationScript.js"></script>
</head>
<body>
    <table id="csvRecord">
        <tr id="header"></tr>
        <tr id="data"></tr>         
    </table>
    <input id="nextRecord" type=submit />
    <iframe id="viewer"></iframe>
</body>
</html>

Step 3 - Parse CSV

There's a jQuery plugin from this answer that will parse the CSV for you. You can read in the file (described here), or if this is a one-off thing you could just copy and paste the csv data into a string variable in your javascript file.

var csv = "",//populate this from file, or paste in data or whatever
records = $.csv.toObjects(csv),
keys = Object.keys(records[0]),
currentRecord = 0;

Step 4 - Display record in form

function displayHeaders (keys) {
    //create a header cell for each key
    //add a header (or headers) for your additional fields
}
function displayRecord (record) {
    //populate a td cell for each piece of data
    //add input element(s) for your additional review fields
    //set the source of the iframe to the url
    $('#viewer').setAttr('src',record.url);
}

Step 5 - Save the record and move on to the next

//this would be a handler for the click event of nextRecord
function moveOn() {
    //read in data from the csvRecord table
    //add the record to another collection, or read in the output file and add a line
    //save the collection, or string or whatever to a local file using the html5 local file api
    //++ currentRecord, and display that
    currentRecord = currentRecord + 1;
    displayRecord(records[currentRecord]);
}

Read Excel File in GUI and write back to a File

1 Answers1