CSV data import/export via a stored procedure?

Question

Post reply

CSV data import/export via a stored procedure?

stacya-671173

Old Hand

Points: 327
More actions
June 12, 2008 at 9:36 am

#96039

I need to be able to import a CSV file into a table via a stored procedure. It would be nice if I could pass the file name into the stored procedure as well as a parameter. Is XML my best option? For various reasons, I do not want to use DTS or Bulk. I will also need to know how to export a csv file from a stored procedure as well. I am using SQL 2000. Any suggestions would be greatly appreciated.
Stacy

Viewing 11 posts - 1 through 10 (of 10 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Michael Earl-395764

SSC Guru

Points: 53873

June 12, 2008 at 11:36 am

#828019

OpenQuery will allow you to do this.

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

Michael Earl (6/12/2008)
OpenQuery will allow you to do this.

Do you have example code of how to use OpenQuery to export to a CSV File?

--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)

bledu SSChampion Points: 12335 More actions · Answer 3

I have used xml before for importing into a table, but as to whether its suitable or not really depends on your particular circumstances which you have not mentioned.

Please put more info about your problem to get better help

Everything you can imagine is real.

stacya-671173 Old Hand Points: 327 More actions · Answer 4

I'm looking for something that is very portable. I have a csv file that needs to be uploaded to a sql table, then later after much manipulation, be exported from a sql table to a new csv file. It would be preferred if I could pass the file name into the stored procedure as a parameter. I realize there are other options for importing/exporting, but I would like to be able to do it within a stored procedure.

I'm not sure what other circumstances you are looking for?? Let me know if I have not answered your question, so that you can answer mine?

Thank you,

Stacy

stacya-671173 Old Hand Points: 327 More actions · Answer 5

OpenQuery will do import or export? or both? Do you have an example? I've looked it up, and unfortunately, am still confused.

Thank you,

Stacy

noeld SSC Guru Points: 96590 More actions · Answer 6

stacya (6/16/2008)
OpenQuery will do import or export? or both? Do you have an example? I've looked it up, and unfortunately, am still confused.
Thank you,
Stacy

Openquery just does IMPORT.

* Noel

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

Jeff Moden (6/12/2008)
Michael Earl (6/12/2008)
OpenQuery will allow you to do this.
Do you have example code of how to use OpenQuery to export to a CSV File?

Jeff, Michael might have intent for OPENROWSET. I have got some code like:SELECT *

INTO db1.dbo.table1

FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',

'SELECT * FROM [sheet1$]')

I guess (right now unable to test as right now I am not on SQL server machine 🙁 ) it will support CSV as well.

Mahesh

MH-09-AM-8694

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 8

[font="Verdana"]

stacya (6/12/2008)
I need to be able to import a CSV file into a table via a stored procedure. It would be nice if I could pass the file name into the stored procedure as well as a parameter. Is XML my best option? For various reasons, I do not want to use DTS or Bulk. I will also need to know how to export a csv file from a stored procedure as well. I am using SQL 2000. Any suggestions would be greatly appreciated.
Stacy

why don't you try Bulk insert in such case? Like:

BULK INSERT [orders]

FROM ' '

Mahesh

[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

OpenRowSet will not create a new file. It can be used to populate an existing file. Same goes with a text based linked server.

Bulk Insert will certainly do an Import but it will not export.

There is no "portable" way to do this amongst the RDBMS's that you mention. You need to wrap the functionality for each database type in a common interface and that's about as close as you're going to get.

The only way to create a new file from a stored proc in SQL server is to call a DTS job, make a trip to xp_CmdShell, or use SP_OA* methods... most of these methods require SA privs.

Of course, you could always run a batch job with calls to either BCP or OSQL... I believe OSQL is known as SQLCmd in 2005 (someone correct me, please).

Or, you can write an app or use something like "Hibernate"...

--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)

phoenix_iv Newbie Points: 3 More actions · Answer 10

Don't know if its too late or not but here is an article that shows how to export data to csv from within a sql script.

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell