[Webinar] Learn how to a build a cloud-first strategyRegister Now


splitting records of column into multiple column from dat or txt file.

Posted on 2014-08-25
Medium Priority
Last Modified: 2014-08-27
I want to import some columns of the file and i want to split one of that column into multiple column. I am using bcp utility and format files. I just cant find the way to modify the format file to split the column into multiple column. s.jpg
Question by:SQL .NET
  • 2
LVL 14

Accepted Solution

Russell Fox earned 2000 total points
ID: 40283718
You need to specify nothing/blank as the field terminator in the format file:
1 SQLCHAR 0 3 "" 1 FirstThree
2 SQLCHAR 0 11 "" 2 NextEleven
3 SQLCHAR 0 2 "" 3 NextTwo
4 SQLCHAR 0 4 "" 4 NextFour
5 SQLCHAR 0 165 "\r\n" 5 RemainingToEOL

Open in new window


Author Comment

ID: 40287171
I just want to confirm that if i am right. Here is how will do it

1. Create a format file for the destination table(i have both fmt and xml)
> basically table has columns like this
Col1      col2         col3      col4   col5  col6   col7   col8   col9  col10
  ID     Number     n1        n2      n3    n4      n5     n6      n7    n8

2. Now as you can see i want the data from specific column . So  for the first row i want the last four digits of column8 i.e 2771(022771) then 191 then split 4 3 1 4 3 3 [each number will be in different column] so

  ID     Number     n1        n2      n3    n4      n5     n6      
2771      191           4         3          1    4          3       3

3. change the format files according to requirement.

So the format file will look like
3. change the format files according to requirement.

So the format file will look like

<?xml version="1.0"?>
<BCPFORMAT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"><RECORD>

<FIELD MAX_LENGTH="12" TERMINATOR="\t" xsi:type="CharTerm" ID="1"/>
<FIELD MAX_LENGTH="30" TERMINATOR="\t" xsi:type="CharTerm" ID="2"/>
<FIELD MAX_LENGTH="30" TERMINATOR="\t" xsi:type="CharTerm" ID="3"/>
<FIELD MAX_LENGTH="30" TERMINATOR="\t" xsi:type="CharTerm" ID="4"/>
<FIELD MAX_LENGTH="30" TERMINATOR="\t" xsi:type="CharTerm" ID="5"/>
<FIELD MAX_LENGTH="30" TERMINATOR="\t" xsi:type="CharTerm" ID="6"/>
<FIELD MAX_LENGTH="30" TERMINATOR="\t" xsi:type="CharTerm" ID="7"/>

<COLUMN xsi:type="SQLFLT8" NAME="ans5" SOURCE="6"/>
<COLUMN xsi:type="SQLFLT8" NAME="ans6" SOURCE="7"/>
<COLUMN xsi:type="SQLFLT8" NAME="ans7" SOURCE="8"/>
<COLUMN xsi:type="SQLFLT8" NAME="ans8" SOURCE="9"/>
<COLUMN xsi:type="SQLFLT8" NAME="ans9" SOURCE="10"/>
<COLUMN xsi:type="SQLFLT8" NAME="ans10" SOURCE="11"/>
<COLUMN xsi:type="SQLFLT8" NAME="ans11" SOURCE="12"/>


1       SQLCHAR             0       12      "\t"     0     ID          ""
2       SQLCHAR             0       30      "\t"     0     Number            ""
3       SQLCHAR             0       30      "\t"     0     N1             ""
4       SQLCHAR             0       30      "\t"     0     N2               ""
5       SQLCHAR             0       30      "\t"     0     N3               ""
6       SQLCHAR             0       30      "\t"     0     N4               ""
7       SQLCHAR             0       30      "\t"     0     N5               ""
8       SQLCHAR             0       30      "\t"     8     N6               ""

Am i missing something here
LVL 14

Expert Comment

by:Russell Fox
ID: 40288284
I think you're looking for something more like below: 8 columns returned but starting with the 8th column. The first two fields, col 8 & 9, end with tabs, but the rest have no terminator, you just know the lengths should be 4, 3, 1, 4, 3, & 3. The tricky part is taking just the last four characters in the first column, despite it being a variable number of characters between the tabs.

If this were my assignment, my solution would be to simply import everything between the tabs into their own columns into an interim table, and then use a view or select statement to parse those columns out as necessary. You can just truncate the table before each bulk insert.
1       SQLCHAR             0       6      "\t"     8     ID          ""
2       SQLCHAR             0       3      "\t"     9     Number      ""
3       SQLCHAR             0       4      ""     10     N1           ""
4       SQLCHAR             0       3      ""     11     N2           ""
5       SQLCHAR             0       1      ""     12     N3           ""
6       SQLCHAR             0       4      ""     13     N4           ""
7       SQLCHAR             0       3      ""     14     N5           ""
8       SQLCHAR             0       3      "\r\n" 15     N6           ""

Open in new window


Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question