The BCP (bulk copy program) command line utility enables us to quickly import CSV files into a SQL Server database. The utility works very well when the import file matches the structure of the table, and the table does not have an identity column.
The moment the import file does not match the structure of the table with can run into different possible errors during the import. This is mostly due to the fact that the fields may not mapped correctly to the columns on the table.
To facilitate the control of how the CSV fields should be mapped to the fields on the table, we can create a view that matches the CSV field order. Let’s take a look at an example to illustrate the problem and a solution using SQL views.
The Table
This is a simple table that allows us to store blog entries. We should note the identity and default datetime columns which would not exist in a CSV file as these are system generated values.
CREATE TABLE dbo.ozkary_blog
(
id int identity,
title nvarchar(200) null,
url nvarchar(100) null,
tags nvarchar(100) null,
created datetime null,
imported datetime default(getdate()) not null
)
|
The CSV File
Our CSV file contains four fields which are fewer fields than columns on the table.
Created,title,url,tags
01/01/2015,Angular, www.ozkary.com/angular,angular,web
02/01/2016,ASP.NET, www.ozkary.com/aspnet,dotnet,web
03/01/2017,NODEJS, www.ozkary.com/nodejs,javascript,nodejs
|
We should note the following possible issues when trying to import this CSV file with our previously define table.
- The order of the columns on the table does not match the fields of the CSV file. The create date field is first on the file, but it is on position five on our table.
- Our table has an identity field which sure will not match the date field on position one of our CSV file.
- Our table also has an import date time field with a default date value.
Data Import Process
Let’s attempt to import the data directly into our table without any modifications. To import the file, we execute the following command:
bcp dataimport.dbo.ozkary_blog IN ".\\ozkary_blog.csv" -t, -c -F1 -S .\sqlexpress -T
|
When calling the BCP utility we use the following parameters:
Parameter Option
|
Description
|
Database and table name
|
This is the database and table name
|
CSV file name and location
|
This is the file location and name
|
-t,
|
Comma is the field delimiter
|
-c
|
Use char as the storage type for all the fields
|
-F2
|
Start at row 2 column - skip field headers
|
-S
|
Server name
|
-T
|
Use a trusted connection with the current session
|
In our first attempt to import the data, we are using the table name. This does not work because of fields mapping to the wrong columns, and ,as expected, we get some data type errors (add –e bcp.log parameter to create a log file and see more detail on the errors)
Solution – Using a View to Map the Fields
To address this problem, we can create a view that only uses the fields that are available on the import file and match their corresponding order.
CREATE VIEW dbo.vw_import_ozkary_blog
AS
SELECT created,title,url,tags
FROM dbo.ozkary_blog
|
We should now modify the command and use the view name instead of the table name. This should provide better results.
bcp dataimport.dbo.vw_import_ozkary_blog IN ".\\ozkary_blog.csv" -t, -c -F2 -S .\sqlexpress -T
|
By looking at the output on the image above, 3 rows got imported. If we query our table we should see the records with our identity and imported date values.
As shown, the approach of importing a CSV file using a database view provides a consistent and simple way to map and skip table columns during a BCP import.
Hope it helps.
