Outlook.Application

Using the Outlook.Application object to read e-mails from Visual Foxpro.

Trying to read e-mail using POP3 or IMAP proved to be hell to me. Documentation is not present or hardly legible. Because we use Microsoft® Outlook® at our company, I was able to connect to Outlook using the Outlook.Application object.

This simple code finds all mails in a certain folder (loTelecomMap):

loOutlook = CREATEOBJECT("Outlook.application")
loMAPI = loOutlook.GetNameSpace("MAPI")
loTelecomMap = loMAPI.GetDefaultFolder(6).Folders([Business]).folders([Telecom])
loMails = loTelecomMap.Items
loMessage = loMails.Find("[Subject]='Mail subject title'") && starts with the last (newest) message
DO WHILE NOT ISNULL(loMessage)
 ldDate = TTOD(loMessage.ReceivedTime)
 lcMessage = loMessage.Body
 lcHTMLsource = loMessage.HTMLbody
 * More actions on the loMessage object
 loMessage = loMails.FindNext() && Next message
ENDDO
RELEASE loOutlook

I had great help using a whitepaper by Andrew MacNeill. Linking to his page is prohibited, so you have to copy-and-paste the URL yourself: http://www.aksel.com/whitepapers/OutlookAutomation.htm. To accommodate for possible closing of that reference, I have included the pages contents (the whitepaper) in PDF form on this page.

Read from Excel using MS ODBC

If you just want to read the contents of an Excel spreadsheet, there is an easier way using the Microsoft Excel (ODBC) Driver.

In a previous post (Foxpro and Excel) we have shown how to control Excel using the Excel.Application object. But if you are just interested in the contents, there is an even easier way to achieve this: The Microsoft Excel (ODBC) driver. that is present on most systems with MS Office installed.

Source: Importing data from a Microsoft Excel 2007 workbook using Visual FoxPro 9.0 (Microsoft Support)

To get an SQL connection to an Excel sheet is no more difficult than this:

lcXLfile = [c:\temp\demo.xlsx]

* Warning: The driver wil never fail! 
* If the file does not exist, this driver wil create the file.
IF !FILE(lcXLfile)
 ? [Excel file not found]
 RETURN .F.
ENDIF

lcConnection = [DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=]+lcXLfile
lnHandle = SQLSTRINGCONNECT(lcConnection)
* Get all sheetnames in a cursor (every sheet is a Table)
? SQLTABLES(lnHandle,[],[crSheets])
lcQuery = [SELECT * FROM ]+TRIM(STRTRAN(crSheets.Table_Name,['],["]))
? SQLEXEC(lnHandle, lcQuery, [crResults])
SQLDISCONNECT(lnHandle)

The SQLTABLES command creates a cursor with a row for each sheet in the Excel file. You can filter the kind of tables in the second parameter. This is a typical row:

Field name Field type Length Example
TABLE_CAT Memo 4 C:\TEMP\DEMO.XLSX
TABLE_SCHEM Character 128 .NULL.
TABLE_NAME Character 128 ‘Sheet1$’
TABLE_TYPE Character 128 TABLE
REMARKS Character 254 .NULL.
SQLTABLES structure

Remarks

Quotes in the SQLTABLES result

The TABLE_NAME field contains single quotes, These seem not to work in a SQL statement. So you have to replace them with double quotes. Check out the lcQuery line in the code above..

Field names in queries

Only when a sheet is properly filled the field names in queries are automatically named correctly: The titles in the first row are accepted as fieldnames. However, if the first row is left blank or there are multiple tables on a page, the result becomes erratic.. In this situation some or all fields get the title F1. and so on (number being the column number).

Field types

It seems the unaltered query results deliver just 2 kinds of columns: Numbers and Memo’s. Both columns may contain .NULL. values.

Quotes in query’s

To compare or use strings in SQL query’s, you need to use the single quotes again. For instance:

lcQuery = [SELECT * FROM "Sheet1$" WHERE surname='de Graaf']
SQLEXEC(lnHandle,lcQuery,[crDeGraaf])

Double complex because the sheet (table) name has to be encapsulated in double quotes, while a compare string has to be encapsulated in single quotes.. I can’t think of a reason for this odd behavior.

Read only

As to be expected, this ODBC method to an Excel file is limited to reading the file. Trying to insert or update using SQL commands renders the result that the query has to be updatable.

Powerquery Tabbladen combineren

Soms worden gegevens aangeleverd op meerdere tabbladen, en deze wil je tot een enkele tabel combineren.

Je begint met het maken van een nieuwe Query als volgt:

Dan kies je het bronbestand:

Klik op Openen. In de volgende dialoog kan je kiezen welke tabbladen je wil importeren. Maar dat wil je nog niet! Als je het wel doet, dan maakt Powerquery voor elk tabblad een nieuwe Query waardoor je moeilijk kan combineren. Nee, je kiest hier de map en voor bewerken!

Nu krijg je keurig alle tabbladen als regels te zien:

Als je een Table klikt, dan zie je dat de hele tabel in deze cel zit gepropt:

We gaan de mutaties niet vergelijken, en het verborgen werkblad FilterDatabase willen we ook niet behandelen. Vandaar dat we de tabbladen uitsluiten van verdere verwerking door hier een filter toe te voegen waarbij we de ongewenste werkbladen de-selecteren:

Nu hebben we alleen de gewenste werkbladen. We gaan deze combineren door de knop met de 2 pijltjes in de Data kolom:

Hierna moeten we kiezen welke kolommen we willen meenemen. Ook staat standaard aan dat je de oorspronkelijke kolomnaam als voorvoegsel gebruikt. Dat wil je niet. Alle kolommen zouden dan Data.Postcode, Data.Huisnummer enzovoorts gaan heten. Dit vinkje dus weghalen!

Nu heb je een enkele tabel met alle rijen en een paar extra kolommen (4). De eerste kolom bevat de bron (uit welk bestand stammen de gegevens). De kolom Item staat achter de ingelezen kolommen en bevat hier de naam van het tabblad waar de gegevens vandaan komen.

Nu moeten de kolomnamen nog worden aangepast, want een kolom met een nummer is weinig duidelijk:

Gelukkig is er een mooie functie voor die de eerste rij omzet naar kolomnamen:

We moeten wel de naam van de eerste kolom herstellen, en het is vervelend dat de overige titelregels tussen de gegevens zijn blijven staan:

De overbodige titelregels kunnen we eenvoudig uitfilteren in bijvoorbeeld de perceelnummer kolom:

Daarna de kolommen een zinvolle naam gegeven, en de tabblad kolom naast de bronkolom geplaatst.

Nu moeten de kolomtypes nog worden aangepast. Dat kan je zelf handmatig doen, maar het is sneller om alles te selecteren (Ctrl-A) en vervolgens de knop Gegevenstype detecteren te klikken. Je ziet nu dat Powerquery vaak het juiste type kan bepalen: