Read MS-Excel from PB - my mySql-queries dont work

Just starting out? Need help? Post your questions and find answers here.
kimio
New User
New User
Posts: 8
Joined: Sat Jul 19, 2003 10:37 am
Location: heidelberg / germany

Read MS-Excel from PB - my mySql-queries dont work

Post by kimio »

hello!

i am just trying to read an excel-sheet (name: "kv.xls", first table = "Tabelle1" ) (MS office 2000), but it does not work.

i can open the 'database', but my select-query is not accepted.

is there anyone who has an idea?

thanks a lot!

kimio

----------------------------------------------------


pw.s:pw=""
user.s:user=""
db_name.s:db_name="kv"
odbc.s:odbc="kv" ;ODBC-driver
conn.b:conn=4
;Gosub init_db
Gosub open_db

query.s = "Select * FROM Tabelle1"
Gosub db_query

While NextDatabaseRow() <> 0 ; gefundene einträge abarbeiten

sa$=GetDatabaseString(0) ;reihe 0 = pho NN
;sb$=GetDatabaseString(1) ;reihe 1 = pho VN+NN
;sc$=GetDatabaseString(2) ;reihe 2 = pho Fa.
Debug sa$
;Debug sb$
;Debug sc$

Wend



;CloseDatabase(3)
CloseDatabase(4)

End


; >>>>>>>> subroutinen

db_query: ;führt in geöffneter db-verbindung die db-query query.s aus
If DatabaseQuery(query.s) = 0
MessageRequester("db_query:", "fehlgeschlagen",#PB_MessageRequester_Ok)
End
EndIf
Return


open_db: ;subroutine - öffnet datenbankverbindung
; pw.s -> password
; user.s -> user
; db_name.s -> db.name
; odbc.s -> odbc-name
; conn.b -> nummer der db-verbindung

Gosub init_db ;initialisieren (auswählen der db)

If OpenDatabase(conn, db_name, user,pw) = 0
MessageRequester("OpenDB fehlgeschlagen", "Fehler",#PB_MessageRequester_Ok)
EndIf

Return

init_db:
InitDatabase()
If ExamineDatabaseDrivers()
While NextDatabaseDriver()
If DatabaseDriverName() = odbc
Base_OK.b = 1
EndIf
Wend
EndIf
If Base_OK = 0
MessageRequester("ODBC-driver:", "not found",#PB_MessageRequester_Ok)
EndIf
Return
dmoc
Enthusiast
Enthusiast
Posts: 739
Joined: Sat Apr 26, 2003 12:40 am

Post by dmoc »

Long time since I have done any SQL but is it case-sensitive? If so then maybe "SELECT..."?
User avatar
Rings
Moderator
Moderator
Posts: 1435
Joined: Sat Apr 26, 2003 1:11 am

Post by Rings »

try tablename with 'tabelle1$'
SPAMINATOR NR.1
kimio
New User
New User
Posts: 8
Joined: Sat Jul 19, 2003 10:37 am
Location: heidelberg / germany

ODBC: do i use normal SQL-syntax to communicate excelsheet?

Post by kimio »

thanks a lot!

ok, the SELECT statement has to be written in upper cases....

and the statement doesnt need a $ behind 'tabelle1'. this is because the whole query is a string, so you dont have to use a '$' behind the table-name (i have also tried it with $ behind tabelle1).

my query still does not work.

my question is now:
if you use the ODBC-interface for communication with an excel-sheet do i use common SQL-syntax? i have not found any hint for this, only for opening a connection to the excel-sheet via ODBC

thanks in advance!

kimio
dmoc
Enthusiast
Enthusiast
Posts: 739
Joined: Sat Apr 26, 2003 12:40 am

Post by dmoc »

What is the value of the result? Maybe it indicates an SQL/ODBC error. Have you actually set up an ODBC data source to the sheet? Have you tried accessing it via MS Query?
Manolo
User
User
Posts: 75
Joined: Fri Apr 25, 2003 7:06 pm
Location: Spain

Post by Manolo »

Hi,
This is you code:
query.s = "Select * FROM Tabelle1"

nad this is my code:

query.s = "Select * FROM Tabelle1;"

ByBy
Manolo
Return to the forum
dmoc
Enthusiast
Enthusiast
Posts: 739
Joined: Sat Apr 26, 2003 12:40 am

Post by dmoc »

I had a go at this last night. I didn't need the semi-colon but I did need to name the table for the Excel ODBC driver to see it. After this it worked fine.

Win98se, Off97, ODBC32 3.5
kimio
New User
New User
Posts: 8
Joined: Sat Jul 19, 2003 10:37 am
Location: heidelberg / germany

How to read Excel....

Post by kimio »

ok....
my ODBC looks like:

DSN (data source name): kv (excel-table='kv.xls'; sheet='Tabelle1', i use excel 2000)

version: excel 97-2000

path to excel-folder: ok

write protected

look at my example on top: the database-connection is opened correctly (sub open_db:), i get no error-message.

can you perhaops show me a working pb-code (opendatabase and sql-query)?


thanks a lot!

kimio
dmoc
Enthusiast
Enthusiast
Posts: 739
Joined: Sat Apr 26, 2003 12:40 am

Post by dmoc »

Kimio, I think all you need to do is select the region on the worksheet containing the table and enter a name for the table in the edit box on the left of the toolbar. Then use this name in your SQL statement, ie, XXX in "... FROM XXX".
Post Reply