Joining a text file with a table in a query

  • I have a text file (fixed length) that I want to import into a table, but I need to only import records that match by a field in an existing table. I have created code to run through the text file record by record and test it. If it passes it is appended to the table. The problem is that both the table and text file are quite large and this takes several hours. If I could query against the text file to select only the matching records and then do an insert it would be much faster. I have not been able to determine the correct syntax to query the two sources, joining by the common field.

    Any help is greatly appreciated. Thanks.

  • I've used the following to accomplish something similar with Excel. I'm sure by changing the ODBC Driver name/parameters you could use this with a text file too. Search the BOL/Web for OPENROWSET

    select *

    from RealTable

    join OPENROWSET ('MSDASQL','Driver=Microsoft Excel Driver (*.xls);DBQ=d:\MYEXCELFILE.xls','select * from "Sheet1$"') as TextTable

    on RealTable.Col = TextTable.Col

  • Create linked server to Text file as example below.

    exec sp_addlinkedserver

    @server = 'Documents',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @srvproduct = 'OLE DB Provider for Jet (Text IISAM)',

    @datasrc = 'c:\temp\',

    @provstr = 'Text'

    go

    exec sp_addlinkedsrvlogin 'Documents', false, NULL, 'admin'

    go

    Modify Text file by adding a header for all columns.

    "id","lname","fname","phone","address1","addrsss2","state","zipcode","status"

    "172-32-1176","White","Johnson","408 496-7223","10932 Bigge Rd.","Menlo Park","CA","94025",True

    "213-46-8915","Green","Marjorie","415 986-7020","309 63rd St. #411","Oakland","CA","94618",True

    Run query like following.

    select *

    from authors a

    inner join (SELECT * FROM [Documents]...[authors#txt] where state = 'CA') b

    on a.au_id = b.id

    I don't know whether you could achieve better performance in this way.

    You may consider create DTS package to import Text file and filter those records by using look-up table too.

  • My file is a fixed length text file. Will I need additional parameters when adding the linked server to specify the schema? Can I add a reference to a bcp.fmt or schema.ini as using a header row will probably not work.

    Thanks

  • Have you tried to BCP Text data into SQL Server and select records that match the filed in existing table? BCP is quite fast.

  • I've used BCP to do a straight import but I have not used an existing table for selecting the records to import. Would I use a lookup for this?

    One other thing...

    The reason I wanted to use the join between the two sources was because I am importing into a SQL database withich is part of a Pivotal CRM system. When I insert new records from within SQL and.or VB I have to use a Pivotal OCX to insert a binary record ID and populate calculated fields. I can do this with a stored procedure, calling it for each record insertd. Or, I can add the records using a custom Pivotal OCX which does this. Using bcp to insert will get my data in, but I won't get the binary ID field populated unless I run the procedure for each record inserted.

    Also, I tried the OpenRowSet approach and I may have something wrong with my syntax. I keep getting and error (OLE DB provider 'MSDASQL' reported an error.

    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified])

    The syntax I am using is:

    select * from dbo.Contact join OPENROWSET('MSDASQL','Driver={Microsoft Text Driver(*.txt;*.csv)};DBQ=D:\Projects\ExtFileMgmt\DataFiles\Pending;','Select * from AdvocareMBrecords.TXT')

    as TextTable on dbo.Contact.Social_Security = TextTable.SSN

    Any more suggestions?

    Thanks

    Edited by - clerner on 07/23/2003 10:05:30 PM

  • Hi Clerner!

    Here is some more explanation on how to access flat files (i.e. txt) as tables in SQL Server.

    First you need to add the directory where you keep your textfiles:

    EXEC sp_addlinkedserver 'TextSource',
    
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\path',
    NULL,
    'Text'
    GO

    'TextSource' is the name you use to access the directory, and 'c:\path' is the local path on the server where your text-file(s) are.

    To find out that the linked server has been correctly configured, you can run:

    EXEC sp_tables_ex TextSource

    This will display a result something like:

    TABEL_CATTABLE_SCHEMTABLE_NAMETABLE_TYPEREMARKS
    
    NULLNULLmyfile#txtTABLENULL

    Note that the dot "." before the file-extansion has been replaces whith a "#".

    In the same directory as the file(s) are, you need to have a schema-file named "schema.ini" with a definition of what the file looks like:

    [myfile.txt]
    
    ColNameHeader=False
    Format=FixedLength
    CharacterSet=ANSI
    Col1="MyColumn1" Text Width 10
    Col2="MyColumn2" Text Width 10
    Col3="MyColumn3" Short Width 12
    Col4="MyColumn4" Long Width 12

    Note that the filename has to be in the schema.ini. If you have more then one text-file you will access you just add the next file-name into the schema.ini enclosed in "[nextfilename.txt]" and then the definition of that file. More information about the schema.ini can be found at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_96.asp

    After above is done, you can simply access the text-file within QA with following syntax:

    SELECT*
    
    FROMTextSource...[myfile#txt]

    Best of luck

    robbac

    ___the truth is out there___


    robbac
    ___the truth is out there___

  • I am able to create the linked server but I am getting a message when I attempt to verify the tables or query the tables (files)

    The error is:

    Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

    [OLE/DB provider returned message: '\\Mhc_remote\Groups\mis\BHO\PREMIER\Pending' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]

    The T-SQL statement I am using to create it is:

    EXEC sp_addlinkedserver 'Advocare',

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    '\\Mhc_remote\Groups\mis\BHO\PREMIER\Pending',

    NULL,

    'Text'

    GO

    What am I missing?

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

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