Programming Challenge 21 - Data preparation

Runs Until February 28, 2009

I use Excel extensively to prepare data for putting into databases or hard coded into applications. The tools I use do this but not everyone has them hence this programming challenge.

The Competition

Using any free or open source library that lets you read the contents xls files and write a command line or GUI application to read an xls file and output the contents as two text files xls.sql and xls.csv.

1. SQL Insert statements
2. CSV Data.

Download the xls spreadsheet
Note: The spreadsheet file will only have one sheet in it and the first row will have column names. I will be using a different spreadsheet with the same name (data.xls) to judge entries. It will have just one sheet and column names in the first row.

Example Answers

If the spreadsheet has 4 columns, say name, height, latitude and longitude, then the sql line for the row with name ='Bolton', height = 456, latitude = -4.0649, longitude = 53.8006 should be

insert into Table (name,height,latitude,longitude) values ('Bolton',456,-4.0649, 53.8006)

With csv data, the first row should be the column names followed by each row after e.g.

name, height, latitude,longitude
'Bolton',456,-4.0649, 53.8006

Results

There were two entries, both in C#, from Richard Mofet of South Africa who won and MJ Carmelotes who came 2nd. I judged them primarily on ease of use and the winner used a GUI with file dialogs etc.

Interestingly the winner used Microsoft Jet to read the file while the other entry used the Microsoft Office COM server for Excel. Thanks to both entrants.

1. Richard Mofet (South Africa) - C#
2. MJ Carmelotes(Philipines) - C#

More Programming Challenges

• Link to Rock, Scissors and Paper Bot Ongoing Programming Challenge - Runs weekly.
• Link to Programming Challenge 2 - Count the Islands. (Finished)
• Link to Programming Challenge 3 - Manage Elevators. (Finished)
• Link to Programming Challenge 4 - Guess the Mastermind Code - (Finished)
• Link to Programming Challenge 5 - Squeeze words into a crossword grid - (Finished)
• Link to Programming Challenge 6 - Guess the Mastermind Code - (Finished)
• Link to Programming Challenge 7 - Validate Chess Positions - (Finished)
• Link to Programming Challenge 8 - Score Poker Hands - (Finished)
• Link to Programming Challenge 9 - Manage a Data structure - (Finished)
• Link to Programming Challenge 10 - Hide Text (Finished)
• Link to Programming Challenge 11 - Match Sets of Cards (Finished)
• Link to Programming Challenge 12 - Calculate Shortest Path - Completed)
• Link to Programming Challenge 13 - Play Battleships (Finished)
• Link to Programming Challenge 14 - Multiply Large Numbers(Completed)
• Link to Programming Challenge 15 - Generate a Dungeon (Completed)
• Link to Programming Challenge 16 - Solve 1,000 Sliding Puzzles ( Completed)
• Link to Programming Challenge 17 - Implement Life (Completed)
• Link to Programming Challenge 19 - Complete a Puzzle (Completed)
• Link to Programming Challenge 20 - Implement Sieve of Erathostenes (Completed)