Banner

 

Converting a spreadsheet to INSERT statements

It is not uncommon to get data in the form of an spreadsheet, typically Excel. This exercise illustrates how to create INSERT statements for loading these data into a relational database.

  1. Download the spreadsheet containing the shr table in Excel format.
  2. The goal is to create an INSERT statement for each data row of the spreadsheet. For the first data row of the example spreadsheet, the required SQL is
    insert into shr values ('FC','Freedonia Copper',27.5,10529,1.84,16);
  3. It is possible to construct the INSERT with a single concatenate function, but that is an error prone approach. It is easier to create each of the pieces of the INSERT, and then put them together.
  4. Create the entry for shrcode, a character string, by entering in cell G2 the formula
    =CONCATENATE("'",A2,"',")
    which should result in 'FC', (the single quotes have been highlighted so it is easier to see them within the double quotes
  5. Repeat the process of step 4 to generate 'Freedonia Copper', in H2.
  6. Create the entry for shrprice, a numeric, by entering in I2 the formula
    =CONCATENATE(C2,",")
    which should result in 27.5,
  7. Repeat the process of step 6 to generate 10529, in J2.
  8. Repeat the process of step 6 to generate 1.84, in K2.
  9. Repeat the process of step 6 to generate 16 in L2.
  10. Now put all the pieces together into a single INSERT, with
    =CONCATENATE("insert into shr values (",G2,H2,I2,J2,K2,L2,");") in M2.
  11. Use copy down to generate the remaining INSERT statements.
  12. Create a table and verify that the INSERT statements work.

Exercise

Create INSERT statements for the spreadsheet containing the nation table.

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 02-Dec-2022