I am one of the 48 speakers at a 48-hour Excel extravaganza online on Oct 12 and 13. It’s FREE but you are encouraged to make a donation to charity to reflect the value to you of the content you benefit from. Visit ExcelVirtuallyGlobal.com.
The agenda:
https://excelvirtuallyglobal.com/program/
“Microsoft’s Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. This virtual conference, now in its fifth year, presents Excel, Data Platform and PowerPoint MVPs, together with other acknowledged experts from Microsoft and / or around the globe to present, answer questions and demonstrate the future of Excel and how it will make your life easier, personally and professionally.
Topics include: Auditing Spreadsheets, Charts, Dashboards, Data Analysis, Data Types, Dynamic Arrays, Excel Tricks & Tips, Financial Modelling, Forecasting, Maps, Microsoft 365, New Functions and Features, PivotTables, Power BI, Power Pivot, Power Query, Presenting Your Data, Spilled Formulae, Testing, Timelines and developing solutions with DNA .Net, VBA, Python, R, OfficeScript and Office-JS. And note well – it’s not all in English, with sessions in Mandarin, Portuguese, Spanish and Telugu, as well.
Most sessions will be recorded so you may watch them later with downloads aplenty – there are no medals for staying up to watch the entire event live!
From your own favourite chair, bring a laptop, an inquisitive mind and your sense of humour. Be prepared to learn heaps. And remember, it’s for charity – all profits will be donated to a global COVID-19 research charity.”
My topic is how to avoid data loss and corruption when importing text files into Excel. It is for the developer who needs to automate ways of importing text files from anywhere in the world with Unicode folder and file names, and content in any character set and encoding. And to correctly import data with any field delimiter and embedded linefeeds in quoted fields. It is based on other articles in this blog, collected together into one demo workbook.
Video timings:
00:00 Intro and the Windows file system object;
04:30 Reading file content as text;
Querying structured data – CSV and its many variants; in four ways:
11:00 User interface vs VBA open methods
21:05 Querytables
27:22 ADO Recordset
32:35 Power Query
42:00 End
While waiting for the organisers to correct the recording, here is a link to the full 42 minute video.
Better and safer ways to read text files in VBA
Patrick O'Beirne @ sysmod 