February 4, 2015 at 8:47 am
Hi
I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
Cant use SSIS as the developers do not know how to use .
February 4, 2015 at 8:57 am
nikki123 (2/4/2015)
Hi
I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
Cant use SSIS as the developers do not know how to use .
do you have a table that lists the names of the files imported so far?
you can get the list of files via xp_cmdShell or a CLR function, and build a dynamic sql statement with bulk insert with those results.
here's an old example, that is going to process all the files in various folders, assuming all the files have the same schema/layout. but what if you ran it twice in a row...
you need to prevent the same file from importing more than once, which depends on your processes; you either track the file usage, and prevent dupes int eh loop below, or you delete/move the files so they are not processed again.
-a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
--the source table: yours already exists, but needed for this example.
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
--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:\DB\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB2\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB3\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB4\'
SET @cmd = 'dir ' + @path + '*.txt /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 '%.txt%'
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 BULKACT FROM ''' + @path + @filename + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 2
) '
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Lowell
February 4, 2015 at 9:09 am
Thank you for the reply
We get the files quaterly , and delete the old files after the import as we a create a table and are kept in database .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply