Import Text File To SQL Server

This blogs describes how flat files or text files can be imported to SQL Server with defining field information in a format file (xml file). Let’s see how ?

Download Sample Text File

So You can download the text file from the given link or if you have your own flat/text file with you then copy file and put it into the folder. For me its “D:\dbsimplified”

Now You must be knowing fields(FieldName) available in your text file. Since my flat/text file contact 8 fields so I have to put description of each field in xml file (The Format File). You can look at the content of xml file

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
 <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="EmployeeId" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="2" NAME="Blank_Column" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="3" NAME="EmployeeName" xsi:type="SQLNVARCHAR"/>
 <COLUMN SOURCE="4" NAME="Company" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Email" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="City" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="OtherInfo" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="Level" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Every fields is defined with datatype, length and terminator , Terminator can vary, in your case it may to Tab separated or Pipe(|) separated or etc. In my case its Tab separated so I mentioned( \t ).

You must copy your format definition into a file and save it as .xml extension. In my case I have saved it as D:\Format.xml

Now use following OpenRowSet Command to Fetch Data From Flat/Text file to SQL Server

Syntax " :

SELECT * FROM  OPENROWSET(BULK  'Flat/Text File Name and Path'  ,FormatFile='Format File Path and Name') as Tab

Let’s run the query

SELECT * FROM OPENROWSET(BULK 'D:\dbsimplfied\UploadData.txt' ,FormatFile='D:\FORMAT.xml') as Tab

 

Output :