Parse Fixed-Width Data in Google Sheets
Data isn't always formatted in a user-friendly manner and the documentation may be sparse. So when a friend asked me if I could help him parse a data file from a county website that ominously warns that the data is in a "large file and requires a person with programming experience to extract the information. The files generated are formatted in fixed width ASCII only. There is no technical support or data interpretation for files from our office", I said I would give it a shot, without having any idea if I could actually help.
The site includes a pdf that defines the file formats, but the titles don't correspond to the files they actually provide so a bit of trial and error ensued.
After a few tries, I was able to apply the format to the file and parse it successfully using some basic functions.. and of course, using the ARRAYFORMULA function since the file contains about 30,000 rows. I'm not sure I've used the ARRAYFORMULA function with that many rows but the performance remained high (no noticeable lagging), which definitely would not have been the case if I had tried to copy a formula for each row of data.
If you have a spreadsheet question or would like help analyzing data, feel free to reach out!