how to store comma separated values in notepad into table

Question

Post reply

how to store comma separated values in notepad into table

victoria vilasiny

Valued Member

Points: 69
More actions
September 25, 2007 at 1:43 am

#177419

Hello all ,say i have a note pad where the fields are name and email ids respectively
something like this
amar,amar@yahoo.com,birju,birju@yahoo.com,samay,samay@gmail.com and so on.
Now what i need is i have to store the names and emails in respective fields ,
like all name should store under name field and all emails must store in email field .
how can i do this somebody tell me the solution.
Thankyou

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

It seems that your column and row separators are ,. in this case you have to use a script components.

My Blog: http://dineshasanka.spaces.live.com/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

And, how do you do THAT? :Whistling:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

/* to make this work, you will need a number table. If you don't know about them, see http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/ Also I'm assuming you have SQL Server 2000 - if it is 2005, then you can use Varchar(MAX) */

DECLARE @notepad VARCHAR(8000)

SELECT @notepad

='amar,amar@yahoo.com,birju,birju@yahoo.com,samay,samay@gmail.com'

DECLARE @split TABLE (MyID INT IDENTITY(1,1),string VARCHAR(255))

INSERT INTO @split(string)

SELECT LEFT(,CHARINDEX(',',email+',')-1)FROM

(

SELECT =SUBSTRING(@notepad,number,8000)

FROM numbers

WHERE number <LEN(@notepad)

AND SUBSTRING(','+@notepad,number,1)=',' )f

/*this assumes that the name will be shorter than the email

but you can easily change it to use the MyID and MOD */

SELECT [name]=CONVERT(CHAR(30),MIN(string)),

=MAX(string) FROM @Split

GROUP BY (MyID-1) /2

/*-------------------------------------------

(6 row(s) affected)

name email

------------------------------ ---------------

amar amar@yahoo.com

birju birju@yahoo.com

samay samay@gmail.com

*/

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Victoria,

The fastest way to do this is to build a BCP format file and then use Bulk Insert to import the data into a table. Of course, all that information is in Books Online, but the format file should look something like this... it needs to be a file that SQL Server can "see" (usually stored in the same directory as the data file(s))

8.0

2

1 SQLCHAR 0 100 "," 1 Name ""

2 SQLCHAR 0 250 "," 2 EmailAddress ""

Again, check out "bcp utility, format files" for what all the above means.

Your import table would look something like the following...

CREATE TABLE yourtable (Name VARCHAR(100), EmailAddress VARCHAR(250))

... and the command to pull the data from the file into the table would look something like this...

BULK INSERT 'Your_Table_Name'

FROM 'Data_File_Path'

WITH (

DATAFILETYPE = 'CHAR',

FORMATFILE = 'Format_File_Path',

TABLOCK

)

Of course, you'll need to substitue the correct table name and file paths.

Again, the important part is to check all this stuff out in Books Online, but the above should get you started.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)