Importing csv files using a stored procedure

Question

Hi,

as in SQL Server 2005/2008 Express Edition it isn't possible to save any import/export data pkgs, is it possible to create a stored procedure to import data from csv files and write the read data into a SQL table?

Thanks

Answer 1

hi,

yes, you can... you can, say, use one of the allowed importing feature like BULK INSERT,

INSERT SELECT FROM OPENROWSET, .... just a caveat about OPENROWSET, which requires a server setting to be enabled like Ad HOc Distributed Query, to be once set via sp_configure... then you can even use a format file...

-- d:\import\data.txt file content
1, Andrea, 1.234, 20120815
--/ d:\import\data.txt file content

-- d:\import\f.txt  format file content
11.0
4
1       SQLCHAR             0       12      ","     1     i        ""
2       SQLCHAR             0       10      ","     2     n        Latin1_General_CI_AS
3       SQLCHAR             0       41      ","     3     d        ""
4       SQLCHAR             0       24      "\r\n"   4     dt       ""
--/ d:\import\f.txt  format file content

-- SQL CODE
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
	i int, 
	n varchar(10),
	d decimal(18,4),
	dt datetime
	);
GO
GO
BULK INSERT dbo.t
	FROM 'D:\import\data.txt'
	WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');
GO
CREATE PROCEDURE usp_BK_table_t 
AS BEGIN
	BEGIN TRY
		DELETE dbo.t;

		BULK INSERT dbo.t
			FROM 'D:\import\data.txt'
			WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

		RETURN 0;
	END TRY

	BEGIN CATCH
		DECLARE @ErrorMSG varchar(2000);
		SET @ErrorMSG = ERROR_MESSAGE();
		RAISERROR (@ErrorMSG, 16, 1);
		RETURN -100;
	END CATCH
END;
GO
EXEC sp_configure 'show advanced options', 1;
reconfigure;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure;
GO
SELECT *
    FROM  OPENROWSET(BULK  'D:\import\data.txt',
    FORMATFILE='D:\import\f.txt'     
    ) as t1 ;
GO
SELECT * FROM dbo.t;
GO
EXEC dbo.usp_BK_table_t;
SELECT * FROM dbo.t;
GO
DROP PROCEDURE dbo.usp_BK_table_t;
GO
CREATE PROCEDURE usp_BK_table_t 
AS BEGIN
	BEGIN TRY
		DELETE dbo.t;

		INSERT INTO dbo.t
			SELECT *
				FROM  OPENROWSET(BULK  'D:\import\data.txt',
				FORMATFILE='D:\import\f.txt'     
				) as t1;
		RETURN 0;
	END TRY

	BEGIN CATCH
		DECLARE @ErrorMSG varchar(2000);
		SET @ErrorMSG = ERROR_MESSAGE();
		RAISERROR (@ErrorMSG, 16, 1);
		RETURN -100;
	END CATCH
END;
GO
EXEC dbo.usp_BK_table_t;
SELECT * FROM dbo.t;
GO
DROP PROCEDURE dbo.usp_BK_table_t;
DROP TABLE dbo.t;

regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

Answer 2

Try below.

bulk insert yourtable from 'C:\file.csv'

with ( FIELDTERMINATOR =',', FIRSTROW = 2 )

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 3

Ok, thanks, but with FIELDTERMINATOR = ';'

Bye

Answer 4

Nice one, Eshani!

---

Ryan Shuell