Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import multiple files into multiple tables Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 5:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:42 PM
Points: 229, Visits: 248
Dear DBAs,

I have 200 flat, text files. Each one has different fields separated by one specific charactere (¶). The name of the files are the name I wish to give to the tables I want to import. The names of the fields are in the first row of each file.

Is there a way to import them all in once, or I have to import one by one?

Do you have any script ready to do it?

Thanks in advance.

DBA Cabuloso,
Lucas Benevides


________________
DBA Cabuloso
Lucas Benevides
Post #1469011
Posted Monday, July 1, 2013 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 2,848, Visits: 5,095
You cannot upload differenet files at once.
I dont' think anyone would have a cript which you could use "out of the box".
There are two main ways for uploading flat files into SQL Server:

1. using SSIS
2. using bulk load: bcp utlity or BULK INSERT


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1469030
Posted Monday, July 1, 2013 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
Lucas the strategy depends on the details.

I'm not anti -SSIS, but if it can be done via TSQL, then it can be done a lot faster.

if the destination tables already exist, then you can easily use BULK insert and some dir commands via xp_cmdShell;

if the tables don't exist yet, you could use a linked server, which points to a folder full of files, and do a SELECT * INTO NEWTABLE FROM MyTextLinkedServer...TableName.

I have script examples of both, and the one i'm posting below is assuming "tables don't exist yet, create on the fly."

a couple of prerequisites if they are not in place already:

install the AccessDatabaseEngine_x64.exe from microsoft:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;

this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
After that is isntalled:
   --Required settings for the provider to work correctly as a linked server
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1


and then the code for the linked server:
   
--#################################################################################################
--Linked server Syntax for Folder Full Of Text Files
--#################################################################################################

--add a folder as a linked server to access all .txt and .csv files in the folder
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
SET @server = N'myFolderFullOfFiles'
SET @srvproduct = N'OLE DB Provider for ACE'
SET @provider = N'Microsoft.ACE.OLEDB.12.0'
SET @datasrc = N'C:\Data\'
SET @provstr ='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\;Extended Properties="text;HDR=YES;FMT=Delimited" '
set @provstr = 'Text'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,@provstr,@provstr
--===== Create a linked server to the drive and path you desire.
--EXEC dbo.sp_AddLinkedServer myFolderFullOfFiles,
-- 'MSDASQL',
-- 'Microsoft.ACE.OLEDB.12.0',
-- 'C:\',
-- NULL,
-- 'Text'
GO
--===== Set up login mappings.
EXEC dbo.sp_AddLinkedSrvLogin myFolderFullOfFiles, FALSE, NULL, Admin, NULL
GO

and here's the core workload: get all the tables available in the text linked server, and loop through them all.
--===== List the tables in the linked server which is really a list of 
-- file names in the directory. Note that the "#" sign in the
-- Table_Name is where the period in the filename actually goes.

CREATE TABLE [#Results] (
[ResultsId] INT IDENTITY(1,1) NOT NULL,
[TABLE_CAT] VARCHAR(128) NULL,
[TABLE_SCHEM] VARCHAR(128) NULL,
[TABLE_NAME] VARCHAR(128) NULL,
[TABLE_TYPE] VARCHAR(128) NULL,
[TABLE_REMARKS] VARCHAR(128) NULL,
CONSTRAINT [PK__Results_ID] PRIMARY KEY CLUSTERED ([ResultsId]) )


INSERT INTO [#Results](TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,TABLE_REMARKS)
EXEC dbo.sp_Tables_Ex myFolderFullOfFiles

--now a cursor to get all the table sinto the current database.

--note this assumes the table doesn't exist at all, maybe add drop if exists?
declare
@isql varchar(2000),
@tbname varchar(64)

declare c1 cursor for select TABLE_NAME from [#Results]
open c1
fetch next from c1 into @tbname
While @@fetch_status <> -1
begin
select @isql = 'SELECT * INTO ' + quotename(@tbname) + 'FROM myFolderFullOfFiles...' + quotename(@tbname)+';'
print @isql
exec(@isql)
fetch next from c1 into @tbname
end
close c1
deallocate c1
--===== Drop the text server
-- EXEC dbo.sp_DropServer 'myFolderFullOfFiles', 'DropLogins'


I just tested this exact scenario, and create 12 tables on the fly, based on the 12 txt/csv files that happen to exist in my C:\Data folder.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469033
Posted Monday, July 1, 2013 8:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:42 PM
Points: 229, Visits: 248
Lowell,

You SET the @provst variable two times, one following the other. Is this correct?

I supposed it was incorrect and changed the second to @provstr2. Where you write "text" is to put the separator character? If not, where do I put the separator?

I tried to run and got the following message:


OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "myFolderFullOfFiles" returned message "Erro não especificado". (Not specified error)

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "myFolderFullOfFiles".


Thanks a lot.


________________
DBA Cabuloso
Lucas Benevides
Post #1469075
Posted Monday, July 1, 2013 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
Lucas the plain set @provstr = 'Text' was what is working for me; the other one was one that i was testing, and not applicable here.

for the error you are getting, I think it's going to be permissions/location of the folder you are using specifically in your case.
what specific path to the folder are you using in your case?

you probably know this, but when you access files or folders or network pats from SQL, it doesn't intuitively use the permissions you might think it should...instead of using YOUR permissions (localadmin/network admin, etc)

it ends up using the startup account of the service...that startup account might not be a domain user(so no network shares work)
or a limited account that doesn't have access to your desktop/my documents/most folders on the drive.

take a look at the startup account you are using:








Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469086
Posted Monday, July 1, 2013 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
ouch, the custom delimiters finally soaked in; that' certainly adds tot eh complexity, since i'd normally BULK INSERT something custom delimtied...but you need the tables built on the fly, based on the table name,

The Import/Export wizard does custom delimters real well, but it's really single-file-scoped, i think, and doesn't do all files nicely.

i've got a TSQL solution from long ago, where i changed the registry settings for the driver to a tab instead of the comma, but that was using the JET driver, and not the ace;
once you change the setting in the registry, it's important to change it back;
i'll test my solution now, but i'll have to find and replace the current commas with ¶, and test it from there.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469100
Posted Monday, July 1, 2013 8:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:42 PM
Points: 229, Visits: 248
Lowell,

I don't think it is a security issue. I am testing in my local machine, and my account runs the SQL Server. My account has permission to read the files.

My files have their fields separated by a specific character (¶), not comma, or semi-comma.
Where do I specify this?
It is not specified anywhere in your code, so how will it work?

Thanks again.


________________
DBA Cabuloso
Lucas Benevides
Post #1469103
Posted Monday, July 1, 2013 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 2,848, Visits: 5,095

I would not use this method of uploading files at all for many reasons. Do you have 32-bit or 64-bit system?
Anyway you can try specify your delimeter in a connection string like that:

FMT=Delimited(¶)

Still, don't think it will help you much.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1469117
Posted Tuesday, July 2, 2013 11:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 431, Visits: 1,742
I was looking at this script a month or so ago. Maybe you can make it work for your purposes? It sounds pretty close already.

http://www.sqlservercentral.com/scripts/T-SQL/95877/

Post #1469691
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse