Is this possible? I have a textfile C:\File.csv that I am trying to query using Query Analyser, with the following syntax.

--Excel Driver
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=C:\File.csv;Extended properties=Excel 8.0')...Xactions

or

--Text Driver
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;','select * from File.csv')


--Error Message with Jet Excel
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

--Error message with textfile file
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] The Microsoft Jet database engine could not find the object 'File.csv'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].


I have used the following websites for reference
http://www.databasejournal.com/featu...0894_3331881_3
Books Online

Any help or pointers appreciated on this
Regards Dan

Use this syntax for the Jet driver:

OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\;Extended Properties="Text;HDR=No;FMT=Delimited"')...FILE#CS V

A few things to note here:
1. the Data Source is the directory that contains the file
2. Extended Properties are Text... not Excel...
3. The Filename is accessed using a # instad of . to separate the extension

You can use a similar syntax with OPENROWSET.

If you're going to be doing this a lot then you can set up a linked server which makes the query much simpler:

From Enterprise Manager:
 Security > Linked Servers > New Linked Server
 General tab inputs:
 Linked Server : type anything, e.g. TEXTTEST
 Server type - click 'other data source'
 Provider : choose Microsoft Jet 4.0 OLEDB Provider
 Product Name : leave blank
 Data Source : type in the directory, e.g. C: Provider String : type in Text;HDR=No;FMT=Delimited

 leave other tabs with default values

 then SQL becomes:

SELECT * FROM TEXTTEST...FILE#CSV

hth
Phil

Phil
thanks for a detailed reply. I am sorry I am being stupid here, I have tried your suggestion in setting up a linked server (I already have one setup to Oracle, so I am a fairly familiar with this)

My Path string I have setup I tried this first
Data Source =C:\;Provider String = Text;HDR=No;FMT=Delimited

I then found this link on textfile linked servers
http://msdn.microsoft.com/library/de...qd_12_6a44.asp

And tried this
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\', NULL, 'Text'

SELECT * FROM txtsrv...file#csv

--Return Error
OLE DB provider 'txtsrv' does not contain table 'file#csv'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='txtsrv', TableName='file#csv'].

there is definately a file on c:\ root on my PC called file.csv that I can access and open

My overall aim, is that the textfile name and location can change (eg the user will browse to a file using VB .Net). This csv file I then want to insert into a temp table within SQL to produce a summary of the data.

1) Does it matter which driver to use for a CSV file text or Excel?

Any thoughts regards
Ian

No problem, this can be tricky to do first time as the syntax has to be just right.

Using EM did you enter all that "Data Source =C:\;Provider String = Text;HDR=No;FMT=Delimited" in the Data Source box, or did you split it up across the Data Source and Provider String boxes? Maybe a daft question, but I'm just wondering why you had the ; in between.

Using sp_addlinkedserver the last param needs to be 'Text;HDR=No;FMT=Delimited'. The other params are fine as you have them.

If the directory is going to change then using a linked server isn't going to be any use, you'll have to use OPENDATASOURCE or OPENROWSET.

BTW are you Dan or Ian
hth
Phil

One other thing - are you accessing the SQL Server using your windows login? If not it could be a permissions issue so you could try executing:
EXEC sp_addlinkedsrvlogin 'txtsrv', 'false', 'Domain\User', 'User', 'Password'

Phil, it's Dan BTW, don't where that came from, anyway **BINGO**

Two areas were I was wrong

1) I was creating the linked server on the database server and the textfiles were on my local C Drive (big mistake), the linked server was looking on the root of the server drive.

2) This realised creating the linked server with the following setup
 Linked Server = TEXTTEST
 Provider = Microsoft Jet 4.0 OLEDB Provider
 Product Name = Jet 4.0
 Data Source = C: Provider String = text;HDR=NO;FMT=Delimited

The above as you suggested initially, using the linked server use
SELECT * FROM TEXTTEST...file#csv
SELECT * FROM OPENQUERY(TEXTTEST,'SELECT * FROM file#txt')

Without a linked server

SELECT * FROM OPENDATASOURCE('Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=C:\;Extended Properties="text;HDR=NO;FMT=Delimited"')...file#cs v


Get textfile into a temp table

SELECT * INTO #tmpFile
FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;','select * from file.txt')

Am I right in saying that you cannot change/reset the path (Data source) once the linked server has been setup?

Once again thanks for your pointers

Hi Dan, glad you got it working now. BTW you signed the 2nd post "Ian" that's why I asked.

You can change the path of a linked server from Enterprise Manager by editing its properties, but I don't know any way to do it from T-SQL, except to drop (sp_dropserver) and re-create the linked server with the new path. Seems easier to just use opendatasource or openrowset...

rgds
Phil

Yes was hoping for a T-SQL way to change the path, the drop and recreate had crossed my mind, but the question 'am I implementing this the right way' came into my head, I seem to be going a long way to get a text file selected by the user on another machine into SQL.

The only problem with 'openDataSource or openRowSet' is that I cannot specify the full path of the filename, SQL Server will be looking for it's C Drive not the users C Drive. I am now questioning the implementation of this.

To recap, The user(s) will browse to a text file (vb.net file dialog box), I want to import this textfile into SQL and link to some other tables, by passing the filename and path to an SP that would drag the file in.

I am now thinking of using datasets/datatables and doing the SQL in the app and not SQL Server, by linking them.

You could always copy the file from the users PC to a public share on the network, somewhere that both the SQL box/user and the app user have access to, and use that sharename as your linked server path. Then you wouldn't need to keep changing the path.

It's probably easier though to do it in the app...

Phil
Yes I like the above idea you suggested all is in place.
--SP
CREATE PROCEDURE dbo.importFile @fileName as varchar(100)
AS
SET @fileName = REPLACE(@fileName, '.', '#')
EXEC('SELECT * INTO ##tmp FROM txtSvr..."' + @fileName + '"')
GO

I am now in familiar ground, so hopefully down hill from now on

Regards