SQL Server Bulk Import - BCP HOW TO

A lot of people using the free MS SQL Server 2005 Express hit a brick wall when they try to import data into the created database. Here is a tutorial, with video demo included on how to use the command-line BCP tool to import data into MS SQL Server 2005 Express.

During an analysis i conducted in the past days, I also found out the hard way that MS SQL Server 2005 Express does not have a GUI based Data Transformation Services. The only thing it does have is a BCP command-line tool.

So, here is a step-by-step tutorial how to use the BCP tool and not give up on an otherwise good (and free) product:

  1. The data - I am importing data collected by tcpdump. I stored the data into a CSV file (data.csv), a text file with a comma delimiter.
  2. Here is a sample row 16,10.176.1.105,NULL,10.176.1.254,NULL,NULL,64,17.12.2007,19:20:52,520,PING Req,NULL
  3. Creating the database - Log-in with the command-line sql tool (sqlcmd) and use the following set of commands to create the database and table for storing of imported data:
  • sqlcmd -S ATLAS\SQLEXPRESS
  • create database data_analysis
  • go
  • use data_analysis
  • go
  • create table data_import (
  • [No_packet] [int] NULL ,
  • [Src_Logical] [varchar] (255) ,
  • [Src_Port] [varchar] (255) ,
  • [Dest_Logical] [varchar] (255) ,
  • [Dest_Port] [varchar] (255) ,
  • [Flags] [varchar] (255) ,
  • [Packet_Size] [int] NULL ,
  • [Packet_Date] [varchar] (255) ,
  • [Absolute_Time] [varchar] (255),
  • [Additional] [varchar] (255) ,
  • [Protocol] [varchar] (255) ,
  • [newdata] [varchar] (255)
  • )
  • go
Content verification - To verify the contents of the created table, use the following set of commands
  • use data_analysis
  • select count(*) from data_import
  • go
  • quit
Data import - To import the data, use the following command
  • bcp data_analysis.dbo.data_import in data.csv -T -C1250 -c -t, -S ATLAS\SQLEXPRESS
Detailed explanation
  • bcp - the executable file name
  • data_analysis.dbo.data_import - name of database, owner and name of table to receive the data
  • in - the same command is used for export and import. in means importing, out means exporting
  • data.csv - file name that contains data to be imported, or to receive exported data when using the out direction
  • -T - swich indicating trusted connection. When using this switch, the bcp command uses the kerberos ticket of the logged-on Windows user to authenticate. If you don't use -T, you'll have to use -U and -P (user name/password)
  • -C1250 - collation. I found out that BCP does not work well with Unicode files, so i am forcing the 1250 collation (central European) - works with most characters
  • -c - treat everything as characters. This way it will be very easy to import any information.
  • -t, - delimiter. Default delimiter for BCP is tab, so i need to inform it of my delimiter character (comma)
  • -S ATLAS\SQLEXPRESS - server. This switch is followed by the hostname\instance name (for MS SQL Server Express its SQLEXPRESS)
Content verification - To verify the contents of the created table, use the following set of commands
  • use data_analysis
  • select count(*) from data_import
  • go
  • quit
Here is a video clip of the entire process



Related posts

Personal Data Protection - Anonymizing John Doe

Talkback and comments are most welcome

No comments:

Designed by Posicionamiento Web