none
Remove the double Quotes RRS feed

  • Question

  • Hi Experts,

    I have data loaded into sql server DB which is containing data in the below format into a single column

    "         ","3800","827","         ","         ","       ","51345","R","S","          10.00000"    

    Could any please assist with converting it into separate columns

    So it will be mapped into another table columns and will be loaded like below 

    SalaryCode  SalaryID   Department  Region       Area      TypeCode     ShortCode     ManagerCode    Units

                         3800        827                                            51345              R                       S                    10.00000

    Thanks & regards

    Priya

    Thursday, February 25, 2016 4:31 AM

Answers

All replies

  • Hi Priya,

    Use below code


      SELECT 
        PARSENAME(REPLACE(Colname,',','.'),1) 'SalaryCode'
    PARSENAME(REPLACE(Colname,',','.'),2) 'SalaryID' ,
    ......

     PARSENAME(REPLACE(Colname,',','.'),9) 'Units' ,
    FROM <<TABLE>> WITH (NOLOCK)

    Please mark as answer if my post is helped to solve your problem

    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao

    Thursday, February 25, 2016 6:04 AM
  • You need to use a string parsing UDF for that

    like

    SELECT RTRIM(LTRIM(REPLACE(f.Val,'"',''))) 
    FROM Table t
    CROSS APPLY dbo.ParseValues(t.[Column],'","')f

    UDF will look like this

    http://visakhm.blogspot.ae/2010/02/parsing-delimited-string.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 25, 2016 6:10 AM
  • If the data is from a flat file, next time when importing, using a flat file source setting Text qualifier " in SSIS,  then data would get splitted and clean after imported, you won't face such situation. Check here.

    Eric Zhang
    TechNet Community Support

    Thursday, February 25, 2016 7:05 AM
    Moderator
  • Your data were not imported properly and  you should find a right way to import them. You can fix the existing one column data through exporting them to a text file and use BULK INSERT them to your target table and make sure you have the right column list and matching order. I use a varchar type to load into a sample target table. Here is a sample for the whole process.

    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
    -- 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

    Thursday, February 25, 2016 4:42 PM
    Moderator
  • Map your columns and main table accordingly in the script below.
    create table #MyTable (col0 varchar(max))
    Insert into #MyTable values('"        ","3800","827","         ","         ","       ","51345","R","S","          10.0000" ')
    
    
    ;WITH CTE AS
    ( 
        SELECT
            
            
            CAST('<M>' + REPLACE(col0, ',' , '</M><M>') + '</M>' AS XML) 
            AS col
        FROM  #MyTable 
    )
    SELECT
        
         REPLACE((col.value('/M[1]', 'varchar(50)')),'"','') As [SalaryCode],
         REPLACE((col.value('/M[2]', 'varchar(50)')),'"','') As [SalaryID],
        REPLACE((col.value('/M[3]', 'varchar(50)')),'"','') As [Department],
    	   REPLACE((col.value('/M[4]', 'varchar(50)')),'"','') As [Region],
         REPLACE((col.value('/M[5]', 'varchar(50)')),'"','') As [Area],
        REPLACE((col.value('/M[6]', 'varchar(50)')),'"','') As [TypeCode],	
    	 REPLACE((col.value('/M[7]', 'varchar(50)')),'"','') As [ShortCode],
         REPLACE((col.value('/M[8]', 'varchar(50)')),'"','') As [ManagerCode],
        REPLACE((col.value('/M[9]', 'varchar(50)')),'"','') As [Units],
    	REPLACE((col.value('/M[10]', 'varchar(50)')),'"','') As [UnknownCol]
    into #t FROM CTE 
    GO
    select * from #t
    
    drop table #t
    drop table #MyTable 


    Aparna

    • Proposed as answer by appsqldev Thursday, February 25, 2016 5:20 PM
    Thursday, February 25, 2016 5:17 PM