Ever wish you could pull data from a documents like spreadsheets, word documents, csv files, XML files, or all ov the above? And import that data for use in an application or program? It might be in the wrong format or you just can’t get it to talk to your application.
You may take the time to just type the date into your application, bur do you value your time and sanity? If you or your staff have to take this additional date entry step on a daily basis, you are certain to be balding from pulling your hair out. Another problem with this method is typing. In order to ensure accuracy, the date entry would need to be checked by another person.
Copy and Paste may help out with some of these hangups, but the entire process will still be tedious and error prone with any regularity or volume of data.
There are many different tools and options available to help ease the burden with manual or otherwise impossible tasks.
The following is a case study for a B2B client.
Document Parsing – Spreadsheet
The client is a reseller of hotel room inventory. Many of the clients vendors have API’s or real-time methods for checking prices and availability for hotel rooms. One major client uses a spreadsheet for the year which contains all of the pricing information for the year.
There were two issues with the spreadsheet which prevented it from being used by the client’s web application:
- The base price varied by ranges of dates in each block in red.
- Some inventory had an additional charge for additional guests.
This format made it impossible to list prices on the clients web application. We needed to have one price, per date, per number of occupancy.
Python is a general purpose, high level programming language. We used this to develop a custom program to read and parse the spreadsheet data into a format that the web application could use. The program read each of the date ranges per block and attached the corresponding price. It also attached a day of the week indicator, as this was a requirement of the web application. We add a row for each number of occupancy and adjust the price if required.
The original source document contained 465 rows. The importable document contains 527,508 rows. Before we Bautis IT, if the client wanted to sell the inventory from this vendor,then someone would have had to create this lengthy spreadsheet by hand. Now the data is in the proper format for import into the web application and the client can sell the important vendors inventory.