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.
| Author |
Topic |
|
eddie
Starting Member
45 Posts |
 Posted - 2002-01-04 : 10:03:14
|
| I have a csv file and I want to know if it is possible to use a stored procedure to grab the values from the file and put them into a table?? IF so, can I have the user enter a parameter which will be the path to the file?Thanks,Eddie |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-04 : 10:10:00
|
| In order to query a text file, you would need to set it up as a linked server (see sp_addlinkedserver in BOL). While setting up linked servers to different files on demand is probably possible, it is going to prove rather challenging. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-04 : 14:34:23
|
| bcp and a little dynamic SQL should work too:CREATE PROCEDURE LoadFile @filename varchar(128) ASDECLARE @cmd varchar(8000)SELECT @cmd='bcp "database.owner.tablename" in "' + @filename + '" -c -Uusername -Ppassword -Sservername'EXEC xp_cmdshell @cmdCall the SP like this:EXEC LoadFile 'C:\myfile.csv'Change the table, server, user and password in the LoadFile procedure accordingly. I haven't tested this so the syntax might need some tweaking, but Books Online has the details on bcp and xp_cmdshell. |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2002-01-04 : 15:27:03
|
| Use bulk insert. Bulk insert uses bcp but its an sql command rather than an operating system comand requiring xp_cmdshell. You'll need to make a format file if the text file and the table don't match up though. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-04 : 15:36:14
|
 CREATE PROCEDURE LoadFile @filename varchar(128) ASDECLARE @cmd varchar(8000)SELECT @cmd='BULK INSERT database.owner.tablename FROM ''' + @filename + ''' WITH (FIELDTERMINATOR='','', ROWTERMINATOR=''\n'')'EXEC (@cmd)The calling syntax is the same as before.Books Online has more details on the BULK INSERT options, and also describes the bcp format file, if you need it. |
|
|
|
eddie
Starting Member
45 Posts |
Posted - 2002-01-08 : 11:18:53
|
| Thanks..that works great. Now for curiosity sake, can I go the other way? Can I take values in a table and write them out to a csv file?Thanks,Eddie |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-08 : 13:08:52
|
Yes, BCP will copy data OUT as well as IN, depending on the options you set. Read all about it in BOL... --------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
using stored procedure to pull values from file?