How to use bcp to import this .rpt file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to use bcp to import this .rpt file

Author

Topic

halifaxdal
Starting Member

2 Posts

Posted - 2014-07-20 : 16:07:09

This is the first time I use bcp to do data import:
I got some data file in .rpt format, which was generated in SSMS when indicate the output is file (other than Text and Grid).
The .rpt file is essentially a fixed-width csv file: first row contains column names and the second row is "----------"
I created the xml format for the bulk insert and bcp:


<?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="CharFixed" LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="12"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="17"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="12"/>
  <FIELD ID="5" xsi:type="CharFixed" LENGTH="12"/>
  <FIELD ID="6" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="7" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="8" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="9" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="11" xsi:type="CharFixed" LENGTH="20"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r\n" LENGTH="2">
</RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="UserID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Category" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="3" NAME="DispositionCode" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="4" NAME="ProtocolID" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="5" NAME="UrlID" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="6" NAME="SourceServerIpInt" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="7" NAME="DestinationIpInt" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="8" NAME="SourceIpInt" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="9" NAME="TotalHits" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="10" NAME="TotalBytesSent" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="11" NAME="TotalBytesReceived" xsi:type="SQLBIGINT"/>
 </ROW>
</BCPFORMAT>

Here is the sample data:


user_id     category    disposition_code protocol_id url_id      source_server_ip_int destination_ip_int   source_ip_int        TotalHits            TotalBytesSent       TotalBytesReceived
----------- ----------- ---------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
39931       108         1026             11          39889786    168303256            1074010031           168216973            1                    66                   0
321267      153         1026             11          40874954    3232282836           1254940174           2887031922           1                    38                   0
300156      68          1026             11          17023059    3232282837           2689815587           2887031876           2                    104                  0
395200      68          1026             11          29001568    3232282806           3245204960           2886904753           1                    1899940              1631571
261118      9           1026             11          71217       168303255            1094154986           168224413            1                    160                  0
159762      2           1026             11          27234540    3232280219           3246572816           2887738013           2                    676                  0
232198      98          1026             11          35062870    168303259            2259292889           168178275            1                    268                  0

In the data file, the last column is 20 characters width but in many cases its value is 0 and uses only 1 byte (ASCII 30) and followed by 0D 0A which is a carriage return \r\n

Here is the script:


BULK INSERT Weblog.dbo.Weblog
FROM 'C:\Data\test.csv' 
WITH 
  (
    FORMATFILE = 'C:\Rxie\Weblog\Weblog.xml'
  );

Here is the error indicating something wrong with the .xml format file:


Msg 9436, Level 16, State 48, Line 1
XML parsing: line 16, character 9, end tag does not match start tag

It seems to me that the line containing \r\n in my format file is causing the </RECORD> not matched because no this error after I remove this line.

Can anyone tell me how to fix it.

halifaxdal
Starting Member

2 Posts

Posted - 2014-07-20 : 16:08:10

Thank you very much. Any other approaches are also welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources