Creating a MS Excel Spreadsheet Template for use with RPG2SQL Integrator

From Support

Listed below are some general steps to follow in MS Excel when designing a new spreadsheet that will be populated or read by the RPG2SQL Integrator.

1.) Decide what type of data you need to capture ?

Detailed data records - Usually where detailed information is required. Multiple data rows will be added to the spreadsheet.

Fixed or changeable column headings - These values can be hard coded in the spreadsheet template and formatted as desired with Excel prior to using the template. They can then be updated via the RPG2SQL Integrator code.

Ex: Setting dates or month names at the top of each column of data and shifting the values depending on which month is current.

Individual numeric or character data values - These would be field values that you want to fill in when generating data into the spreadsheet. These field could be column headings, a part number, customer name, rep name, etc.....

2.) Create the new spreadsheet file and add as many sheets to it as required.

Ex: You may want to create a new spreadsheet template with worksheets for each month.

Also add any static fields you need to create or any formulas that you would like to use to summarize data later when data is added to the spreadsheet by the RPG2SQL Integrator.

• • Note: Always make sure the first row of the spreadsheet template is left blank. Start placing data in row 2 instead, unless you want to use row one for column names.

3.) For detailed record information, create a named range via the Excel Insert/Name/Define menu.

Call the named range something like: JulyDetail or DetailInfo.

This named range will later be used via the RPG2SQL Integrator to add or clear detail records within the spreadsheet. Think of a named range as a pre-defined detail area or single cell within a spreadsheet. A spreadsheet template can have multiple named ranges across multiple worksheets.