This is a useful method to insert data from an Excel spreadsheet into an existing SQL database table, using the bulk insert statement. This can be a good way to get around issues with data type conversion when going from data within an Excel spreadsheet to the various datatypes within the SQL table.
The following example was used to import data into tbl_billing_rates, below is the SQL statement used to insert the data
--Example of a bulk insert SQL statement
bulk insert CallScripter48.dbo.tbl_billing_rates
fieldterminator = ',',
rowterminator = '\n',
firstrow = 2
The first step is to edit the formatting for all columns in the Excel spreadsheet to the text format, then you can type any datetime fields into the required format. For this example 2016-01-20 16:00:00.000. Any values that are blank would be inserted into the database table as NULL, please check that the column will allow NULL values to be inserted.
Save the spreadsheet as a CSV file, this creates a comma separated list that can be inserted into the database. View the CSV file using Notepad to ensure the data is displaying correctly, as below.
The following parameters are defined in the SQL query
- fieldterminator - Identifies which delimiter is separating each piece of data to be inserted. In this example a CSV file is used, that separates each piece of data by a comma so ',' is the field terminator. Other delimiters such as tab and colon can be used so this value would change depending on the delimiter used.
- rowterminator - Identifies how each row of data is separated. In this example each row is separated by a line break so '\n' is the rowterminator
- firstrow - Identifies when the first row of data begins. In this example the first row is a list of column names, this information should not be inserted into the database table so firstrow is set to 2