Creating columns from a comma delimited row

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

General SQL Server Forums

New to SQL Server Programming

Creating columns from a comma delimited row

Author

Topic

Mike_007
Starting Member

8 Posts

Posted - 2007-08-20 : 09:40:10

Hi All

This is my first official post...very exciting:)

OK....

I need to split a string, based on comma delimetrs, into columns of a table.

I am using SQL Server 2005.
The plan is to use webmethods (Integration Software) to receive a flat file, loop through the records in that flat file and during each iteration of the loop call a SQL stored procedure that will split the record, based on comma delimetrs, into columns of a table.

A typical record will look like this:
AP05-07,ACTUAL,ZA01,......

I have looked at some of the past solutions to this type of post, but am battling to understand....

So if its possible, is there a simple way to create a stored procedure that will do this?

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:43:32

BCP
BULK INSERT
OPENROWSET

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-20 : 11:53:00

quote:
Originally posted by Peso

BCP
BULK INSERT
OPENROWSET

E 12°55'05.25"
N 56°04'39.16"

You didn't mention what version

DTS, SSIS (not that I recommend them)

What's the replacement for bcp called?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Mike_007
Starting Member

8 Posts

Posted - 2007-08-21 : 04:00:50

Thanks for the quick replies...

Ok so I am now playing with the bulk insert command...

I have created a file that contains the following text:

Michael,is,a,king

I have saved this file on the root of C.

I have created a table called Test with the following fields:

Michael
[Is]
A
King

The idea is to insert each field of the file into each of the columns above.

My Bulk Insert command looks like this:

BULK INSERT
ABC.dbo.Test
FROM 'c:\Bulk.txt'
WITH
(
CODEPAGE = 'ACP',
DATAFILETYPE = 'char',
ROWTERMINATOR = ','
)

And I get these errors:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Please excuse my ignorance, Im still bumbling my way around these type of operations..

Thanks for the help!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-21 : 04:13:41

Make sure you closed the file before running bulk insert

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources