How to import file information from a file system into a sql server table

jjliu4492
jjliu4492 used Ask the Experts™
on
I have files of type jpg, tif, and txt located on my computer, and I want to be able to import data from those files into two tables in sql server. Depending on the file type, I want to import to either one or the other table. How would I go about writing a script to do this? Also, how can I store the path of the file into the database as well?
Files.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You can create a table with the datatype as Image or Varbinary. Then insert the path or file into the table as below.
CREATE TABLE tblData
(
	id INT IDENTITY (1,1),
	FilePath NVARCHAR(MAX),
	FileData VARBINARY(MAX),
	Extn NVARCHAR(20)
)

DECLARE @FilePath NVARCHAR(MAX);
DECLARE @Extn NVARCHAR(20)

SET @FilePath ='C:\Documents and Settings\manik\Desktop\DateFormat.Jpg'
SET @Extn = SUBSTRING(@FilePath,CHARINDEX('.',@FilePath)+1,LEN(@FilePath))

INSERT INTO tblData 
(
	FilePath,
	FileData,
	Extn
)
SELECT @FilePath,@FilePath,@Extn

Open in new window

Author

Commented:
If all of my files (jpg, tif, and txt) are located within the same folder, how would I determine which table I should be writing to based upon the file extension? How would I write that into the script?

Author

Commented:
And I want to be able to loop through all the files in the folder, and do the insert for each one, and then move onto the next file. How would I go about doing that in the script?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
You can not do that solely using TSQL. You need the help of a programming language to do this. Anyway Powershell script whould be ideal for you..

This describes how you can iterate through files using powershell

http://technet.microsoft.com/en-us/library/ee176988.aspx

This describes how you can run a sql script from powershell

http://serialseb.blogspot.com/2007/09/executing-sql-scripts-from-powershell.html

If you are new to powershell just google about it. Its awsome scripting language.

Author

Commented:
Is there a way to loop through the files, and then insert into one of the two tables (depending upon the file type), by using SSIS?
Commented:
Yes you can, it is another option you can use the for each loop container in ssis to do this easily. Here is a greate step by step tutorial.

http://msdn.microsoft.com/en-us/library/ms166566.aspx

Anyway I would have used powershell coz it is hassel free and you have total control over what you are doing.
lcohanDatabase Analyst
Commented:
You could list them all by extension(type) in a file then read that file in SQL temp table and use it to build a cursor or loop for your inport rather than SSIS - you have more flexibility in t-sql:

--list all jpg image files
exec xp_cmdshell 'dir C:\File_Folder\*.jpg > C:\File_Folder\image_file_list.txt'

--read their names into a temp table
Create table #tempfile (line varchar(8000))
exec ('bulk insert #tempfile from "C:\File_Folder\image_file_list.txt"')
   
select * from #tempfile

Author

Commented:
How can I insert the filename from the foreach loop container into my sql server table in an SSIS package? Is there a way to extract other data from the file, such as date, title/name, location, file type, etc?

Author

Commented:
I am trying to build an Execute SQL task in my dtsx package, with the Expressions Property = SQLStatementSource, and the Expression = INSERT INTO dbo.t_vadoc ([vadoc_batch], [vadoc_docpack], [vadoc_firstimage], [vadoc_lastimage], [vadoc_doctype], [vadoc_firstpage], [vadoc_lastpage], [vadoc_contno])
VALUES (GetDate(), GetDate() , REPLACE( @[User::FileName],'_FIRST', '' ) , REPLACE( @[User::FileName],'_LAST', '' ) , 'GRAPHIC', REPLACE( @[User::FileName],'_FIRST', '' ) , REPLACE( @[User::FileName],'_LAST', ''), GetDate()).

When I try and evaluate the expression, I get the following error message. What am I doing wrong?


ErrorMessage.docx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial