How do I read correctly this ASCII text file?
I can download it as a zip file here: https://www.irs.gov/pub/irs-soi/eo2016.zip When I open it out of the zip file, add ".txt" to the file name, and open it in Excel - there are many numbers without any sense displayed (screenshot attached).enter image description here I have also opened it in MatLab and RStudio, but there these numbers are also displayed.
Anybody knows how to do this correctly?
As discussed in the comments, the file is in fixed-width format (line length: 9444), and column positions have been specified in a separate Excel sheet.
Here are 3 possibilities to import such a file in Excel.
There's a 'Text to Columns' button in the 'Data' tab of Excel's ribbon. It supports fixed-width files, but manually placing 833 column separators will be an incredibly tedious job. And there seems to be no way to save the column definitions for subsequent imports.
From the specs sheets (EO990_16), copy columns C and D, and paste them to another Excel sheet, transposed; use Paste Special - Transpose. This should populate rows 1 and 2 as follows:
1 13 22 26 27 102 162 ...
12 9 4 1 75 60 2 ...
Now fill the rest of the sheet starting from row 3 with formulas referencing the data sheet, like you see below. This is a straightforward duplication of any single cell horizontally and vertically.
=MID(Data!$A3, A$1, A$2) =MID(Data!$A3, B$1, B$2) =MID(Data!$A3, C$1, C$2) ...
=MID(Data!$A4, A$1, A$2) =MID(Data!$A4, B$1, B$2) =MID(Data!$A4, C$1, C$2) ...
=MID(Data!$A5, A$1, A$2) =MID(Data!$A5, B$1, B$2) =MID(Data!$A5, C$1, C$2) ...
... ... ...
Source:
https://www.wizardofexcel.com/2011/09/28/saving-a-fixed-width-import-layout/
CSV is easy to import.
This command-line approach may help:
convert a fixed width file from text to csv