Clean Double Quoted Text to Another Table


  

   create table testtablewithOneColumn (col0 varchar(4000))
Insert into testtablewithOneColumn values('"Some data        ","3800&","827","         ","         ","       ","51345","R","S","          10.00006" ')
,('"         ","3800","827","         ","         ","       ","51345","R","S","          10.00008" ')
,('"row3         ","3800","827","         ","         ","       ","51345","R","S","          10.00009" ')

CREATE TABLE testtablewithTargetColumns (
SalaryCode VARCHAR(50)
,SalaryID VARCHAR(50)
,Department VARCHAR(50)
,Region VARCHAR(50)
,Area VARCHAR(50)
,theMisingCol VARCHAR(50)
,TypeCode VARCHAR(50)
,ShortCode VARCHAR(50)
,ManagerCode VARCHAR(50)
,Units VARCHAR(50)) 

DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = 'BCP [mydb1].[dbo].testtablewithOneColumn OUT  "C:\temp\myTableData.txt" -c -r \"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd1, no_output 
-- Export to a text file

BULK INSERT  mydb1.[dbo].testtablewithTargetColumns
  FROM 'C:\temp\myTableData.txt'
  WITH
     (
        FIELDTERMINATOR ='","',
        ROWTERMINATOR ='" "\n',
        FirstRow=1,
		DATAFILETYPE = 'char'
     );

---remove the first  double quote
Update  mydb1.[dbo].testtablewithTargetColumns
SET
SalaryCode=Replace(SalaryCode,'"', '')  

  --Check the target table
select SalaryCode
	 , SalaryID, Department, Region, Area, theMisingCol, TypeCode, ShortCode, ManagerCode, Units
 from  mydb1.[dbo].testtablewithTargetColumns

 --clean up
 drop table testtablewithOneColumn,testtablewithTargetColumns
https://social.msdn.microsoft.com/Forums/en-US/6ca6eb19-b73d-4289-8b5a-f2287c8d330f/remove-the-double-quotes?forum=transactsql

 
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