Using fmt File to import text file with double quotes text qualifiers through bcp


--          truncate table [dbo].[myFiles]

 
--Text Qualified with double quotes in text file
--How to to import them into a SQL Table with bcp
 
--Step 1:
--Create a format file as below
 
DECLARE @cmd1 VARCHAR(4000) 
set @cmd1 = 'BCP  [test1].[dbo].[myFiles] format nul   -f "E:\X\data\myFormatFile.fmt" -c -t\, -T -S'+ @@servername
exec master..xp_cmdshell @cmd1
  
 --Step 2: 
 --Modify myFormatFile2.fmt to myFormatFile22.fmt
 --2.1---Add one row before row one and lable it as row 0 in the position column
 --2.2 Modify the first column with the added row and add one to the total number of rows on the second line in the format file
 
 --The format generated


/*
12.0
 
14
1       SQLCHAR             0       500    "\""         0     x                              SQL_Latin1_General_CP1_CI_AS
 
2       SQLCHAR             0       500    "\",\""      1     accountnumber                  SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       500    "\",\""      2     firstname                      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       500    "\",\""      3     lastname                       SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       500    "\",\""      4     street1                        SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       500    "\",\""      5     street2                        SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       500    "\",\""      6     city                           SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR             0       500    "\",\""      7     state                          SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR             0       500    "\",\""      8     postalcode                     SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR             0       500   "\",\""       9     zip4                           SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR             0       500    "\",\""      10    joindate                       SQL_Latin1_General_CP1_CI_AS
12      SQLCHAR             0       500    "\",\""      11    email                          SQL_Latin1_General_CP1_CI_AS
13      SQLCHAR             0       500    "\",\""      12    accountexpire                  SQL_Latin1_General_CP1_CI_AS
14      SQLCHAR             0       500    "\"\r\n"     13    businessname                   SQL_Latin1_General_CP1_CI_AS
*/
 
 
 
---Step 3 import the text file with the format file
 
DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].myFiles IN "E:\X\data\myFiles.csv" -f "E:\X\data\myFormatFileModified.fmt" -T -t\, -F 2 -S'+ @@servername
exec master..xp_cmdshell @cmd2
 
 
 /*
 12.0
13
1       SQLCHAR             0       500    ","      1     accountnumber                  SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       500    ","      2     firstname                      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       500    ","      3     lastname                       SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       500    ","      4     street1                        SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       500    ","      5     street2                        SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       500    ","      6     city                           SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       500    ","      7     state                          SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR             0       500    ","      8     postalcode                     SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR             0       500    ","      9     zip4                           SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR             0       500    ","      10    joindate                       SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR             0       500    ","      11    email                          SQL_Latin1_General_CP1_CI_AS
12      SQLCHAR             0       500    ","      12    accountexpire                  SQL_Latin1_General_CP1_CI_AS
13      SQLCHAR             0       500    "\r\n"   13    businessname                   SQL_Latin1_General_CP1_CI_AS


 */

 --the format file modified

/*
12.0

14
1       SQLCHAR             0       500    "\""         0     x                              SQL_Latin1_General_CP1_CI_AS

2       SQLCHAR             0       500    "\",\""      1     accountnumber                  SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       500    "\",\""      2     firstname                      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       500    "\",\""      3     lastname                       SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       500    "\",\""      4     street1                        SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       500    "\",\""      5     street2                        SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       500    "\",\""      6     city                           SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR             0       500    "\",\""      7     state                          SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR             0       500    "\",\""      8     postalcode                     SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR             0       500   "\",\""       9     zip4                           SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR             0       500    "\",\""      10    joindate                       SQL_Latin1_General_CP1_CI_AS
12      SQLCHAR             0       500    "\",\""      11    email                          SQL_Latin1_General_CP1_CI_AS
13      SQLCHAR             0       500    "\",\""      12    accountexpire                  SQL_Latin1_General_CP1_CI_AS
14      SQLCHAR             0       500    "\"\r\n"     13    businessname                   SQL_Latin1_General_CP1_CI_AS
*/



---Step 3 import the text file with the format file

DECLARE @cmd2 NVARCHAR(4000) 
set @cmd2 = 'BCP [test1].[dbo].myFiles IN "E:\X\data\myFiles.csv" -f "E:\X\data\myFormatFileModified.fmt" -T -t\, -F 2 -S'+ @@servername
exec master..xp_cmdshell @cmd2


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s