none
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

    Monday, May 28, 2012 6:28 AM

Answers

  • 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/

    Tuesday, May 29, 2012 1:19 AM
  • 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


    • Edited by Eshani Rao Tuesday, May 29, 2012 1:29 AM
    • Marked as answer by pscorca Tuesday, May 29, 2012 7:09 AM
    Tuesday, May 29, 2012 1:28 AM

All replies

  • 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/

    Tuesday, May 29, 2012 1:19 AM
  • 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


    • Edited by Eshani Rao Tuesday, May 29, 2012 1:29 AM
    • Marked as answer by pscorca Tuesday, May 29, 2012 7:09 AM
    Tuesday, May 29, 2012 1:28 AM
  • Ok, thanks, but with FIELDTERMINATOR = ';'

    Bye

    Tuesday, May 29, 2012 7:10 AM
  • Nice one, Eshani!

    Ryan Shuell

    Monday, June 25, 2012 11:02 PM