STEP 6 EXCEL Processing

Owned by kurt.martin
Last updated: Jun 13, 2023 by Haoyuan Wang (Unlicensed)
2 min read

Now we drop the contents of DATA.TXT into Excel, sort it and determine the newest data.

Open Data.txt

Click "select all" and "copy"

Open Excel

Paste all of the data into COLUMN B

 

click "select All" and sort by Column B DESCENDING  (newest to oldest)

 

Verification check

  • there should be 10 rows of headers from concatenating the text files, up on top

  • delete them

 

So, the Newest FAA SDR data will now be up top.

The leftmost date seen here is the FAA Report-Reviewed date. From this we determine how much data, we're going to import into the M70_SDR_Transit table for analysis. If this task hasnt been performed in a while, we may want to go back a full year or more.

 

Now, we paste in a repetitive character

   into  Column 'A' = '['

  and   Column 'C' = ']'

for 10,000 rows (from step 1, it should be able to figure out how many rows will be needed)  - this count will vary based on how long its been since we've done an import. This is purely a judgement call.

So our data is now between Square brackets, which we'll replace with Insert statement characters, in the next step.

Note : a statement like “Select “[“ from y50_party where rownum < 10001” will generate you a handy text file of repetitive characters

 

At this point I normally scroll downward at least 3 months (should be about 10-20k records) and copy the contents of Columns A-B-C back into Data.txt. Our goal here, is to have square brackets around the newest data.

 

Click save.

 

Much of this data we already have, but new reports can be found anywhere in the batch.