|
| Excel as a Back-End to a data table |
| Iniciado por guest, 08,ene. 2018 19:07 - 5 respuestas |
| |
| | | |
|
| |
| Publicado el 08,enero 2018 - 19:07 |
I need to load data from a spreadsheet into a data table.
I created a table in the Analysis that points to the spreadsheet
and I created a query to preprocess (clean up) the data and
I use this query to update the SQL data table (also declared
in the analysis). I do it all in Windev.
My problem is that on some files (the spreadsheet format
is the same juts new data) fail with no logical reason while
others work fine. If I take a smaller version of the file usually
works. I can't find the solution.
Is anyone out there using a similar approach with Excel
and can point me in the right direction?
THNX !!
Joe Maldo |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,enero 2018 - 19:33 |
Hi Joe
FAILS....
How?
On what line of code?
What is the file like (name, size, number of lines)?
... |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,enero 2018 - 23:54 |
This is a query that displays on a table.
When I run the query on a large file, 207MB it crashes.
I will appreciate any help/direction that you can provide!
This is the error detail:
The 'TABLE_QRY_IMP_IIPAS_REV' control cannot be initialized, the following HFSQL error occurred:
=============================
OLE DB access error.
Error Number = 170129
Failure opening file.
=============================
System Error Details:
Description = Class not registered
Source = Provider
Help Context = 1240640 (0x12ee40)
Error Number = -2147221164 (0x80040154)
Native Error Number = -2147221164 (0x80040154)
----- Technical Information -----
Project : BVS11
What happened?
The 'TABLE_QRY_IMP_IIPAS_REV' control cannot be initialized, the following HFSQL error occurred:
=============================
OLE DB access error.
Error Number = 170129
Failure opening file.
=============================
Error code: 10085
Level: fatal error (EL_FATAL)
WD55 error code: 3001
System error message:
Description = Class not registered
Source = Provider
Help Context = 1240640 (0x12ee40)
Error Number = -2147221164 (0x80040154)
Native Error Number = -2147221164 (0x80040154)
Dump of the error of 'wd200obj.dll' module (20.0.373.11).
Identifier of detailed information (.err): 10085
Debugging information:
Query before correction:
Text of the query: SELECT DISTINCT `IIPAS_REV$`.`EXTR_FINC_ARNG_CD` AS `EXTR_FINC_ARNG_CD` , `IIPAS_REV$`.`pri_chnl_nm` AS `pri_chnl_nm` , `IIPAS_REV$`.`ModifiedPolicyNumber` AS `ModifiedPolicyNumber` , `IIPAS_REV$`.`Policy_Customer` AS `Policy_Customer` , `IIPAS_REV$`.`GL_PROD` AS `GL_PROD` , `IIPAS_REV$`.`PRDCT_NM` AS `PRDCT_NM` , `IIPAS_REV$`.`PRI_CHNL_CD` AS `PRI_CHNL_CD`
FROM `IIPAS_REV$`
WHERE ( ( `IIPAS_REV$`.`Policy_Customer` <> ) AND ( `IIPAS_REV$`.`EXTR_FINC_ARNG_CD` = ) )
ORDER BY `IIPAS_REV$`.`ModifiedPolicyNumber` , `IIPAS_REV$`.`GL_PROD` ,1,2,4,6,7
Number of Parameters: =2
Parameter 0 : WLanguage Type: 110 = <(null)>
Parameter 1 : WLanguage Type: 110 =
Text of the query: SELECT DISTINCT `IIPAS_REV$`.`EXTR_FINC_ARNG_CD` AS `EXTR_FINC_ARNG_CD` , `IIPAS_REV$`.`pri_chnl_nm` AS `pri_chnl_nm` , `IIPAS_REV$`.`ModifiedPolicyNumber` AS `ModifiedPolicyNumber` , `IIPAS_REV$`.`Policy_Customer` AS `Policy_Customer` , `IIPAS_REV$`.`GL_PROD` AS `GL_PROD` , `IIPAS_REV$`.`PRDCT_NM` AS `PRDCT_NM` , `IIPAS_REV$`.`PRI_CHNL_CD` AS `PRI_CHNL_CD`
FROM `IIPAS_REV$`
WHERE ( ( `IIPAS_REV$`.`Policy_Customer` <> ? ) AND ( `IIPAS_REV$`.`EXTR_FINC_ARNG_CD` = ? ) )
ORDER BY `IIPAS_REV$`.`ModifiedPolicyNumber` , `IIPAS_REV$`.`GL_PROD` ,1,2,4,6,7
Number of Parameters: 2
Parameter 0
ADO type: : <8> =
Value: : <(null)>
Parameter 1
ADO type: : <8> =
Value: :
IEWDOLDB=111.6
Module=
Version=<20.0.23.0>
[Current Recordset parameters]
Wanted = LockType = , Location = , Type = , Capacities =
Got = LockType = , Location = , Type = , Capacities =
Error defining options of ADO object.
Cursor Location: <2>
Cursor Type: <1>
Lock Type: <3>
Provider: Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;
Data source: C:\BIVSER\DATA_LOAD\REV_201801.xlsx
Connection time-out: 30
Command time-out: 30
Unicode supported: 1
Page code of WL: 1252
Page code of the connection: UTF-16
MDAC Version = <6.1.7601.17514>.
[Data format]
Query Parameter Checked = <0> = <>
Query Parameter Needing conversion = <0> = <>
DecimalSeparator =
DateFormat =
[Cursor settings Match]
LockType = , Location = , Type = , Capacities =
LockType = , Location = , Type = , Capacities =
LockType = , Location = , Type = , Capacities =
Additional Information:
EIT_NATIVECODE : <170129>
EIT_ADOCODE : <-2147221164>
EIT_BASECODE : <-2147221164>
EIT_ODBCDESCRIPTION :
EIT_LOGICALTABLENAME : |
| |
| |
| | | |
|
| | |
| |
| Publicado el 09,enero 2018 - 10:59 |
Hi Joe,
In the past, I tried your approach with Pervasive SQL (Btrieve) tables and WINDEV.
I imported the tables in the Analysis, Created Queries and tried to use tables etc.
It Worked fine with small tables but it "randomly failed" with large tables.
After sending some emails to PCSoft support I gave up an tried another approach that worked ALL the time...
Here is what I do:
1. I create an ODBC data source (myODBC), pointing to my data.
2. I use the code LIKE THIS:
ResSQL is boolean
SQLCode is string
SQLCode =
[
SELECT Categ.Seq, Categ.Descr
FROM Categ;
]
SourceName = "myODBC"
ConnectionNum is int = SQLConnect(SourceName, "", "", "", "ODBC")
IF ConnectionNum <> 0 THEN
// The connection was successful
// Run the query and retrieve the result line by line
ResSQL=SQLExec(SQLCode, "QRY1")
IF ResSQL=True THEN
WHILE SQLFetch("QRY1") = 0 // There is still another line to read
mySeqVariable=SQLGetCol("QRY1", 1)
myDescriptionVariable=SQLGetCol("QRY1", 2)
// and now add code to fill a table etc
END
END
END
The code is probably "scrabbled" on the post, because of HTML, but I think you get the idea.
Just don't forget that SQLGetCol is very "sensitive".
Everything must be in the "order" you use in your SQL query.
Steven Sitas
www.alpha360.biz |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,enero 2018 - 03:18 |
Steven,
I really appreciate your feedback.
I will try this approach right away.
The interesting thing about this is that I load three different
files and I use the exact same method, two work perfectly
and this one flops, I can't wrap my head around it . . .
Regards,
JoeMaldo |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,enero 2018 - 04:36 |
Hello Joe
Re the file you having a problem with, it may be useful to have a look at the "xls" functions.
They will allow you to open the Excel file and you can then construct a looping process to read each row, column and cell.
This may be useful to find the problem as you can trace the process using the debugger or the process you build could work as an alternative method to extract the data.
Regards
Al |
| |
| |
| | | |
|
| | | | |
| | |
|