import csv using bulk import problem

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

import csv using bulk import problem

Author

Topic

jallah_88
Starting Member

12 Posts

Posted - 2011-02-15 : 09:40:32

Hi all,

I am trying to make a stored procedure to:
-> import a csv file into a table
-> select whatever i need from the table
-> export to a csv file

This all works great but i need to be able to make the path for the import file and export file into variables. Bulk import can't work with variables and the "openrowset" is not a solution due to work security policies. Is there a way to make this possible?

Kind regards, Martin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-15 : 09:44:20

You need to use dynamic sql and bulk import

Madhivanan

Failing to plan is Planning to fail

jallah_88
Starting Member

12 Posts

Posted - 2011-02-16 : 05:00:32

Hi,

Thanks for the reply. I have been looking into this dynamic sql, but it troubles me a lot. Is it necessary to put it into a stored procedure? Because when i do so, it says Msg 217, Level 16, State 1. Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-16 : 05:10:59

post the query that you used and the complete error message

KH
[spoiler]Time is always against us[/spoiler]

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-16 : 05:17:16

this may useful

DROP PROCEDURE sp_Emp_Import
GO
CREATE PROCEDURE sp_Emp_Import
@TableName varchar(200),
@PathFileName varchar(200)
AS
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT ' + @TableName + ' FROM ''' + @PathFileName+ ''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
EXEC (@SQL)

EXEC sp_Emp_Import 'emp' , '\\aaa\aa\Table1.txt'

--Ranjit

jallah_88
Starting Member

12 Posts

Posted - 2011-02-16 : 06:20:02

I think i found another way to resolve this, using bcp. Here's the code

-- Creating permanent table to put csv file data into
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(
N'importcsv') AND type = (N'U'))
DROP TABLE importcsv

CREATE TABLE importcsv
(
action varchar(10),
src varchar(80),
dst varchar(80),
service varchar(80),
fwrule int,
xlatesrc varchar(40)
)
--extract csv data from from file
DECLARE @cmd varchar (8000)
DECLARE @input varchar (500)

SELECT @cmd = 'bcp Monitor..importcsv IN "' + @input + '" -t, -c -T -S' +@@ServerName

EXEC master..xp_cmdshell @cmd

--Put the sql query into output file
DECLARE @output varchar(8000)
DECLARE @temp1 VARCHAR(8000),

@tablename1 VARCHAR(5000),
@path varchar(1000)

SET @path = @output
SET @tablename1 = @path + 'opg1.4_'+'.csv'
SET @temp1 = 'bcp "SELECT fwrule, COUNT(fwrule) AS NumberOfTimes FROM Monitor..importcsv GROUP BY fwrule HAVING (COUNT(fwrule)>1) ORDER BY NumberOfTimes" queryout "'+ @tablename1 + '" -t, -c -T -S' + @@Servername

EXEC master..xp_cmdshell @temp1

My problem is that I want to make an input through a cmd file which looks like this:
sqlcmd -E -v @input = "drive:\path\test.csv" @output = "drive:\path\" -i drive:\path\testing.sql

But when i run this, it doesnt make the file. I should say that if I specify the @input and @output variable in the sql query it works.
Hope you can help me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources