How to read from a CSV file in a sProc?
-
What is the simplest way in sProc to convert a CSV file into a table?
-
if csv file is correct, use bulk insert should be simplest.
-
But how do I do a "bulk insert" in a stored procedure?
-
create table outside stored procedure.
create stored procedure with filename as parameter.
Here is sample:
--prepare csv data file, in dos command, assume local pubs database.
bcp pubs.dbo.authors out authors.csv -S(local) -T -c -t,
--create table in query analyzer
use pubs
go
select * into copyauthors from authors where 1=2
--create stored procedure in query analyzer
create proc copyauthors_sp @filename varchar(100)
as
set nocount on
declare @sqlst varchar(4000)
set @sqlst = 'bulk insert copyauthors from ''' + @filename + '''' +
'with (datafiletype = ''char'', fieldterminator = '','')'
exec(@sqlst)
go
--call the stored procedue for data import
exec copyauthors_sp 'c:\temp\authors.csv'
--check result
select * from copyauthors
--clean up
drop table copyauthors
drop proc copyauthors_sp
-
Thank you. Appreciate your help.
-
If you prefer GUI's (and I do) AND the file to import is not one HUGE S.O.B. (because bcp is probably a smidgeon faster) then the easy thing to do is to go to your Enterprise Manager console, trace down to Data Transformation Services and make a new local Package. DTS is user friendly and well documented. If I can use it, a third grader can.
Studdy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply