Import multiple text files into Single table

How to import multiple text files (residing in single folder) into SQL Server table? I know how to import single file but not sure how multiple files could be loaded? Pls. guide.

Thanks,

HShah

I am assuming that thefiles all have the same structure.

There are a number of options availableto you. See here:

Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

-Jamie

sql

Import multiple csv into multiple tables

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecietedChicagoboy27 (jeremy.bird@.gmail.com) writes:

Quote:

Originally Posted by

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecieted

You could use BCP or BULK INSERT, but it appears that you would have to
add quite some control code on top that.

A better alternative could be to turn to SQL Server Integration Services,
which is what the Import Wizard uses. Unfortunately, though, I am
completely unexperienced myself with SSIS, so I cannot assist further.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

Import folder of XML

I'm Sql7 and have a folder full of XML"S I would like to import into a
table.
any examples how I could do this ...
Thanks.
You best upgrade to SQL Server 2005... You can use NTEXT or TEXT
columns...
Best regards
Michael
"Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
news:NOGdnZNdXLxiwkveRVn-iQ@.kconline.com...
> I'm Sql7 and have a folder full of XML"S I would like to import into a
> table.
> any examples how I could do this ...
>
> Thanks.
>

Import folder of XML

I'm Sql7 and have a folder full of XML"S I would like to import into a
table.
any examples how I could do this ...
Thanks.You best upgrade to SQL Server 2005... You can use NTEXT or TEXT
columns...
Best regards
Michael
"Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
news:NOGdnZNdXLxiwkveRVn-iQ@.kconline.com...
> I'm Sql7 and have a folder full of XML"S I would like to import into a
> table.
> any examples how I could do this ...
>
> Thanks.
>

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.sql

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.

Import all xml-Files from a Folder into SQL

Hello

I'm just starting with SQL and BIDS. I have created a xml-Import to SQL Server which works without any problem.

Now I like to import (daily) all xml-files of an appropriate Folder at once to the DB. The files will have different names each day.

I could not find any help in the internet to solve this problem.

Can someone give me an Idea how to import many xml-files out of the same folder to SQL?

Thanks & regards

Chaepp

The For Each Loop can do this. It is covered in Books online, or try this article-

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)

|||

Hi DarrenSQLIS

Thank You very much for the info's. It's working perfect.....

Have a nice day

Regards

Chaepp