Importing Multiple pipe delimiter files at once

• jeff.cohen 8758

  SSC Rookie

  Points: 48

  More actions

  February 6, 2018 at 9:49 am

  #343894

  Hello everyone,

  I am able to import one pipe delimited file at a time with the script below but can't get it to import multiple files at once. I tried to use a wildcard in the file name but it won't allow it (ie. *.txt or eJ*.text).

  Any assistance that you can provide would be greatly appreciated.

  Thank you,

  BULK INSERT databasename.tablename
  FROM 'C:\temp\EJ_Detail_file.txt'
  WITH
  (
  FIELDTERMINATOR ='|',
  FIRSTROW = 2,
  ROWTERMINATOR ='\n'
  )

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  February 6, 2018 at 1:27 pm

  #1978774

  Try:
  
  DECLARE
       @DirTree table (subdirectory varchar(255), depth tinyint, is_file bit);
  DECLARE
       @path varchar(255)='C:\temp'
     , @bs varchar(1)='\'
     , @fullpath varchar(255)
     , @sql nvarchar(MAX);
  INSERT @DirTree
     ( subdirectory
     , depth
     , is_file )
  EXEC sys.xp_dirtree @path, 1, 1 ;
  DECLARE FILES CURSOR FOR      
  SELECT @path+@bs+dt.subdirectory
  FROM @DirTree dt;
  OPEN FILES
  FETCH FILES INTO @fullpath;
  WHILE @@fetch_Status = 0
  BEGIN
     SET @sql =
     'BULK INSERT databasename.tablename
     FROM '+@fullpath+'
     WITH
     (
     FIELDTERMINATOR =''|'',
     FIRSTROW = 2,
     ROWTERMINATOR =''\n''
     )'
     EXEC(@sql);
     FETCH FILES INTO @fullpath;
  END
  CLOSE FILES;
  DEALLOCATE FILES;