"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..

"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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"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 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"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 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"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 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 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"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 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 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"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 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 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 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"Mark Williams" 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 default
> 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:
> >
> > > 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 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 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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..

"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/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_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..