Import text file into SQL Server using OpenRowSet

Import text file into SQL Server  using OpenRowSet

This example imports text file into SQL-Server 2012 using OpenRowSet command.

Step 1) Create a Datatable (corresponding to columns in text file)

CREATE TABLE [dbo].[players](
    [RK] [varchar](50) NULL,
    [PLAYER] [varchar](50) NULL,
    [Salary (US$)] [varchar](50) NULL
) ON [PRIMARY]

  

Step 2) Create a format file Specific to Text file

  It is using sql server edition 11.0(i.e Sql server 2012)

it has 3 columns

each column can accept 0 to 50 chars

each column is tab separated \t


new row is separated by new line char \r\n

Players.fmt

11.0
3
1       SQLCHAR             0       50      "\t"   1     RK                           Latin1_General_CI_AI
2       SQLCHAR             0       50      "\t"   2     PLAYER                       Latin1_General_CI_AI
3       SQLCHAR             0       50      "\r\n"   3     Salary                Latin1_General_CI_AI
 

Step 3) Test OpenRowSet command

select document.* from openrowset(BULK N'C:\players.txt',formatfile=N'c:\player.fmt' ,firstrow=2) as document

Step 4) Insert into Players Datatable

insert into [dbo].[players]
select document.* from openrowset(BULK N'C:\players.txt',formatfile=N'c:\players.fmt',firstrow=2) as document

Players.txt

RK    PLAYER    Salary
1    Alex Rodriguez    30,000,000
2    CC Sabathia    24,285,714
3    Mark Teixeira    23,125,000
4    Ichiro Suzuki    18,000,000
5    Derek Jeter    15,729,365
6    Mariano Rivera    14,940,025
7    Robinson Cano    14,000,000

Tags: Import text file into sql server using OpenRowSet, Import text file using format file, Import text file using datafile,Import text file using format and datafile,Bulk Import text file using OpenRowSet, Open Row Set.