Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
1  General / Gerrit-Jan Linker's Weblog / Find computer serial number
 on: 14.10.16 at 12:20:32 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Find computer serial number
 
To find a computer's serial number, the following command can be used in a MSDOS window:
WMIC BIOS GET SERIALNUMBER
Reply Quote Notify of replies  

2  SQL*XL / Database Connections / Oracle connections through LDAP
 on: 14.06.16 at 21:27:10 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Oracle connections through LDAP
 
It is possible to connect to Oracle databases through LDAP:  Lightweight Directory Access Protocol.
 
When using this protocol, the ldap.ora file can be used to specify the ldap properties.  
Location: $ORACLE_HOME/network/admin
 
In the sqlnet.ora file you need to specify the name resolution adapter in the following way:
NAMES.DIRECTORY_PATH=(LDAP)
or if you have multiple name resolution adapters:
NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, ...)
 
Source:
http://www.orafaq.com/wiki/Ldap.ora
Reply Quote Notify of replies  

3  SQL*XL / General Usage and Feature Troubleshooting / Re: date range in sql query is ignored
 on: 25.05.16 at 09:02:31 
Started by MikeLust | Post by Gerrit-Jan Linker
Hi,
We need a little more information. Which database do you use and which driver?
 
In general it is best to be explicit with dates and not rely on implicit conversions. A good query would be:
 
select * from mytable where mydate between to_date('01-01-2000', 'dd-mm-yyyy') and to_date('01-01-2010', 'dd-mm-yyyy');
Reply Quote Notify of replies  

4  SQL*XL / General Usage and Feature Troubleshooting / date range in sql query is ignored
 on: 13.05.16 at 19:15:20 
Started by MikeLust | Post by MikeLust
In a search of other forums there doesn't seem to be a report of this finding with SQL*XL 515.  The date range in my sql query is being ignored and the query pulls into Excel all records in the Oracle database table being queried (from Kronos Visionware), regardless of the date in the date field.  The same query returns an appropriate resultset when run the Visionware user interface custom query section. This is a recent occurrence - any advice?  Thanks.
Reply Quote Notify of replies  

5  SQL*XL / Evaluation, Installation & First use, Purchasing / Excel in safe mode: Use our digital certificate
 on: 09.05.16 at 09:36:55 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Excel in safe mode: Use our digital certificate
 
To ensure SQL*XL can run in Excel in safe mode, it is important that you mark our digital certificate as trusted. Excel will ask you to mark it as trusted, but if it does not you can do it manually too.
 
The important file to mark as trusted is sqlxl.xla. This file is located in the SQL*XL program folder (default installation directory is c:\program files\sqlxl).
 
Double click this file to open it in Excel. Alternatively, start Excel and use file-open to open it.
 
Excel might display a message similar to the message below. It states that it cannot use this file as a trusted file because the digital signature with which it was signed was not marked by you as trusted yet.
You can view the digital certificate as indicated by the arrows.
 
To mark the certificate as trusted, simply click the button in Excel marked with the arrow.
 
Reply Quote Notify of replies  

6  General / News and announcements / MasSim
 on: 29.04.16 at 10:44:08 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Software release: MasSim
 
A new version of the MasSim software was released. A digital signature was added to the code to ensure secure download and installation. On the technical level, old DAO code was rewritten as ADO code. The software was tested on windows 2010 (x86).
 
A new version of the program can be downloaded.
Reply Quote Notify of replies  

7  General / News and announcements / Software release: Outlook2Excel
 on: 19.04.16 at 12:00:45 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Software release: Outlook2Excel
 
A new version of the Outlook2Excel software was released. A digital signature was added to the code to ensure secure download and installation. The software was tested on windows 2010 (x86) and Outlook 2016.
 
A new version of the program can be downloaded.
Reply Quote Notify of replies  

8  General / News and announcements / Software release: Huckel 3.2
 on: 18.04.16 at 09:55:38 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Software release: Huckel 3.2
 
A new version of the Huckel software was released. A digital signature was added to the code to ensure secure download and installation. Enhancements include the calculation of bond orders and pi charges. Small fixes were applied to the user interface for windows 10. The software is now secured with a registration key. Upon download, a 10 day activation key is sent for an evaluation of the software with full functionality.
 
A new version of the program can be downloaded.
Reply Quote Notify of replies  

9  General / News and announcements / Software release: SQL*XL 2.2.12
 on: 15.04.16 at 23:59:29 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Software release: SQL*XL 2.2.12
For SQL*XL, version 2.2.12. was released.  
    
The new version is compatible with Windows 10 and Excel 2016.
A new digital certificate was added which ensures the software can run in Excel in secure mode.
    
 See also:  
SQL*XL Revision History  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130772637
Reply Quote Notify of replies  

10  SQL*XL / General Usage and Feature Troubleshooting / Analysing a huge text file in minutes
 on: 12.04.16 at 22:14:42 
