September 20, 2011 at 1:10 am
Hi
I am having an issue with a linked server I have configured to a tab-delimited text file.
The file resides in a folder where I also have a schema.ini file defined.
There are a few rows in the file where a particular field contains either a comma and/or quote characters.
This does not cause an issue to Excel when it opens the file but it does when I try to access the file through a linked server using OPENQUERY. Below is my setup.
Schema.Ini file contents are as follows
[Parts.txt]
Format=TabDelimited
ColNameHeader=False
Col1="Part No" Text
Col2="OEM Part No" Text
Col3="Unit Price" Double
Col4="Description" Text
Col5="Supercession Part No" Text
Col6="OEM Supercession Part No" Text
Linked Server Configuration
/****** Object: LinkedServer [PARTS TEMP] Script Date: 09/20/2011 16:38:28 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PARTS TEMP', @srvproduct=N'Parts Temp', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\\SYDIFS01\Group_Data\Temp\Parts\', @provstr=N'Text'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PARTS TEMP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTS TEMP', @optname=N'use remote collation', @optvalue=N'true'
When you look at the row in question in the text file, it contains the following. I have used a | character to represent the location of the TAB character.
EWD238YBB40 | EWD23-8Y,"BB40 | 33.32 | 42,HZB50RZB5SUP||
When I run the following query
SELECT * FROM OPENQUERY([Parts Temp], 'Select [Part No], [OEM Part No], [Unit Price], Description, [Supercession Part No], [OEM Supercession Part No] From [Parts#txt]') AS Parts where [Part No] = 'EWD238YBB40'
I get the following result (sorry, I cant get it to display nicely)
Part No OEM Part No Unit Price Description Supercession Part No OEM Supercession Part No
---------------- -------------- ----------- -------------- ------------------------ -------------------------
EWD238YBB40 EWD23-8Y, NULL NULL NULL NULL
When I Open the file in Excel however it parses the file correctly.
It would seem that the apostrophie (quote) character is causing the issue. However, in the Schema.ini file the file is explicitly defined as tab delimited so why would should it? Is there a work around for this?
Any help is appreciated.
Doug
September 20, 2011 at 1:38 am
I found a work around.
I set the TextDelimiter= setting in the Schema.ini file to a character that is highly unlikely to ever be used.
I set it to TextDelimiter=` and now it works fine. Who would have thought?
Thanks
Doug
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply