Import: Insert Excel data into a database

SQL*XL home

Your Excel data can be imported easially into your database. To do a data import use the multiple rows insert dialog. There are only a few prerequisites:

• Format the data as a table. No empty columns or empty rows are allowed. Empty cells (null values) are allowed.
• You can only load data into 1 table at a time
• Use the column names as they are in the database as heading above the data table. For example:

  	A	B	C
  1	employee_nr	employee_name	salary
  2	1000	Linker G	5000
  3	1001	Davis R	4000
  4	1002	Jones P	6000

To insert the data, display the Insert Multiple Rows dialog (SQL*XL|Insert|Multiple Rows...). This dialog essentially takes 3 items as input.

• Table Name:
  Type the table name you wish to insert the data into.
  For example:

  employees
  

• Column Names:
  Either type the column names (comma separated) or use the button to use the mouse to select the range containing the column names. SQL*XL will build the comma separated column names list for you. For example:

  employee_nr , employee_name , salary
  

• The Data Range:
  Use your mouse to click on the spreadsheet to select the range of data cells. Just select it as a single block and you will see that SQL*XL writes the cell addresses in the input field. For example:

  A1:C4

Now just press the OK button and all the data will be sent to the database. It's as easy as this! Progress information will be shown in Excel's statusbar as % completed. Every now and then SQL*XL will refresh the screen so you can see the progress for yourself. At the end of each row in your spreadsheet SQL*XL will put a status indicator: successful or a short error message.

Tip:

• Data errors may occur. Most databases have rules to accept data. There are constraints that specify which data is acceptable. If SQL*XL finds a problem it will put an error message next to the row. After the insert process you can easially review the problem rows. You have to fix the data errors and resubmit the failed rows only. SQL*XL makes it easy for you to sort all failed records to the top of the spreadsheet.
• I advice to leave the commit each row setting on. Some databases implement read consistency and therefore keep changes of one user separate to other users. This only works well if you don't do too many changes. If you bulk load a lot of data you may find your database complaining with all the data inserted so far being rolled back.

See also:

• Multiple Rows Insert dialog
• SQL*XL Features
• SQL*XL Advanced Features
• Dialogs
• Menu and Toolbar Items
• SQL*XL Home