Started by Gerrit-Jan Linker | Post by Gerrit-Jan Linker
Analysing a huge text file in minutes
 
In this topic I will show that analysis of large text files is easy using SQL*XL.  
 
It is based on a real life problem with a php web server that ran over its disk quota. Analysing the problem, the php.log file had grown to 3.8 GB. Warning messages are logged in this file. The task at hand is to find out what the problems are on this php web site.
 
I will show that within minutes, with the help of SQL*XL, 20 problems could be identified that covered 90% of the contents of the php.log file. I will show you step by step how this was done.  
 
Step 1: Obtaining a copy of the file.  
I used an ftp program to download the file file. I renamed the file to php.txt. The file is too large to open with notepad. I also tried notepad++, ultraedit and PFE, all with not too good results.
 
Step 2: Access the file through SQL
Using the SQL*XL software, Excel can be connected to this text file. Once connected, SQL can be used to analyse its contents. SQL*XL is going to 'connect' to the directory that contains the text file.  Files in the directory can be accessed as tables.
 
To set up a new connection in SQL*XL, using the connect button in the SQL*XL ribbon in MS Excel. Select to setup a new connection and start the Database Connection Wizard. Select text/csv as the database type and specify the directory.  
Now, SQL*XL will try out several connections. On my machine it managed to connect through two drivers: an ODBC driver and an OLEDB connection through the Microsoft Jet driver. I chose the Jet driver.  
 
Step 3: Counting the number of lines in the text file:
In SQL*XL's SQL dialog the number of lines in the file can be retrieved with a 'count(*) query':
select count(*) from [php.txt];
The query result is put in Excel cells: 23287499. So there are over 23 million rows to be analysed. Note that the table name should always be put in between square brackets [].
 

 
Step 4: Retrieve a selection of the file data:
In SQL dialog a 'top x query' was typed and run:
select top 100 * from [php.txt];
It retireved 100 rows from the file. The data was like this:
'[18-Jan-2016 11:55:30 Europe/Amsterdam] PHP Notice:  Undefined index: action in /home/1234/sites/abc/mysite.nl/web/index.php on line 12
 
All warning messages seem to start with a date/time location statement after of which the actual warning is displayed. We need to skip the first part of the line so we can work only with the warning messages.
 
Step 5: Define the layout of the table
It helps to let SQL*XL view the table as having 2 columns. The first can contain all the data we don't need, the date, time and location, and a second column that we can analyse further.
We can specify this by creating a small text file in the same directory as the php.txt file. Name the filie schema.ini. I entered the following information in the schema.ini file to indicate that the php.txt file contains fixed length data, that it contains no column headers and that it contains two columns. I specified that the first 40 characters to form part of column a and the next 100 characters are regarded as column b:
[php.txt]
Format=FixedLength
ColumnHeader=False
Col1=a Text Width 40
Col2=b Text Width 100
 

 
Step 6: Retrieve the unique messages and count their occurrences
A 'group by query' can be used for this. In the SQL dialog type:
select count(b), b from [php.txt] group by b order by count(b) desc;
 
Explanation of this query:
I wish to retrieve the warning message of column b but also count how often that message occurs in the file. Hence the select count(b), b. Group by b specifies that the data should be grouped by column b. Using the order by clause I specified that the messages with the highest occurrence should be listed first.
 
The result:
No      per.      number      warning
1      12%      2694801      PHP Notice:  Undefined index: escape_output in /home/1234/sites/abcd/mysite.nl/web/
2      11%      2610967      PHP Notice:  Undefined property: stdClass::$edittime in /home/1234/sites/abcd/mysite.nl/web/cms/
3      11%      2598029      PHP Notice:  Undefined index: target in /home/1234/sites/abcd/mysite.nl/web/cms/cmspages/
4      9%      2206085      PHP Notice:  Use of undefined constant MODULE_NAME - assumed 'MODULE_NAME'  
5      9%      2031366      PHP Notice:  Undefined index: function in /home/1234/sites/abcd/mysite.nl/web/cms/cmspages/base/
6      7%      1515632      PHP Notice:  Undefined index: width in /home/1234/sites/abcd/mysite.nl/web/cms/cmspages/base/view
7      5%      1261983      PHP Deprecated:  Function ereg() is deprecated in /home/1234/sites/abcd/mysite.nl/web/index.php
8      4%      1021392      PHP Notice:  Undefined index: prijs in /home/1234/sites/abcd/mysite.nl/web/cms/class/cms.php
9      4%      1021028      PHP Notice:  Undefined index: prijs_uitleg in /home/1234/sites/abcd/mysite.nl/web/cms/class
10      3%      739672      PHP Notice:  Constant QDOM_NEWLINE_STR already defined in /home/1234/sites/abcd/mysite.nl/web
 
To be fair, I added the first two columns by hand with Excel.  
 
Reply Quote Notify of replies