Manual tasks to perform if the batch file does not work for you or anybody on the team:
Note: Never download from the FIXED WIDTH section of the page, the FAA code introduced an error causing corruption on these files, sometime in 2017. FAA SDR management is aware of the issue.
We use the Tab-Delimited Zipped Data files from the lower part the page.
Download the Tab Delimited Data sdr zip files (lower list of files on the screen) for the last 5 years from https://av-info.faa.gov/dd_sublevel.asp?Folder=%5cSDRS
unzip the zip files:
unzip sdr2018a.zip sdr2018a.txt
unzip sdr2018g.zip sdr2018g.txt
unzip sdr2019a.zip sdr2019a.txt
unzip sdr2019g.zip sdr2019g.txt
unzip sdr2020a.zip sdr2020a.txt
unzip sdr2020g.zip sdr2020g.txt
unzip sdr2021a.zip sdr2021a.txt
unzip sdr2021g.zip sdr2021g.txt
unzip sdr2022a.zip sdr2022a.txt
unzip sdr2022g.zip sdr2022g.txt
unzip sdr2023a.zip sdr2023a.txt
unzip sdr2023g.zip sdr2023g.txt
concatenate the zip files to create DATA.TXT
type sd*.txt > DATA.TXT
Delete the unneeded zip and txt files.
DEL SD*.TXT
DEL SD*.ZIP
Character replacements
AMPERSAND | & BECOMES PLUS SIGN + |
|---|---|
QUOTE | ' BECOMES BACKPICK |
DOUBLE QUOTE |
|
SEMICOLON | ; BECOMES COMMA |
VERTICAL BAR | | BECOMES SPACE/BLANK |
SQUARE BRACKET | [ BECOMES ROUND BRACKET ( |
SQUARE BRACKET | ] BECOMES ROUND BRACKET ) |
DOUBLE SPACE | BECOMES SINGLE SPACE (this is just to reduce file size - do this 3-4 times) |
Save the DATA.TXT file to c:\DATA\FAA
Open the DATA.TXT file in excel
These three steps are the quickest way to open the DATA.TXT the same way the batch file does:
Go to a command prompt (does not have to be as an admin).
cd \Program Files (x86)\Microsoft Office\root\Office16
run the command: excel C:\DATA\FAA\DATA.TXT
The data in DATA.txt will now look similar to this. Notice the ENTRY_DATE is our leftmost value.
0 Comments