create format file without delimiter

• bkshn

  Right there with Babe

  Points: 794

  More actions

  February 16, 2015 at 1:35 am

  #398021

  Hello

  I have a date file with no delimiter like bellow

  0080970393102312072981103378000004329392643958

  0080970393102312072981103378000004329392643958

  I just know 5 first number in a line is for example "ID of bank"

  or 6th and 7th number in a line is for example "ID of employee"

  Could you help me how can I create a XML format file?

  thanks alot

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  February 16, 2015 at 7:32 am

  #1777829

  bkshn (2/16/2015)

  ---

  Hello

  I have a date file with no delimiter like bellow

  0080970393102312072981103378000004329392643958

  0080970393102312072981103378000004329392643958

  I just know 5 first number in a line is for example "ID of bank"

  or 6th and 7th number in a line is for example "ID of employee"

  Could you help me how can I create a XML format file?

  thanks alot

  Since your data is fixed width just use substring to get each data element. Then you can do whatever you want with it.

  For example.

  declare @Something varchar(100) = '0080970393102312072981103378000004329392643958'

  select LEFT(@Something, 5) as BankID

  , SUBSTRING(@Something, 6, 2) as EmpID

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 16, 2015 at 7:55 am

  #1777837

  Check the following page from BOL: https://msdn.microsoft.com/en-us/library/ms187833.aspx

  In the Sample XML Format Files section (bottom of the page):

  F. Importing fixed-length or fixed-width fields

  The following example describes fixed fields of 10 or 6 characters each. The format file represents these field lengths/widths as LENGTH="10" and LENGTH="6", respectively. Every row of the data files ends with a carriage return-line feed combination, {CR}{LF}, which the format file represents as TERMINATOR="\r".

  <?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="10"/>

  <FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>

  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r"

  </RECORD>

  <ROW>

  <COLUMN SOURCE="1" NAME="C1" xsi:type="SQLINT" />

  <COLUMN SOURCE="2" NAME="C2" xsi:type="SQLINT" />

  </ROW>

  </BCPFORMAT>

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2