Members CSV file export.... Aircraft type field request?

Bluedharma

I was wondering if this was possible.

On the member section you are currently able to export a .csv file for review.

.......

Photo Type ... Airport or Aircraft

Folder Name .. Folder name in which photo is directly located

Filename ..... Photo filename in Airport-Data.com system

Tail Number .. Aircraft Register Number, a.k.a. Tail Number

Airport ...... Airport where the photo is taken

Description .. Photo description

Taken Date ... Photo Taken Date

Upload Date .. Date when photo was uploaded onto airport-data.com

Would it be possible to include the "type" of aircraft if known along with the tail number?

I exported a .csv file, then imported it into Excel. With a little Excel magic and a pivot table I was able to see what I posted too much of.

I found

86-0358 = 20 pics at KAPA

N735YP = 11 pics at KAPA

N28KT = 9 pics at KAPA

N308WK = 8 pics at KAPA

Now it would be nice to see this in my excel file...

86-0358 ..... 1986 Lockheed F-16C = 20 pics at KAPA

N735YP ..... 1942 Boeing B75N1 = 11 pics at KAPA

N28KT ..... 2005 Shpakow Thomas SA 750 = 9 pics at KAPA

N308WK ..... 1944 Vickers SPITFIRE MARK IX = 8 pics at KAPA

Then I could import it to a spreadsheet and try to get more original shots.

Also, while out spotting, it would be nice to have a copy of the sheet to tell pilots that I meet that I have a few shots of the aircraft.

Is this possible? If so, I would be happy to write a "guide" as to how to do a pivot table for others who wish to try this.

Best Regards,

Ken Wang

It shouldn't be hard. I will try to add it as soon as I find some time. Thanks for the suggestion.

Ken

Bluedharma

Ken,

Thank you and many thanks for this site!

If there is interest... I could try to put together a little Excel Pivot table example when the new export is ready.

Regards,

Ken Wang

I could try to put together a little Excel Pivot table example when the new export is ready.

What does "Excel Pivot table" means?

Bluedharma

It is a way of organizing data in an Excel file.

See:http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx

The problem with a normal input into Excel (from a .csv) is that the same values (N-NUMBERS) are shown again and again.

The advantage with a pivot table is that unique values are shown, and a count.

You can see how many shots of an aircraft are taken at which airport.

Here is an example:

What I get from Airport-data.com site.

Tail | Airport

===================

N51JT | KAPA

N322P | KAPA

N308WK | KAPA

N5163H | KAPA

N5220A | KAPA

After making a pivot table I can get this.

Count of Tail | Airport

=========================================================

Tail | KAPA | Grand Total

=========================================================

N308WK | 2 | 2

N322P | 1 | 1

N5163H | 1 | 1

N51JT | 1 | 1

N5220A | 1 | 1

=========================================================

Grand Total 6 | 6

Now I know I have 2 pics of N308WK at KAPA.

If I had the aircraft type... I could tell you I have 2 pics of a Spitfire at KAPA.

Best Regards,

Ken Wang

Okay now I get it.

The aircraft model field has been added into exported CSV file. It's a bit more complicated than I thought, but I managed to make it without impact on performance of exporting.

Ken

Bluedharma

Thank you very much!!!!

Now I can see what I have and start organizing it!

By pulling a list, I can now see my WACOs, Yakovlev and Eurocopters!

Pivot tables are nice, but...

I guess this can also be done in Excel by the following.

Step 1: (open the csv)

Step 2: (select the two fields you wish to get unique values on)

Step 3: (select data>>filter>>advanced filter)

Step 4: (I just clicked OK because I really didn't care about the headers)

Step 5: (select unique values only)

Step 6: (Here is your N-Number and types list)

If there is interest, I will put up a Pivot Table example.

Thanks again to Airport-data.com!

Yakfreak-VAP

I try to export an actual file for some days now and i am always recieving "Server had a temporary error. Please try again later.". As i did it from severeal Computers i do not believe that there is an error from my side.

Thanks for help

Dietmar from VIE

Bluedharma

Hi Dietmar,

I just tried to export a few sec ago.

I was able to get a file.

It is possible...

Any "commas" in the aircraft model could throw off the export. Since CSV = comma-separated list or comma-separated variables... This may cause problems.

Ken, when you are exporting on the SQL statement... are you removing commas from the airport model prior to export? The commas wouldn't cause an error in the php output would it?

Thanks again for adding the aircraft model. It has helpl me a great deal.

Best Regards,

Ztex

I get the same message as Dietmar....

I don't understand the comma thing...does it mean that if there is a comma in the description or the aircraft model? I don't see any commas in a quick survey of the models...

???

Bluedharma

Sorry Zane,

I know at my work we sometimes have trouble exporting data as a .csv when an extra comma is in the description.

When you import data into Excel, the extra comma causes problems as all the data is separated by commas. This could happen on export too, depending on what you do.

I don't work much with .php (what is used for this site). I use Cold Fusion, but there could be issues with any process that exports if the data has unexpected characters in it.

Strange that the export is currently working for me.

Best Regards,

Yakfreak-VAP

I am exporting .csv files since this function was intruduced and i never had any problem with it. The Problem appeard with the changes in the download.

I am using IE7 at home and IE6 in the office. What browser are you using?

Dietmar

Bluedharma

I Tested it with the following:

Windows Internet Explorer 7

Version: 7.0.5730.11

Update Versions

Firefox

Version 2.0.0.9

Both versions exported correctly on my machine.

Microsoft Windows XP Professional

Version 2002... Service Pack 2

Strange...

Best Regards,

Ken Wang

Sorry I didn't notice the new posts reporting problem. I will take a look at the issue.

Thanks,

Ken

Ken Wang

Just did a quick look at code, commas in photo description has been considered so it should not be a problem. I still need to figure out Dietmar's problem though.

On a side note, browser types should not matter either.

Ken

Ken Wang

A bug was found in the SQL statement used to export data. It has been fixed.

Ken

Ztex

Well I was able to D/L it at home and then I tried at work this morning and it worked there too.

Thanks

Zane

JohnLittle

Hi Blue,

Had I known that I had so many duplicates in my database, I would have been a bit more careful. Excellent suggestion, and Ken, thanks for implementing.

I see absolutley no reason for me to have 25 pictures of Aluminum Overcast in my photo section. While they may be different in some respects, this is a waste of server and storage resources.

I will be cleaning up my playground this weekend !!

Best,

John L