linked server and pipe delimted file

I have can query a comma delimited file in query analyzer easily. Content of
mytest1.csv file on txtsrv21 linked server is as follows
/*
c1,c2,c3
11,12,13
22,33,44
55,66,77
88,33,12
*/
When I run the following select:
SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
I get the following results
/*
c1 c2 c3
-- -- --
11 12 13
22 33 44
55 66 77
88 33 12
(4 row(s) affected)
*/
But if I change the file content such that its pipe delimited instead of
comma delimited as follows:
/*
c1|c2|c3
11|12|13
22|33|44
55|66|77
88|33|12
*/
I get following errors when I run the following select
SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
/*
Server: Msg 207, Level 16, State 3, Line 9
Invalid column name 'c1'.
Server: Msg 207, Level 16, State 1, Line 9
Invalid column name 'c2'.
Server: Msg 207, Level 16, State 1, Line 9
Invalid column name 'c3'.
*/
I would like to be able to run select on pipe delimited content just as I
could on comma delimited.
Is there any setting that I am missing?
Google or searching through forums is not turning up any thing.
Please help.
TIA..You'll need to put a file named schema.ini in the same folder with your
mytest1.csv text file. See
http://msdn.microsoft.com/library/d...ma_ini_file.asp
for how to create the schema.ini text file for a custom delimiter, like |
Good luck.
"sqlster" wrote:

