Recent Comments
Author Archives: Patrick O'Beirne, spreadsheet auditor
Querying Text/CSV files in VBA, method #3: Power Query
The module PowerQueryText.bas contains VBA code to query text/csv/tab delimited files and return tables to worksheets. Continue reading
Querying Text/CSV files. Method #2: ADO Recordset
The module ADOQueryText.bas has the functions to read a delimited text file into a worksheet. It uses an ADODB recordset to perform the query. Continue reading
Querying Text/CSV files. Method #1: Querytable
The legacy Get Data from Text/CSV command in Excel creates a Querytable. This is pretty good for most purposes, except… Continue reading
Reading Unicode file contents as text
This is a short update on my post of Sep 28, 2020 on reading Unicode text in VBA. I am reposting the module as ADOStream.bas Continue reading
Hello (the rest of the) World – reading data in other languages
Have you ever opened a text file in Excel and found that the characters looked weird or corrupted? The Wikipedia entry for mojibake says ‘In German, Buchstabensalat (“letter salad”) is a common term for this phenomenon, and in Spanish, deformación … Continue reading
UK Covid-19 Track & Trace Excel snafu: Uncontrolled spreadsheets lead to data loss
The story broke around October 5th that Public Health England temporarily lost 16,000 Covid test results because they did not notice that the XLS data format they were using was insufficient to process the number of results they were getting. … Continue reading
Posted in Uncategorized
1 Comment
Nested JSON to flat data tables
Have you ever had to convert a file of JSON text to Excel tables? Not simple JSON, but one with a collection of dictionaries inside a dictionary inside a collection of dictionary records inside another collection of records inside a … Continue reading
VBA Read Unicode file contents in various encodings
To read a text file, you need to be able to handle more character sets than just ANSI. Not just in the contents but also in the file and folder names.The classic article on this is by Joel Spolsky:https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/ Here … Continue reading
A look back to Eusprig 2019
The 20th conference of the European Spreadsheet Risk Interest Group was held in Browns of Covent Garden, London on 11-12 July 2019, Thursday 11 July 2019 Developing Excel Thought Leadership David Lyford-Smith, ICAEW, Chartered Accountants’ Hall This described the development … Continue reading
A look ahead to Eusprig 2019
The 20th annual conference of the European Spreadsheet Risk Special Interest Group (http://www.eusprig.org) will be held on July 11 & 12 at The Barrister’s Court, Browns, 82-84 St Martin’s Ln, Covent Garden, London WC2N 4AG, UK. David Lyford-Smith of the … Continue reading
Posted in eusprig, Excel/VBA, Research, Software Quality, Software Testing
Tagged Excel tutorials, Spreadsheet management, spreadsheet risk
Leave a comment
