Retrieving data from a plain file

Dear all,
I'd need to retrieve data from a text file located in a folder from a SELECT
statement. How could I do such thing? Is it possible obtain all the data and
then show them on the screen or better, import them to a SQL table'
Best regards,
EnricIt is better to import the file using BCP/BULK INSERT/DTS etc. if you need
that data in various queries.
Otherwise, you can directly query the text file data too. Here are some
examples:
http://www.users.drew.edu/skass/sql/TextDriver.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:B53B3A9D-8DDC-434B-AEF9-19F69D3B4DA4@.microsoft.com...
Dear all,
I'd need to retrieve data from a text file located in a folder from a SELECT
statement. How could I do such thing? Is it possible obtain all the data and
then show them on the screen or better, import them to a SQL table'
Best regards,
Enric|||Create a linked server to the text file and use a Schema.ini file to describ
e
the structure of the data:
http://msdn.microsoft.com/library/d...r />
_6a44.asp
http://msdn.microsoft.com/library/d...ma_ini_file.asp
This enables you to query the text file directly, as if it were a SQL table.
ML|||If your ADO.NET application needs to display records from a text file, then
you can just open the file directly using the Microsoft Text Driver. There
is no need to go through SQL Server.
Dim cn As New OdbcConnection("Driver={Micros_oft Text Driver
(*.txt;*. csv)};DefaultDir=d:\websites_\jpg\dataim
port;")
Dim cmd As New OdbcCommand ("select * From cfr.csv")
If you need the data in SQL Server, then you can use DTS to import it.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:B53B3A9D-8DDC-434B-AEF9-19F69D3B4DA4@.microsoft.com...
> Dear all,
> I'd need to retrieve data from a text file located in a folder from a
> SELECT
> statement. How could I do such thing? Is it possible obtain all the data
> and
> then show them on the screen or better, import them to a SQL table'
> Best regards,
> Enric

Retrieve the default data folder path

Is it possible to find out the default path SQL Server creates databases in
depending on what instance you are connected to. E.g. My default instance
default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My
named instance path I also have is C:\Program Files\Microsoft SQL
Server\MSSQL$Testing\Data and my SQL Express data path is C:\Program
Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data.
Can I find out the default data path for the current instance I am connected
to using a query?
ThanksYou can probably use the Model database's SysDatabases.FileName to get the
path.
"Chris" wrote:

> Is it possible to find out the default path SQL Server creates databases i
n
> depending on what instance you are connected to. E.g. My default instance
> default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My
> named instance path I also have is C:\Program Files\Microsoft SQL
> Server\MSSQL$Testing\Data and my SQL Express data path is C:\Program
> Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data.
> Can I find out the default data path for the current instance I am connect
ed
> to using a query?
> Thanks
>
>|||Thanks Mike,
If I do the following it seems to return the path.
SELECT REPLACE(filename, 'model.mdf', '') AS DataPath FROM SysDatabases
WHERE name = 'model'
Chris
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:E3722206-05B2-4026-9E59-9F863C75B73E@.microsoft.com...
> You can probably use the Model database's SysDatabases.FileName to get the
> path.
> "Chris" wrote:
>|||You might want to research what database you are currently connected to.
Just checking a different database might not get you really what you want.
set nocount on
create table #output
(
spid int,
status varchar(100),
login varchar(100),
hostname varchar(100),
blkby varchar(10),
dbname varchar(30),
command varchar(50),
cputime int,
diskio int,
lastbatch varchar(20),
programname varchar(200),
spid2 int
)
insert into #output exec sp_who2
set nocount off
select
ltrim(rtrim(dbname))
from
#output
where
spid = @.@.spid
drop table #output