Database Simplified Headline Animator

Database Simplified

Tuesday, 29 May 2012

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 :

image