SQL BULK INSERT FROM CSV From a Folder

Question

I have this SQL command which will be used in a stored procedure and will be scheduled:

BULK INSERT Test101.dbo.Test102
FROM 'C:\Bulk\samp.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

It works well but what I want to do is to process all the .csv files from a folder (let's say Bulk folder) automatically. I mean, the user doesn't have to define the exact location with filename instead the stored procedure will process all the .CSV file from that folder and ignore the other files if there were.

Please help.

Thank you.

Wil, please tag stuff correctly. MSFT sql server is `sql-server`, keep `mysql` off of it or clean that tag up and out in the future. — Drew, Jul 26 '16 at 03:23
Years ago, but not at the moment. Sorry. Wait for the pros to show up :p — Drew, Jul 26 '16 at 03:27
@WilDech Dech A solution that I have in mind will need elevated rights and access xp_cmdshell or some other MSSQL system function ... let me know if that will work for you — objectNotFound, Jul 26 '16 at 03:44
@objectNotFound That will do. Actually I have this reference: http://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder I just don't know how to make that work for me. — Wil Dech, Jul 26 '16 at 03:55
@WilDech that was exactly what I had in mind ... what is the error that you are getting ? — objectNotFound, Jul 26 '16 at 04:06
@objectNotFound I haven't tried that actually. 'cause I don't know how to implement that to mine. Sorry, I'm new to programming — Wil Dech, Jul 26 '16 at 05:03
@objectNotFound I have tried it now and it's not getting error but it says Access is denied. in the WHICHFILE column — Wil Dech, Jul 26 '16 at 05:10
http://stackoverflow.com/a/11560496/2628302 try running just the sp_configure commands listed in that post (its a one time config change only) and then try that code again and see if you can get past the access denied issue — objectNotFound, Jul 26 '16 at 05:17

score 0 · Accepted Answer · edited May 23 '17 at 11:51

try these to make sure you have the right permissions and settings to do xp_cmdshell

One Time config change to enable here : https://stackoverflow.com/a/5131503/2628302

--To Test if that worked run these commands. There should be no errors and should return list of files under c:\
declare @files table (ID int IDENTITY, FileName varchar(500))
insert into @files execute xp_cmdshell 'dir c:\ /b'
select * from @files

--if no errors then proceed to create this SP as shown below. This is the one that does all the work

CREATE PROCEDURE dbo.sp_BulkInsAllFilesInDirectory
AS
BEGIN
    --a table to hold filenames 
    Declare   @ALLFILENAMES as TABLE (WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

    --some variables
    declare @filename varchar(255),
            @path     varchar(255),
            @sql      varchar(8000),
            @cmd      varchar(1000)


    --get the list of files to process:
    SET @path = 'C:\Bulk\'
    SET @cmd = 'dir ' + @path + '*.csv /b'
    INSERT INTO  @ALLFILENAMES(WHICHFILE)
    EXEC Master..xp_cmdShell @cmd
    UPDATE @ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null


    --cursor loop
    declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM @ALLFILENAMES where WHICHFILE like '%.csv%'
    open c1
    fetch next from c1 into @path,@filename
    While @@fetch_status <> -1
      begin
          --bulk insert won't take a variable name, so make a sql and execute it instead:
           set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' '
               + '     WITH ( 
                       FIELDTERMINATOR = '','', 
                       ROWTERMINATOR = ''\n'', 
                       FIRSTROW = 2 
                    ) '
        print @sql
        exec (@sql)

        fetch next from c1 into @path,@filename
      end
    close c1
    deallocate c1

END

--- TEST it by running it like so (start with just one csv file in C:\BULK\ directory. If it works for one it will most likely work for more than one file.

EXEC dbo.sp_BulkInsAllFilesInDirectory

see if there are errors. Leave a message here and I will check tomorrow. Good luck.

Thank you so much. I have tried this but the following errors occur: — Wil Dech, Jul 26 '16 at 05:51
BULK INSERT PH_ProdCost.dbo.ProdCost FROM 'C:\Test\po.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ) Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)". — Wil Dech, Jul 26 '16 at 05:51
@WilDech you are most welcome. For the benefit of others Please edit your question and explain how you got over that error (Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK"). — objectNotFound, Jul 26 '16 at 11:53

SQL BULK INSERT FROM CSV From a Folder

1 Answers1