Newbie - Help Connecting To Excel File

[darylr]

Hi,

 

I am currently evaluating Voiceguide using a Dialogic D4PCIUFW card.

 

I am able to connect and play the intro wave files. I am trying to enter a 2 digit # and the look up that entry in an Excel spreadsheet in order to extract other data. I have read the forum and saw the Excel Test sample script.

 

I put the spreadsheet path and file name in the Database line and the sheet$rowcol:row:col entry in the sql field, but I keep getting errors saying the ADA.NET provide could not be found. I also tried the OLEDB method described in one of the forum topics, but that failed too.

 

I am running a 32 bit windows 7 SP1 fresh installation. After getting the error, I load MS office 2007 Pro to put Excel on the machine but that didn't help.

 

I am attaching a zip file with my test.vgs script, the excel file, audio files, and a folder with the logs.

 

I'm sure there is something simple I'm missing, but I cannot figure it out. I'm a telecom guy, not a windows programmer.

 

Thanks!

 

Daryl

 

TEST.zip

[darylr]

I forgot to mention, I also tried the example Excel Test script and received the same error. Has to be something not set up in the software.

 

Thanks!

 

Daryl

[SupportTeam]

In VoiceGuide v7 please use the OLEDB drivers to read data from Excel.

 

If the Microsoft.ACE.OLEDB.12.0 driver is not installed on your machine (and it most probably isn't as it isn't installed with Windows by default) then you will see an error like this in vgEngine trace file:

 

105356.766 23 1 1 ERROR v7.5.5872.17996 (29-Jan-16 9:59:53.02) ConnectAndRun_OleDb init1 : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

If you get the error above then you need to download the MS Office "Data Connectivity Components".

 

As VoiceGuide is 32-bit software (ie: 'x86' software) then you need the 32-bit versions of the "Data Connectivity Components". They can be obtained here:

 

https://www.microsoft.com/en-us/download/details.aspx?id=23734

 

 

We ran your script on our test system as we got this error:

 

110516.089 23 1 1 dboledb connectionOleDbConn open call [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test\test.xls;]
110516.126 23 1 1 ERROR v7.5.5872.17996 (29-Jan-16 9:59:53.02) ConnectAndRun_OleDb init1 : Unrecognized database format 'c:\test\test.xls'.

So to fix this on our test machine we just saved the .xls file as a .xlsx and changed the connection string to be:

 

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\test\test.xlsx; Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1";

 

(the ADO.NET Data Provider text box is left blank)

 

 

But you may be able to read in your .xls without saving it in new format - please see the various connection string for the other/older Excel versions here:

 

https://www.connectionstrings.com/ace-oledb-12-0/

 

 

We would however recommend using the current .xlsx format. Note that in the Connection string above, all the Extended Properties (including HDR=NO;IMEX=1) are important and need to be included.

 

 

With the newer versions, the 'SQL Query' to read the data from the Excel will need to be changed as well.

 

It now needs to be:

SELECT * FROM [Locations$A$RV[LocCode]$RV[LocCode]]

that should now work.

 

Below is vgEngine trace from our system showing data retrieved from your Excel, and shows what $RVs were created.

