Import multiple files into multiple tables

  • 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

  • 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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply