Split a string into multiple fields

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Split a string into multiple fields

Author

Topic

sbothobotho
Starting Member

2 Posts

Posted - 2007-04-10 : 04:35:23

Hello

Can someone help me out here, please...
I want to split a string from a notepad txt file into multiple fields and Import it to a table in SQL Server 2000.

example
these are the strings: 001258745659 12457 556987
Field1: 001
Field2: 25
Filed3: 874
Filed4: 5659

Field5: 124
Field6 57
.
.
.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 04:38:54

import into a staging table with single column and use left(), substring() to split

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-10 : 09:49:10

...or use DTS and import it as fixed-width columns.
That is some crappy data you are working with. Can't you get the provider to send it comma-delimited or something?

e4 d5 xd5 Nf6

sbothobotho
Starting Member

2 Posts

Posted - 2007-04-12 : 07:17:23

Hello

Thanks for the response, I'm new to databases and i've been going trhough to your reply but couldn't understand what to do. I'll try to simplify what i want to do.
The data,its an export from another system, and thats the only format i can get it.

The whole idea here is that I'm trying to spatial refference that information but before i could do that, I need to import it into a database.

What the first string actually means is:

001258745659
the 001 = Department Name
The 25 = branch Name
the 874 = unit Name
.
.
.

now, I've created this table structure on my database:

Dep_id, dept_name, branch_name, unit_name, ...

the dep_id is an auto number, for dept_name I want to insert: 001, for branch name: 25, for unit name:874, etc.

So, is it possible to do something like this???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 07:23:33

Yes, with DTS it is.
Import flat file (text) and set up column widths yourself. Do not use delimiters.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources