Get all values from one column of a CSV string using Stored Procedure

Question

Within a stored procedure, I need to take a whole CSV file as a string, then pick out all the values in one "column" to do a further query on the database.

I cannot use a saved doc - so i think that rules out openrowset, and the whole thing has to be done within a stored procedure.

Have spent hours googling and trying, but can find a good answer. One possible was http://www.tainyan.com/articles/entry-32/converting-csv-to-sql-data-table-with-stored-procedure.html but it doesnt work and i can find the error.

How should this be done please?

where is the csv file stored? is it in the file system or in database? — Joe G Joseph
– Joe G Joseph, Commented Aug 17, 2012 at 11:45
This is better done on the application side, not SQL Server (barring SSIS for data import). — Oded
– Oded, Commented Aug 17, 2012 at 11:45
Sounds like it forms part of a wider application. Have a look at Bulk inserting the CSV data into a separate table and going for there. In .Net this would be using SQLBulkCopy — Jon Egerton
– Jon Egerton, Commented Aug 17, 2012 at 11:46
Believe me, I would much rather being using C# / LINQ, but i need to do it on the database. The string comes into the server via a web service. I am using SQL Server 2005 — JJJCoder
– JJJCoder, Commented Aug 17, 2012 at 12:03

Tobsey · Accepted Answer · 2012-08-17 14:34:28Z

1

I don't really like this but it will work, provided your csv column remains at the same column index. I'd be wary of the performance of this but it might work.

See Fiddle here: http://sqlfiddle.com/#!3/336b7/1

Basically convert your csv file to xml, cast to an xml type, then perform queries on the xml.

answered Aug 17, 2012 at 14:34

Tobsey

3,40016 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JJJCoder Over a year ago

I have marked this as the answer, as that would work. In the end, i had to use this split function blog.logiclabz.com/sql-server/… .- Split the csv into rows, insert them as a text column into a temp table, loop through that table, split the info and load it into another temp table. It's pretty ugly (hence i haven't posted it), but it just about does the job.

Collectives™ on Stack Overflow

Get all values from one column of a CSV string using Stored Procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related