114611.825  50   1   1       dboledb ConnectAndRun_OleDb begin. Create connection: [Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\test\test.xlsx; Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1";]
114611.825  50   1   1       dboledb connectionOleDbConn open call [Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\test\test.xlsx; Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1";]
114611.838  50   1   1       dboledb connectionOleDbConn open returned.
114611.838  50   1   1       dboledb m_strSQL=[SELECT * FROM [Locations$A14:D14]
]
114611.838  50   1   1       dboledb DataAdapter.Fill init
114611.838  50   1   1       dboledb DataAdapter.Fill call.
114611.840  50   1   1       dboledb DataAdapter.Fill returned. tsFillTime=00:00:00.0020001
114611.840  50   1   1       db    xmlData=<vgmDBQuery_OleDb>  <Table>    <F1>14</F1>    <F3>CORNING</F3>    <F4>c:\test\audio\cng.wav</F4>  </Table></vgmDBQuery_OleDb>
114611.840  50   1   1       dbread loop iColIdx=0, sBlob_DbColumn=[], strField_Name=[F1]
114611.840  50   1   1       rv    add   LocData_F1|14
114611.841  50   1   1       rv    add   LocData_1|14
114611.841  50   1   1       rv    add   LocData_F1_1|14
114611.841  50   1   1       rv    add   LocData_1_1|14
114611.841  50   1   1       dbread loop iColIdx=1, sBlob_DbColumn=[], strField_Name=[F2]
114611.841  50   1   1       rv    add   LocData_F2|
114611.841  50   1   1       rv    add   LocData_2|
114611.841  50   1   1       rv    add   LocData_F2_1|
114611.841  50   1   1       rv    add   LocData_2_1|
114611.841  50   1   1       dbread loop iColIdx=2, sBlob_DbColumn=[], strField_Name=[F3]
114611.841  50   1   1       rv    add   LocData_F3|CORNING
114611.841  50   1   1       rv    add   LocData_3|CORNING
114611.841  50   1   1       rv    add   LocData_F3_1|CORNING
114611.841  50   1   1       rv    add   LocData_3_1|CORNING
114611.841  50   1   1       dbread loop iColIdx=3, sBlob_DbColumn=[], strField_Name=[F4]
114611.841  50   1   1       rv    add   LocData_F4|c:\test\audio\cng.wav
114611.841  50   1   1       rv cleaned as ini_moduleGetNbrs_DoNotShowNumbersLongerThenLen=8
114611.842  50   1   1       rv    add   LocData_4|c:\test\audio\cng.wav
114611.842  50   1   1       rv cleaned as ini_moduleGetNbrs_DoNotShowNumbersLongerThenLen=8
114611.842  50   1   1       rv    add   LocData_F4_1|c:\test\audio\cng.wav
114611.842  50   1   1       rv cleaned as ini_moduleGetNbrs_DoNotShowNumbersLongerThenLen=8
114611.842  50   1   1       rv    add   LocData_4_1|c:\test\audio\cng.wav
114611.842  50   1   1       rv cleaned as ini_moduleGetNbrs_DoNotShowNumbersLongerThenLen=8
114611.842  50   1   1       rv    add   LocData_RowCount|1
114611.842  50   1   1       dbcompleted.  SqlQueryType=READER, RowsCount=1, sRv=
114611.842  50   1   1       dbcompleted. iRunWait=1, WavPlayWasStarted=0, WavPlayHasNowFinished=0
114611.842  50   1   1       dbcompleted. iRowsAffected=1
114611.842  50   1   1       FindNextVgmTitleInPathList: next module title is=[LocMatch]
114611.842  50   1   1       RunModule_AddToTaskQueue [Evaluate,[LocMatch],143]
114611.842  50   1   1       q_scr +     evScriptEvent 9800 CMD_RUN_SAME_MODULE_AGAIN
114611.842   6   1   1       q_scr run   evScriptEvent sCode=[CMD_RUN_SAME_MODULE_AGAIN] iActionID=0, crn=0 [0|0|0|0|0][|||||] 00:00:00 max:3|00:00:00.0850111
114611.842   6   1   1       se    CMD_RUN_SAME_MODULE_AGAIN 9800  0|0|0  || LineState=LS_DBQUERY
114611.842   6   1   1 t     timer clear (force=False)
114611.843   6   1   1       RunModule start
114611.843   6   1   1       RunModule start Evaluate, [LocMatch], iModuleIdx=143, previous: vgm=143, vgs=3:3

[darylr]

Great, thank you for the pointers! I actually started out with the xlsx version but when it didn't work, I thought maybe the older format would be better. Since you got my script to work, I'm confident I'll get it to work on my system as well. I'll incorporate the changes you provided and let you know how it turns out.

 

Much appreciated!!!!

 

Daryl

[darylr]

Everything ran perfectly after adding the data connectivity components package and changing the connection string and query statements. Thank you for the help!!!

 

Daryl

[darylr]

Hello again. Our Voice Guide service has been working great for the past 18 months. However, I was just informed that the I need to upgrade the data connectivity components from office 2007 to at least office 2010, due the 2007 being end of life and not getting security updates.

 

Is the 32-bit 2010 Access database engine compatible with Voice Guide's SQL query?

 

https://www.microsoft.com/en-us/download/details.aspx?id=13255

 

I wanted to ask before I tried to install it.

 

Thanks!

 

Daryl

[SupportTeam]

Any of the versions of Microsoft's "Data Connectivity Components" can be used.

 

From previous traces in this thread it looks like this version of OLEDB drivers is currently used by this system to connect to the Excel spreadsheet: Microsoft.ACE.OLEDB.12.0

 

So it looks like this system will be getting updated to latest Service Pack of the same driver version (?).