none
import csv file using stored procedure RRS feed

  • Question

  • I have CSV file that is formatted as such:

    id, name
    1,john smith,
    2, michelle smith,
    3, dave johnson, 
    4,jim stewart

    Is it possible to pass the CSV file to a stored procedure and have the procedure parse the file and insert the values into the correct columns in the table?  Yes an SSIS package will be easier, however, this procedure could be called from multiple applications, and an SSIS package isn't practical for this scenario. The csv file format will always have the same columns names

    Monday, January 13, 2020 5:19 PM

All replies

  • One option is to use OPENROWSET to import your text file.
    Monday, January 13, 2020 5:22 PM
    Moderator
  • You can using OPENROWSET.  However, I would recommend using an SSIS package. You can execute the SSIS package using a stored proc.  

    https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

    Monday, January 13, 2020 5:28 PM
    Moderator
  • After just speaking with some folks, an SSIS package it totally out of the question. It has to be done either in code or a stored procedure, no SSIS packages (at this time)
    Monday, January 13, 2020 5:34 PM
  • Hi SharePointGuy123,

    What is your SQL Server version?

    Monday, January 13, 2020 5:44 PM
  • Hi SharePointGuy123,

    What is your SQL Server version?

    2016 SP 2.

    I just tried to run the bulk insert and I get "you do not have permission to use the bulk load statement.'

    Monday, January 13, 2020 5:48 PM
  • Hi SharePointGuy123,

    Check it out.

    SQL Server 2017:

    USE tempdb;
    GO
    
    DROP TABLE IF EXISTS dbo.tbl;
    
    CREATE TABLE dbo.tbl (ID VARCHAR(20), PersonName VARCHAR(30));
    
    BULK INSERT dbo.tbl
    FROM 'e:\Temp\SharePointGuy123.csv'
    WITH (FORMAT='CSV' -- starting from SQL Server 2017 onwards
       , FIRSTROW = 2
       , FIELDTERMINATOR = ','
       , ROWTERMINATOR = '\n');
    
    SELECT * FROM dbo.tbl;


    SQL Server 2016:

    USE tempdb;
    GO
    
    DROP TABLE IF EXISTS dbo.tbl;
    
    CREATE TABLE dbo.tbl (ID VARCHAR(20), PersonName VARCHAR(30));
    
    -- SQL Server 2016
    BULK INSERT dbo.tbl
    FROM 'e:\Temp\SharePointGuy123.csv'
    WITH (
       DATAFILETYPE = 'char' -- { 'char' | 'native' | 'widechar' | 'widenative' } 
       , FIELDTERMINATOR = ','
       , ROWTERMINATOR = '\n'
       , FIRSTROW = 2 
       --, LASTROW = 3 -- Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.
       , CODEPAGE = '65001');
    
    SELECT * FROM dbo.tbl;

    Monday, January 13, 2020 5:55 PM
  • After just speaking with some folks, an SSIS package it totally out of the question. It has to be done either in code or a stored procedure, no SSIS packages (at this time)

    Can you use Powershell - or some other language?  If the requirement is to use SQL Server - or just provide some way to load the file?


    Jeff Williams

    Monday, January 13, 2020 10:46 PM
  • Hi , 

    Here are some ways .

    1.The quickest way to get your Excel file into SQL is by using the import wizard: Import data from Excel to SQL Server or Azure SQL Database

    2. Use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server

    BULK INSERT table_temp FROM 'C:\yourpath\file2.csv'
     WITH (
         FORMAT='CSV'
     )

    3.Import and export bulk data using bcp (SQL Server)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 14, 2020 2:50 AM
  •  

    Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 15, 2020 9:23 AM