> I have can query a comma delimited file in query analyzer easily. Content
of
> mytest1.csv file on txtsrv21 linked server is as follows
> /*
> c1,c2,c3
> 11,12,13
> 22,33,44
> 55,66,77
> 88,33,12
> */
> When I run the following select:
> SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
> I get the following results
> /*
> c1 c2 c3
> -- -- --
> 11 12 13
> 22 33 44
> 55 66 77
> 88 33 12
> (4 row(s) affected)
> */
> But if I change the file content such that its pipe delimited instead of
> comma delimited as follows:
> /*
> c1|c2|c3
> 11|12|13
> 22|33|44
> 55|66|77
> 88|33|12
> */
> I get following errors when I run the following select
> SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
>
> /*
> Server: Msg 207, Level 16, State 3, Line 9
> Invalid column name 'c1'.
> Server: Msg 207, Level 16, State 1, Line 9
> Invalid column name 'c2'.
> Server: Msg 207, Level 16, State 1, Line 9
> Invalid column name 'c3'.
> */
> I would like to be able to run select on pipe delimited content just as I
> could on comma delimited.
> Is there any setting that I am missing?
> Google or searching through forums is not turning up any thing.
> Please help.
> TIA..|||As a follow up, I just tested the following
Create a text file named schema.ini in the same directory with mytest1.csv .
The schema.ini file had the following lines:
[mytest1.csv]
Format=Delimited(|)
The contents of mytest1.csv :
c1|c2|c3
4|5|6
7|8|9
10|11|12
Add the linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'f:\texts',
NULL,
'Text'
GO
SELECT * FROM txtsrv...file1#txt
returns
c1 c2 c3
-- -- --
4 5 6
7 8 9
10 11 12
(3 row(s) affected)
You can even change the delimeter "on the fly," without dropping and
re-creating the linked server. You can even specify different delimiters for
different files within the same folder.
"Mark Williams" wrote:
> You'll need to put a file named schema.ini in the same folder with your
> mytest1.csv text file. See
> http://msdn.microsoft.com/library/d...ma_ini_file.asp
> for how to create the schema.ini text file for a custom delimiter, like |
> Good luck.
> "sqlster" wrote:
>|||Thanks Mark,
It worked..
"Mark Williams" wrote:
> You'll need to put a file named schema.ini in the same folder with your
> mytest1.csv text file. See
> http://msdn.microsoft.com/library/d...ma_ini_file.asp
> for how to create the schema.ini text file for a custom delimiter, like |
> Good luck.
> "sqlster" wrote:
>|||Mark,
What if all the imported files in the designated directory are pipe
delimited? Do I have to keep adding those file names in the schema.ini file?
I will look around for the answer but if you have the solution handy, please
post it here.
Once again, thanks for the fast answer.
"Mark Williams" wrote:
> As a follow up, I just tested the following
> Create a text file named schema.ini in the same directory with mytest1.csv
.
> The schema.ini file had the following lines:
> [mytest1.csv]
> Format=Delimited(|)
> The contents of mytest1.csv :
> c1|c2|c3
> 4|5|6
> 7|8|9
> 10|11|12
> Add the linked server
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'f:\texts',
> NULL,
> 'Text'
> GO
> SELECT * FROM txtsrv...file1#txt
> returns
> c1 c2 c3
> -- -- --
> 4 5 6
> 7 8 9
> 10 11 12
> (3 row(s) affected)
> You can even change the delimeter "on the fly," without dropping and
> re-creating the linked server. You can even specify different delimiters f
or
> different files within the same folder.
> "Mark Williams" wrote:
>|||You can edit the registry to set the default delimiter that JET will use. I
haven't tried this. Run regedit, go to the
HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text key. (your version may be
different, lik3 3.5 instead of 4.0).
Double-click on the Format string value. The default value is CSVDelimited.
Change it to
Delimited(|)
click OK, and exit regedit. This will change the default delimiter character
to a '|'. I am not sure if it will require a restart.
"sqlster" wrote:
> Mark,
> What if all the imported files in the designated directory are pipe
> delimited? Do I have to keep adding those file names in the schema.ini fil
e?
> I will look around for the answer but if you have the solution handy, plea
se
> post it here.
> Once again, thanks for the fast answer.
>
> "Mark Williams" wrote:
>|||Tested this and it works. Don't even need to reboot!
"Mark Williams" wrote:
> You can edit the registry to set the default delimiter that JET will use.
I
> haven't tried this. Run regedit, go to the
> HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text key. (your version may be
> different, lik3 3.5 instead of 4.0).
> Double-click on the Format string value. The default value is CSVDelimited
.
> Change it to
> Delimited(|)
> click OK, and exit regedit. This will change the default delimiter charact
er
> to a '|'. I am not sure if it will require a restart.
> "sqlster" wrote:
>|||Is there a way other than registry manipulation?
TIA..
"Mark Williams" wrote:
> You can edit the registry to set the default delimiter that JET will use.
I
> haven't tried this. Run regedit, go to the
> HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text key. (your version may be
> different, lik3 3.5 instead of 4.0).
> Double-click on the Format string value. The default value is CSVDelimited
.
> Change it to
> Delimited(|)
> click OK, and exit regedit. This will change the default delimiter charact
er
> to a '|'. I am not sure if it will require a restart.
> "sqlster" wrote:
>|||Not sure if you saw my earlier post, but I did test editing the registry to
define the customer delimiter of |, and it works. Didn't even need to reboot
.
The only thing I would be cautious about is that this will change the defaul
t
delimiter for all Text file connections.
If you don't want to edit the registry, then I'm afraid the only way is to
create that schema.ini file, and have a section for every file in the folder
that the linked server points to. The schema.ini file allows you to override
the default on a file-by-file basis. I tried using wildcards, like
[*.csv]
Format=Delimited(|)
but it did not work.
"sqlster" wrote:
> Is there a way other than registry manipulation?
> TIA..
> "Mark Williams" wrote:
>|||Mark, thank you very much...
"Mark Williams" wrote:
> Not sure if you saw my earlier post, but I did test editing the registry t
o
> define the customer delimiter of |, and it works. Didn't even need to rebo
ot.
> The only thing I would be cautious about is that this will change the defa
ult
> delimiter for all Text file connections.
> If you don't want to edit the registry, then I'm afraid the only way is to
> create that schema.ini file, and have a section for every file in the fold
er
> that the linked server points to. The schema.ini file allows you to overri
de
> the default on a file-by-file basis. I tried using wildcards, like
> [*.csv]
> Format=Delimited(|)
> but it did not work.
> "sqlster" wrote:
>