bcp a text file with double quotes to a table — another sample

 ---Generate xml format file
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [testcs].[dbo].[Table_1] format nul   -f "C:\temp\myformatfile.xml" -x -c -t\^",\^" -r \^"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
  
 --Modify the format file myFormatFile.xml  
--By adding one row in the Record section by copy Row ID=1 and change the ID to 0


----bcp in with all data
exec master..xp_cmdshell  'bcp [testcs].[dbo].[Table_1] IN "C:\temp\mytest.txt" -f "C:\temp\myformatfile.xml" -T  -S'+ @@servername;


 
--Another sample table and file
create table bcpTest
(
CASH1 varchar(10),
CASH2 varchar(10),
CASH3 varchar(10),
CASH4 varchar(10)
)
--file:
"-28,750.00"^"-28,750.00"^"-1,000,000"^"5.75"

 -- Generate fmt format file 
 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f E:\DATA\myFormatFiletest.fmt -t\"\^\" -r\"\n -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd
  
 --Modify the format file myFormatFiletest.fmt 
--By adding one row   by copy Row 1 and modify it and change the first column to 1 through 5
--change column count from 4 to 5

12.0
5
1       SQLCHAR             0       10      "\""    0     ""          SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       10      "\"^\""    1     CASH1          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       10      "\"^\""    2     CASH2          SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       10      "\"^\""    3     CASH3          SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       10      "\"\r\n"   4     CASH4          SQL_Latin1_General_CP1_CI_AS

 

--Import code

--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt'
WITH
(
FORMATFILE ='E:\DATA\myFormatFiletest.fmt' 
 
)
select * from bcpTest



--XML format file

 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f "E:\DATA\myformatfile.xml" -x -t\^"^^\^" -r \^"\n   -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd

--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt' WITH
(FORMATFILE ='E:\DATA\myformatfile.xml' )
select * from bcpTest



---Modified XML file  (add one row and modify it.  (check the -t flag  -t\^"^^\^"  ). The ^ has special meaning so double up to escape it as delimiter.



 
   
  
  
  
  
 
 
  
  
  
  
 



 
Advertisements

Optional Parameters in Stored Procedure: how to make it perform well

Kimberly Tripp: Building High Performance Stored Procedures

http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/


Powershell With UPDATE


if (-not(Get-Module -name 'SQLPS')) {
  if (Get-Module -ListAvailable | Where-Object {$_.Name -eq 'SQLPS' }) {
    Push-Location # The SQLPS module load changes location to the Provider, so save the current location
   Import-Module -Name 'SQLPS' -DisableNameChecking  
   }
  }

$val1="abc"
$val2="xyz"
 
$svrname = "MC\MSSQL2014"

Invoke-Sqlcmd -Query "update testcs.dbo.mytest Set col2='$val2' WHERE col1 like '$val1'+'%' " -ServerInstance $svrname  



 

Special Characters in an Int column from Text File

Char Dec Hex Octal HTML Function / Description / Notes

^I 9 0x09 0011 ^I HT horizonal tab [\t]
^J 10 0x0a 0012 ^J LF line feed [\n]
^M 13 0x0d 0015 ^M CR carriage return [\r]
^Z 26 0x1a 0032 ^Z SUB substitute

SELECT Cast(Replace(Replace(Replace(Replace(ANumber,char(9),”),char(10),”) ,char(13),”) ,char(26),”) as INT) ANumber

http://www.idautomation.com/product-support/ascii-chart-char-set.html
—There is a handy function with nested REPLACEs to remove all no printable characters:
http://www.sqlservercentral.com/Forums/Topic860321-338-3.aspx