Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I occasionally get one-off requests to modify some data, and the request comes with some values in CSV. Is there a way I can treat arbitrary CSV as a table or row source quickly and easily, even as inline data?

EG, can I do something like:

SELECT * FROM SomeSpecialCommand('
147074,13
153987,16
147075,3
176405,16
176437,25
176436,14
176406,14
176439,13
176407,13
') as MyInlineCSV

What prompted this was a request to modify inventory levels for ~100 items, and I was given a CSV with a SKU number and quantity (like above).

share|improve this question
    
I'm almost positive this is possible, though I can't explain how to do it. I've seen similar questions/answers over at stackoverflow.com. They've got more people working with down and dirty SQL stuff. –  Ryan Bolger Nov 25 '09 at 0:50

3 Answers 3

up vote 1 down vote accepted

You would want to make use of something called a user-defined table-valued function. Basically, you'll create a function that takes a single nvarchar(max) parameter and creates a table that is returned from the CSV input. There are literally dozens of approaches you could take here (using a numbers table, using an XML type, using loops, etc., etc.), but this has been thoroughly documented and tested by some smart Sql guys, so I'll just point you there instead. Be sure to read through the comments section as well, as there are alternative solutions and test results for different data sizes, environments, etc. - just pick the one that works best for you.

share|improve this answer

Unfortunately, you can't do this. You could create a linked server that points to an excel file. You could use this as your 'utility' linked server and save random files as the proper .xls file and you'll be able to select from the sheet.

share|improve this answer

Does it have to be using sql-server?

There's DBD::CSV for perl which would solve simple modifications that don't require pulling data from other tables already in your database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.