Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 4th, 2004, 05:24 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Querying a textfile in QA

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

Reply With Quote
  #2 (permalink)  
Old October 4th, 2004, 06:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old October 4th, 2004, 09:36 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old October 4th, 2004, 10:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old October 4th, 2004, 10:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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'
Reply With Quote
  #6 (permalink)  
Old October 5th, 2004, 06:17 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #7 (permalink)  
Old October 5th, 2004, 08:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old October 5th, 2004, 11:20 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #9 (permalink)  
Old October 5th, 2004, 11:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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...
Reply With Quote
  #10 (permalink)  
Old October 6th, 2004, 10:56 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL QA Analyzer error Adam H-W SQL Server 2000 4 July 14th, 2005 06:16 AM
Appending to a TextFile jfleming SQL Server DTS 3 June 16th, 2004 09:45 AM
Works in QA but not on ASP page rstelma SQL Server 2000 1 October 10th, 2003 04:05 PM
Importing a TextFile, best way ioates SQL Server 2000 1 June 27th, 2003 08:24 AM
Debugging in SQL 2000 QA chrislepingwell SQL Server 2000 3 June 25th, 2003 01:09 PM



All times are GMT -4. The time now is 12:31 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.