Reading records in a CSV file using Query Analyser

[QLearnerNow]

Hi Guys,

I want to be able to write an SQL Select statement that returns records from Table1 that are not present in rec.csv file. how can I do this using Query Analyser? is this possible?

Thanking you

QLearnerNow

 

[simma]

Hi
If you are using SQL2000 then right click on resluts window,select save as and save as .csv file in any location
Good Luck

 

[QLearnerNow]

wrong answer simma

 

[JamesLean]

You can't query a csv file directly. You would need to import it into the DB first (use BULK INSERT or DTS).

--James

 

[QLearnerNow]

what about the OPENDATASOURCE T-SQL command James. I dont have to use a csv file I can also use an excel file.

I have tried using the OPENDATASOURCE command but even that doesnt work says cannot find object

 

[SQLSister]

YOu can create a linked server with an Excel file. From Books Online:
"To create a linked server against an Excel spreadsheet:

The Microsoft OLE DB Provider for Jet 4.0 can be used to access Microsoft Excel spreadsheets.

To create a linked server that accesses an Excel spreadsheet, use the format of this example.
sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO

To access data from an Excel spreadsheet, associate a range of cells with a name. A named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up in the previous example.
SELECT *
FROM EXCEL...SalesData
GO

When you insert a row into a named range of cells, the row will be added after the last row that is part of the named range of cells. Thus, if you want to insert row rA after the column heading, associate the column heading cells with a name and use that name as the table name. The range of cells will grow automatically as rows are inserted."

 

[QLearnerNow]

Here is what I tried SQLSister:

sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\Names.xls', NULL, N'Excel 8.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO


SELECT *
FROM EXCEL...Trust$
GO

Here is the error Message I get :

Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
The server 'Excel' already exists.

(0 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)

Server: Msg 7314, Level 16, State 1, Line 3
OLE DB provider 'EXCEL' does not contain table 'Trust$'. 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='EXCEL', TableName='Trust$'].

Here is my excel data

ID Name
1 Mike
2 Nitish
3 Peter
4 Scott
5 Russell

Ive named the range Trust.

Don't know why it dont work.

The server 'Excel' already exists.
this error comes only because I created it on the first attempt while trying this but i dont think see it as a hinderance to the rest of the T-SQL.

Thanks for your help already. Hope you can help me more with regard to this.

Thanking you,
QLearnerNow

 

[SQLSister]

if Excel already exists, did you try just running the select statement without the others?

SELECT *
FROM EXCEL...Trust$

What error message or results do you get?

 

[QLearnerNow]

This is what I tried SQL Sister

SELECT *
FROM EXCEL...Trust$

This is the error message I get

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'EXCEL' does not contain table 'Trust$'. 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='EXCEL', TableName='Trust$'].

I just dont get it.

Thanks for your help

QLearnerNow

 

[QLearnerNow]

SqlSister I just tried this

SELECT *
FROM OPENQUERY(Serv, 'spColumns Comp1')

and I get this error
Server 'Serv' is not configured for DATA ACCESS.

Serv is the name of the SqlServer