Jump to content

Dana

Active Members
  • Posts

    303
  • Joined

  • Last visited

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

Dana's Achievements

  1. But-- I should have checked this before-- it seems to be a Windows display issue, as the DOS dir shows the date is in fact being set to 1903. If I use an "allowed" date (e.g. 1999) it displays correctly in Explorer.
  2. Just being silly, really. The code snippet is just test code to demonstrate the issue. The real program is a flight data analysis app which reads an airport location database from a text file and displays the database file date at program startup. It then uses the database to identify stops in a GPS track log. If the file is missing the program creates it, padding it with one line of data, that line being the location of the Wright Brothers' first successful flight... so just for fun I used the date and time of that flight. That date will of course change the first time the file is edited with the user's real data.
  3. I'm seeing some weirdness with FileSetTime. I have: Global $customapfile = @ScriptDir & "\databases\customap.txt" FileWrite($customapfile, "Loc Id|Name|Elevation|ARP Latitude DD|ARP Longitude DD" & @CRLF & "F001|Kill Devil Hill|90|36.014614|-75.667966" & @CRLF) FileSetTime($customapfile, "19031217103500", 1) ; set created time MsgBox(4096, "debug", "type 1 " & FileGetTime($customapfile, 0, 1)) FileSetTime($customapfile, "19031217103500", 0) ; set modified time MsgBox(4096, "debug", "type 0 " & FileGetTime($customapfile, 0, 1)) FileSetTime($customapfile, "19031217103500", 2) ; set accessed time MsgBox(4096, "debug", "type 2 " & FileGetTime($customapfile, 0, 1)) FileSetTime($customapfile, "19031217103500", 0) ; set modified time MsgBox(4096, "debug", "type 1 " & FileGetTime($customapfile, 0, 1)) ;FileDelete($customapfile) The first round (type 1) sets sets today's date in the windows explorer "date modified" column and the 1903 date in file properties dialog "created", today in "modified", FileGetTime shows today. The second round (type 0) blanks out the date in the explorer date modified column but shows the 1903 date in properties "created" and "modified", FileGetTime shows 1903. The third round (type 2), the date modified column is stall blank but still shows the 1903 date in both properties and FileGetTime. It seems nothing I can do will show the 1903 date in the explorer "modified" column, but a DOS dir command shows the 1903 date. But if I open the file in a text editor and resave it, the correct "modified" date shows in both explorer and autoit. Now I know the helpfile says, "Using a date earlier than 1980-01-01 will have no effect," but it certainly has an effect, just not a predictable one. This is Windows 10 Pro, NTFS.
  4. Wow, thanks! That's a LOT faster... 4 seconds to read 3 lines and 56 seconds to read the entire file, 38 seconds to read a single column. Now to see how it does on a slower computer at home, and work out the best way to extract the 5 columns I need.
  5. I tried to use this but it's very slow, with or without 7za.exe. I have: #include <Array.au3> ;#include <_XLSXReadToArray.au3> #include <xlsxNative.au3> Local $a, $t, $d, $Error, $Extended Global $airportfile = @ScriptDir & "\all-airport-data.xlsx" ; https://adip.faa.gov/publishedAirports/all-airport-data.xlsx $t = TimerInit() ;Global $a = _XLSXReadToArray($airportfile, 0, 0, 0) $a = _xlsx_2Array($airportfile,1,1,3) $error = @error $d = TimerDiff($t) ConsoleWrite("Return = " & $a & "; @Error = " & $Error & @LF) ConsoleWrite("Timer = " & $d / 1000 & " seconds to read file" & @LF) _ArrayDisplay($a) ConsoleWrite("DONE") With the file from the commented link it takes 18 minutes to read the file, even trying to read only three lines. With an abridged version of the file (the original is 17MB, 98 columns and ~20k rows and ) it processes in under a second. XLSXReadToArray takes 24 seconds to read the big file, but for some reason drops the first line unless the file is opened in Excel and resaved first. Ultimately I only need to read 5 columns, but first I need to read the entire first line to get the column names and identify the columns I need.
  6. At home: At work: I looked at that and would have tried it if it was just my own system, but: "Note: Before installing this download, you must first remove any previous versions of the Access Database Engine from your computer by using Add/Remove Programs in the Control Panel. " I don't want to get into that on a user's system. Or maybe I should just try a different approach using sqlite or something.
  7. Tested with three different versions of the connection string. The version that works on my work computer does this at home, and the ADO UDF gives the same error: ObjCreate returned Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\d-m-h\Documents\Danaware\sandbox\all-airport-data-abridged.xlsx;Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1" ado_test.au3 (26) : ==> COM Error intercepted ! err.number is: 0x80020009 err.windescription: Exception occurred. err.description is: Provider cannot be found. It may not be properly installed. err.source is: ADODB.Connection err.helpfile is: C:\windows\HELP\ADO270.CHM err.helpcontext is: 1240655 err.lastdllerror is: 0 err.scriptline is: 26 err.retcode is: 0x800A0E7A oConnection.Open error -2147352567 oConnection.Open returned The other two versions do the same both at work and at home: Provider=MSDASQL;FileDSN=C:\Users\d-m-h\Documents\Danaware\sandbox\all-airport-data-abridged.xlsx;Extended Properties="Excel 8.0 Xml;HDR=NO;IMEX=1" ado_test.au3 (26) : ==> COM Error intercepted ! err.number is: 0x80020009 err.windescription: Exception occurred. err.description is: General error: Invalid file dsn '' err.source is: Microsoft OLE DB Provider for ODBC Drivers err.helpfile is: err.helpcontext is: 0 err.lastdllerror is: 0 err.scriptline is: 26 err.retcode is: 0x80004005 oConnection.Open error -2147352567 oConnection.Open returned ObjCreate returned Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\d-m-h\Documents\Danaware\sandbox\all-airport-data-abridged.xlsx;Extended Properties="Excel 8.0 Xml;HDR=NO;IMEX=1" ado_test.au3 (26) : ==> COM Error intercepted ! err.number is: 0x80020009 err.windescription: Exception occurred. err.description is: Could not find installable ISAM. err.source is: Microsoft JET Database Engine err.helpfile is: err.helpcontext is: 5003170 err.lastdllerror is: 0 err.scriptline is: 26 err.retcode is: 0x80004005 oConnection.Open error -2147352567 oConnection.Open returned 3.3.14.2
  8. Thanks, I will look into it. I tried _xlsx_2Array again, I could make it work with but it's way too slow for what I need.
  9. Same results on home PC. ConsoleWrite("ObjCreate returned " & isObj($oConnection) & @crlf) returns 1
  10. Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object if @error Then ConsoleWrite("ObjCreate error " & @error & @crlf) ConsoleWrite("ObjCreate returned " & $oConnection & @crlf) gives no @error, but $oConnection appears empty; still, the subsequent $oConnection.Open line works (on the work computer with the ACE provider, but not with the MSDASQL provider). I haven't had a chance to try it on the other computer yet.
  11. That line's OK; it fails on the next line and returns no error or return $ret = $oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query if @error then ConsoleWrite("returned error " & @error & @crlf) ConsoleWrite("returned " & $ret &@crlf) $ret = $oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query if @error then ConsoleWrite("returned error " & @error & @crlf) ConsoleWrite("returned " & $ret &@crlf) I'm guessing it's a provider issue, but I'm not really familiar with using COM objects. On the work computer the Microsoft.ACE.OLEDB provider works but others such as the default MSDASQL don't work and error the same as the ACE does at home.
  12. Well, now that that's working, another problem. I have code that works on my work computer, but when I try to run it on my home PC, it won't run. U have: #include <_Array.au3> Global Const $iCursorType = 0 ; adOpenForwardOnly Global Const $iLockType = 1 ; adLockReadOnly Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object Global $sFilename = @ScriptDir & "\all-airport-data-abridged.xlsx" ; debug file Global $sFilename = @ScriptDir & "\all-airport-data.xlsx" Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"' $oConnection.Open($sConnectionString) ; Open the connection Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object Global $sSQL_Query = "Select * FROM [Airports$A:zz1]" ; Select all records and all fields of the selected range $oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query Global $array = $oRecordset.GetRows(1) $oRecordset.Close ; Close the recordset ;$oRecordset = 0 ; Release the recordset object $numfields = UBound($array, 2) _ArrayDisplay($array) But it fails to open the connection: "......\sandbox\ado_test.au3" (13) : ==> The requested action with this object has failed.: $oConnection.Open($sConnectionString) $oConnection^ ERROR Obviously it requires some component not installed on my computer (MS Office, perhaps? I have that at work but use OpenOffice at home). I need this to be portable, not relying on any other software on the user's computer. Any ideas?
  13. I fixed it with IMEX=1: Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"' I tried _xlsx2Array but it didn't work, I forget why.
  14. I'm working on retrieving data from a xlsx file using a sql query and ADO, this is a standalone snippet: #include <_Array.au3> Global Const $iCursorType = 0 ; adOpenForwardOnly Global Const $iLockType = 1 ; adLockReadOnly Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object Global $sFilename = @ScriptDir & "\all-airport-data-abridged.xlsx" ; debug file ;Global $sFilename = @ScriptDir & "\all-airport-data.xlsx" Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"' Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO"' $oConnection.Open($sConnectionString) ; Open the connection Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object Global $sSQL_Query = "Select * FROM [Airports$A:zz3]" ; Select all records and all fields of the selected range $oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query Global $array = $oRecordset.GetRows(-1) _ArrayDisplay($array) exit There seems to be a problem reading the first line with the field names in some columns when the remaining data in the column is numeric, unless I only read one or two rows. But not all, e.g. columns 104-105 where it's numeric but the field name does come through. In the line where I create the query: Global $sSQL_Query = "Select * FROM [Airports$A:zz3]" As written above, I get no data for the first line in columns 3 and 27 (and maybe more, I didn't check them all): But if I change it to zz2 or zz1, I do get data. Any ideas? I didn't create the xlsx file so I have no control over how it's formatted (the abridged file attached is chopped out of the original, which has ~20K records). Nor can I count on the user of the script having MS Office installed so I can't use the Excel UDF. The workaround is simple, just read the first line to get the names, then read the rest of the data. But why is it happening? all-airport-data-abridged.xlsx
  15. Turns out SmarTeam has a text search function which can search the displayed tree and leave the highlight on the search result; I was able to use that after sending a couple of up arrows to make sure the cursor is above the desired entry so it's the first result.
×
×
  • Create New...