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.
At the beginning of each calendar year, the download and unzip statements need to be adjusted to correspond to current calendar year and go back five years.
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:
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