July 23, 2003 at 11:24 am
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.
July 23, 2003 at 11:46 am
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
July 23, 2003 at 12:46 pm
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.
July 23, 2003 at 2:40 pm
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
July 23, 2003 at 3:01 pm
Have you tried to BCP Text data into SQL Server and select records that match the filed in existing table? BCP is quite fast.
July 23, 2003 at 7:42 pm
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
July 24, 2003 at 4:56 am
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_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
NULL NULL myfile#txt TABLE NULL
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 *
FROM TextSource...[myfile#txt]
Best of luck
robbac
___the truth is out there___
robbac
___the truth is out there___
July 24, 2003 at 10:01 am
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