Querry Csv File

[vladcbv]

Hy All,

I didn't know where to put this thread o I hope this topic is ok

I have Avaya IPOffice and Voicemail Pro 9.1.8 running on Windows (not yet an SQL thing but I'm just getting started )

For you who don't know the products above, I have a PBX and a Windows Serveice that allows me to integrate PBX with a database - i am testing an IVR sistem where the client calls, inputs a n ID , it is being searched in the database and then it does a querry based on another input.
I have tested with .mdb, .accdb and with SQL server and it works.

I also notice that VoicemailPro on Winodws allows me to use Microsoft OLE DB Provider ODBC Driver so I want to use a .csv or .xls file instead of a database.

I have created a DataSource under ODBC Data SOurce Admin (32-bit) -> System DSN -> Driver to Microsoft Excel.

And now I am doing a querry:

Select NrAb,IVechi
From [Sheet1$]
WHERE (NrAB= '$KEY');

$KEY - is the input value

The error I get is this :

07/10 10:28:29.774 vmprov5s (17,2) 65c, 6a0: Database Error: TmodClrObjects:vtExecute[721]: Failed to "execute the query "Select NrAb,IVechi

From [Sheet1$]

WHERE (NrAB= '123');"", error -2147217913 [0x80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147217913 [80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression., native number -3030 - 22005].
07/10 10:28:29.774 vmprov5s (09,2) 65c, 6a0: Session: 00000064 - Database execute failed: TmodClrObjects:vtExecute[721]: Failed to "execute the query "Select NrAb,IVechi

From [Sheet1$]

WHERE (NrAB= '123');"", error -2147217913 [0x80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147217913 [80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression., native number -3030 - 22005].


It is prety self explanatory for me the Data type mistmatch error, but may there is something else here..

Any ideas, Any hints?

Thank you!

 

[SkipVought]

HI,

have created a DataSource under ODBC Data SOurce Admin (32-bit) -> System DSN -> Driver to Microsoft

I've always used a User DSN in the ODBC Data Source Adminstrator (32-bit) for Excel Files.

You appear to have opened the .csv using Excel and SaveAs as an Excel workbook, from the SQL that you posted, so this Excel sheet is your table in the workbook database, yes?

[pre]
Select NrAb,IVechi
From [Sheet1$]
WHERE (NrAB= '$KEY');
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vladcbv]

Yes,

That is correct.

 

[vladcbv]

Ok,

So I used a User DSN and no more erros.. now, I see that the information is stored in my variables in an unknown format for me.
After the Select querry Voicemail is storing into $DBD0 - info from 1st column, $DBD1- info from second column.. and so on ..
When I check the information stored it looks like this :
For a querry like

Select *
From [Sheet1$]
WHERE ([NrAB]= 1001);

they return:
$DBD0 $DBD1 $DBD2 $DBD3
1001.0, 1001.1, 1001.1, 1001.3

now i'm really lost ..

 

[SkipVought]

At this point since your query works, why not post in an Avaya forum.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vladcbv]

Because I wanted to jump to my next problem..

After the querry I want to modify those DBD values and then do an INSERT..

When I do the insert this is what i get :


15/10 15:14:03.416 vmprov5s (17,2) 764,1260: Database Error: TmodClrObjects:vtExecute[721]: Failed to "execute the query "INSERT INTO [Sheet1$]

(DataInreg, INou, IVechi, NrAB, Numar )

VALUES (1001.0,1001.1,1001.1,1001.3,801);"", error -2147467259 [0x80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147467259 [80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query., native number -3035 - S1000].
15/10 15:14:03.416 vmprov5s (09,2) 764,1260: Session: 00000087 - Database execute failed: TmodClrObjects:vtExecute[721]: Failed to "execute the query "INSERT INTO [Sheet1$]

(DataInreg, INou, IVechi, NrAB, Numar )

VALUES (1001.0,1001.1,1001.1,1001.3,801);"", error -2147467259 [0x80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147467259 [80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query., native number -3035 - S1000].
15/1

 

[vladcbv]

PS the querry works in the sense that i don't receive an error. but the values that are stored are incorrect..

 

[SkipVought]

but the values stored are incorrect"

What exact values did you have in your variables, and what were the exact incorrect values that resulted in Sheet1?



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

>i don't receive an error
What you show is an error.

Check whether the system account running MSSQL Server (typically that's NETWORK SERVICE) has write access to the excel file.

Bye, Olaf.