Text Migration to SQL Server

In this article I will discuss some of the method used to transfer data from text file to sql server!

Part 1: Extract the data

First create the destination table structure in the SQL Server. This will be handy when generating mapping file

Bulk Copy Program bcp.exe

This will generate the mapping file as xml shown below

bcp.exe [dbmame].[dbo].[table]
format nul -f format.xml -x
-S .\sqlexpress -T -c

The generated format.xml mapping  file is shown below

...

 

  
  
 
 

  
  
 

...

T-SQL OPENROWSET (BULK ..) to read text file into SQL Server

• data.txt : data file
• error.txt : place-holder name for moving erroneous rows
• format.xml : mapping file discussed above

SELECT   *  FROM  OPENROWSET(BULK 'E:\..\data.txt',
ERRORFILE = 'E:\..\error.txt',
MAXERRORS = 100,
FORMATFILE = 'E:\..\format.xml' ) AS T

Unfortunately OPENROWSET does not accept parameters. So the only way is to create a dynamic sql string and execute using T-SQL sp_executesql  say for example when looping through folder of files

INSERT INTO dbo.Table..( Id, ...)
exec sp_executesql  @query

delemeting single qoute is tricky one

set @query  = 'SELECT  '+@recnumber+' AS recnumber,  *  FROM  OPENROWSET(BULK '   
   + '''' + @path +@fileName + ''''
      +', FORMATFILE = ' + ''''+ @formatfile + ''''    
      +' ) AS T' 

To read all the files in a particular folder use the extended stored procedure master.sys.xp_dirtree

CREATE TABLE #files (
    [filename] NVARCHAR(255),
    depth INT,
    [isfile] BIT
)

insert into #files exec
master.sys.xp_dirtree  @path,0,1

select * from #files

use this simple construct to loop through all files  using While

While ( (Select Count(*) From #files) >  0 )
Begin
    select Top 1 @fileName = [filename] From #files
    --your exec here..
    delete from #files where [filename]=@fileName

End

Part 2: Transform the data

sample data which need to be grouped and concatenated

RecNo Property Data
71594 URN 1317604
71594 MED Sertraline | ? | Daily
71594 SAV CALLAWAY, kelly | 08/08/2016 | 11:05
71594 SAV CALLAWAY, kelly | 08/08/2016 | 11:07
71594 SAV SINANAJ, jasmina | 08/08/2016 | 13:44

T-SQL for XML comes to rescue FOR XML PATH('')

 SELECT        RecNo, Property, Count(*) AS [Num],
 STUFF ((
                SELECT ', [' + Data +'] ' FROM  dbo.TABLE1 AS I
    WHERE I.RecNo=O.RecNo AND I.Property=O.Property
    FOR XML PATH('')
   ),1,1,'') Csv
 FROM Mig.MIGRN_TRIAGE AS O
 GROUP BY RecNo, Property

Now the result table looks like this

RecNo Property Data
71594 URN [1317604]
71594 MED [Sertraline | ? | Daily]
71594 SAV [CALLAWAY, kelly | 08/08/2016 | 11:05],[CALLAWAY, kelly | 08/08/2016 | 11:07]
,[SINANAJ, jasmina | 08/08/2016 | 13:44]

Use PIVOT to transform row data to columnar data

PIVOT_TABLE AS(

 select P.* from   (select RecNo, Property, Csv from dbo.TABLE1 )  as T
 PIVOT(
  Max( Csv)
  for [Property] in ([KID],[PRO],[MED],[SAV] )
  ) AS P
)

Part 3: Load the Data

Use the OLE DB drivers to load data into another SQL Server/Oracle ...

--EXEC sp_configure 'show advanced options', 1;
--RECONFIGURE;

--EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
--RECONFIGURE;

SELECT TOP 10  *  FROM
OPENROWSET('SQLNCLI'
,'Server=.\sqlexpress2016;Trusted_Connection=yes;database=dbname'
,'select * from [dbo].[table1]') AS A

SELECT TOP 10 *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=.\sqlexpress; Integrated Security=SSPI'
).[dbmame].[dbo].[table1]