x

Automatic File Import

I m using sql server 2008 and I need a way to import all the files in a directory. The file names will change but the file layout will remain the same. I have written a Bulk Insert that will add the files once i have the file name. The only thing I am missing is a way to get the file names and possibly add them to a table where I can then run my script against them and import each one.

I cannot use xp_cmdshell and this will be on a occur on a daily bases so I want to automate it if possible.

more ▼

asked May 08, 2013 at 04:43 AM in Default

avatar image

imation91304
40 1 1 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Thanks, for the reply. I did find another way to do it. I use xp_dirtree

--Create a temporary table to hold the entire list of files in the folder

CREATE TABLE #DirectoryTree ( id int IDENTITY(1,1) ,subdirectory nvarchar(512) ,depth int ,isfile bit);

--Insert all the files in the directory into my temporary table

INSERT #DirectoryTree (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree 'C:\SomeFolder',1,1;

INSERT INTO ImportFiles (subdirectory,depth,isfile,isimported) SELECT subdirectory,depth,isfile,0 FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.TXT' AND subdirectory NOT IN(SELECT subdirectory FROM ImportFiles) ORDER BY id;

more ▼

answered May 09, 2013 at 02:57 PM

avatar image

imation91304
40 1 1 3

Glad you found a solution! Be careful with the undocumented extended procedures as they might not be supported by Microsoft and usually introduce a whole different level of security concerns.

May 09, 2013 at 03:41 PM JohnM
(comments are locked)
10|1200 characters needed characters left

create a package in ssis to import

more ▼

answered May 08, 2013 at 09:12 AM

avatar image

ramesh 1
2.2k 66 69 73

(comments are locked)
10|1200 characters needed characters left

I agree with @Ramesh1 in that using SSIS is probably the better course of action. Here's an article that might help you get started down this path:

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

It's older but the logic still holds true.

Hope this helps!

more ▼

answered May 08, 2013 at 12:45 PM

avatar image

JohnM
14.1k 3 7 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170
x71
x25
x21

asked: May 08, 2013 at 04:43 AM

Seen: 1448 times

Last Updated: May 09, 2013 at 03:41 PM

Copyright 2017 Redgate Software. Privacy Policy