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

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
SQL .NETAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQL .NETAuthor Commented:
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
Russell FoxDatabase DeveloperCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.