Introduction

Sheet2SQL is a small APP that transforms Sheet files to SQL-Insert statements. It's ideal for creating a reusable testset for testing DB2-applications.

Just create your TestData in a easy to use spreadsheet (Sheet) and let my little tool loose on it.

A file will be created with the name of the the database.

Important

The tool needs to bring the Sheet to the foreground in order to perform its stuff. It makes use of the inbuilt clipboard function of the epoc device so it must have exclusive use of it during the processing. No cutting and pasting in other apps while the SQL statements are being made! The tool will fail!

Sheet Layout!

Just click on the Sheet-object to get a good look at a template Sheet.

The A-column is used to define Data-Lines. If a cell in the A-column is left empty the rest of that row will be seen as comment and will be left alone during processing.

>TABLE

• Name of Database-Table.

• Number of entries: 1

• Mandatory

>PROLOGUE

• Here you can add whatever you want. The entry must be started in the B-Column. These prologue entries will be processed and put in the outputfile. I mostly use it for a delete statement as to clear table entries before I add them

• Number of entries: n

• Optional

>NUMCOLS

• Number of columns in the table

• Mandatory

>COLNAME

• The names of the columns in the table. Start definition in the B-Column of the sheet and go left for every new table-column. Make sure that numcols equal the number of names defined.

• Number of entries: 0 - 25

• Optional, but really not advisable to leave out. This is where the tool was written for.

>TYPE

• The type of the entry in the table. If numeric the entry will not get quotes in the insert statement and vice versa. So don't worry if you have a timestamp or something.... just make it type C and it will get quotes.

• Values allowed:
  - N(umeric) = default
  - C(har)

>FORMULA

• This one I added later. By default this tool reads the real value of a cell. This can be cumbersome if you make full use of the power of the spreadsheet. Normally if you use formulas to quickly create data for your table this tool would read the formula and not the result of the formula. If you define the column as Y than all entries in that column will be processed so as to get the result of the formula and not the formula itself!
  Note that the processing of formulas is a bit slower so if it's not necessary than don't use it.

• Values allowed:
  - Y(es)
  - N(o) = default
  - Empty (default value will be taken)

• Optional

>VALUE

• The real values of the table-fields! These values will be processed and per row an insert statement will be created!

• Number of entries: n

• Optional, but really not advisable to leave out. This is where the tool was written for.

>END

• Defines the end of Processable data. The tool will stop processing and the open(ed) sheet document will be closed.

• Optional. If not defined the tool will try 24 more rows after the first empty cell in the A-column and then quit. The sheet file will stay open!

Notes

• The first row of the sheet will not be read by my proggy! You can use it as a comment line!

• Between Data-lines it is possible to add a maximum of 25 comment lines. If more are added the tool will quit.

• Empty cells in the >Value-rows will by default get a NULL value
  

Tools

Elf proef

Checks if dutch bank account numbers are correct.

Julian date

Returns a julian date (day of year) froom a given date.

History

v2.01

• added Tool - Elf proef

• added Tool - Julian date

v2.00

• Epoc look and feel. Toolbar and stuff.

• Option for creating an empty processable sheet

• some tuning

v1.01

• fixed some bugs

• added documentation

• wishlist

v1.00

• first release as app

Wishes

(Wishes can be mailed to: ivo.psion@hetnet.nl)

• Multiple tables in one sheet

• Errorhandling does not handle all at this time

• create sheet enhancements

• Processing of an inactive sheet file