Other SOTA sites: SOTAwatch | SOTA Home | Database | Video | Photos | Shop | Mapping | FAQs | Facebook | Contact SOTA

Activator csv download header data fields mismatch

There seems to be a mismatch between the header and the data. With V2 format the other summit could be listed and is in the standard format header.

But when you examine the data content there is one field less, I think there should be a double comma between the OtherCallsign and the Comments.

Below is the current down load.

Version,YourCallsign,“Your Summit”,Date,Time,Band,Mode,OtherCallsign,OtherSummit,Comments,Points,BonusPoints,ActivationID
V2,MM0FMF/P,GM/SS-064,13/10/2006,13:45,144MHz,FM,GM4COX/M,Jack,4,0,48617
V2,MM0FMF/P,GM/SS-064,13/10/2006,14:00,144MHz,FM,GM6BIG/M,David,4,0,48617
V2,MM0FMF/P,GM/SS-064,13/10/2006,14:15,144MHz,FM,MM3NRX,Jason,4,0,48617
V2,MM0FMF/P,GM/SS-064,13/10/2006,14:20,144MHz,FM,MM0MIJ,Jim,4,0,48617

I have altered line 2 to what I would expect.

V2,MM0FMF/P,GM/SS-064,13/10/2006,13:45,144MHz,FM,GM4COX/M, ,Jack,4,0,48617

So the result when you upload to Excel etc is a misalignment between column header and data.

It’s not a big issue once you are aware.

The facility to download other activator data is really fantastic for getting a feel of what is possible from a summit, especially on VHF+. The database is a fantastic resource especially with a few home brew SQL skills on a local database.

73 de

Andrew G4VFL

1 Like

Someone made me add the header and I guess I missed a comma :slight_smile:

Which database are you using Andrew? The old one or the new one (newsotadata.sota.org.uk)?

The official advice is don’t put much effort into doing stuff on the old one as it WILL be retired in 2020, probably Easter time. If it’s on the new one, then we will need to get it sorted.

Maybe try the new database currently in beta?

https://newsotadata.sota.org.uk

There I would say it is working fine.

I admire your confidence :slight_smile:

Train arrives in 5 minutes, fix will go out a few minutes after that

1 Like

It’s the new database I am using.

Andrew G4VFL

Fixed. In a classic example of the adage “If a job’s worth doing, it’s worth doing half-arsed”, I’d added the extra comma in one location but not the other.

2 Likes

Good man. Much as though I have spent much time with the old database and its codebase, it’s served its purpose and has earned a long and happy retirement. ISTR the code for it in places will be 15+ years old and some files have never been changed in all that time.

Yes its fixed !

How do you do it ?

I didn’t have to fill in a 20 page change request, followed by 5 review meetings and a test report and roll back plan on the test database before it could be implemented on the live database !

73 de
Andrew G4VFL

2 Likes

he’s a techo… they have their ways, best not to ask if it is working. if not, a court martial…

image

6 Likes

Sorry to come back on this subject, it appears the Summits download csv has the wrong number of commas in the data field.

There appears to be an extra comma at the end of the Data. It doesn’t show well on the snip.
The result is that there is no header to match the data. I did a fix by adding “,Test” to the header. That fixed the load into SQL, in Excel you would never notice because it is all blank cells.

I also have to remove the line for the listing date, but I would expect that.

I assumed it was my error trying to get this to BULK INSERT with various error codes but traced it to a header / data field mismatch.

I have checked the S2S and it appears to be OK. Currently I don’t use that down load.

Here’s a boring bit of T-SQL below the signature.

OFF TOPIC
In case you are wondering what I am doing, I use it to extract for an Activator,Band,Summit list done and to do. The current Unique says how many summits but not which ones because when you click on view it lists all summit the activator has been to not just the ones for the band on interest.

73 de
Andrew G4VFL

/*****

Create the summits database

This uses the SOTA summits database to create a SQL local copy.

There are three stages

  1. Create a blank database
  2. Download the .csv file from the SOTA database site
    2a) remove the 1st line with the date of the down load
    2b) Add an additonal field into the headder “,Test” this will then matchup with the data fields
  3. Load the new .CSV file into the database.

*****/

USE [master]
GO

/*
drop DATABASE [Summit]
go

*/

CREATE DATABASE [Summit]
GO

ALTER DATABASE [Summit] SET COMPATIBILITY_LEVEL = 150 – set to 120 if using SQL 2014
GO

USE [Summit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Summit](

[SummitCode] [nvarchar](255) NULL,
[AssociationName] [nvarchar](255) NULL,
[RegionName] [nvarchar](255) NULL,
[SummitName] [nvarchar](255) NULL,
[AltM] [int] null,
[AltFt] [int] null,
[GridRef1] [nvarchar](255) NULL,
[GridRef2] [nvarchar](255) NULL,
[Longitude] [float] null,
[Latitude] [float] null,
[Points] [int] null,
[BonusPoints] [int] null,
[ValidFrom] [date] null,
[ValidTo] [date] null,
[ActivationCount] [int] null,
[ActivationDate] [date] null,
[ActivationCall] [nvarchar](255) NULL,
[Test] [nvarchar] (255) NULL

) ON [PRIMARY]
GO

/* Load the database from the .csv file */

use [Summit]
go

DELETE FROM [dbo].[Summit]

SET DATEFORMAT dmy; – matches the Day Month and Year to UK format
GO

BULK INSERT [dbo].[Summit]
FROM ‘S:\Users\Andrew\Documents\SOTA\SQL Data and Scripts\summitslist.csv’
WITH
(
FIRSTROW = 2, – this get round the colum header
ROWTERMINATOR = ‘0x0a’,
FORMAT = ‘CSV’

)
GO

/*

Sample header including work around to match headder and data fields.

SummitCode,AssociationName,RegionName , SummitName, AltM, AltFt, GridRef1, GridRef2, Longitude, Latitude, Points,BonusPoints, ValidFrom , ValidTo ,ActivationCount,ActivationDate,ActivationCall,Test
3Y/BV-001 ,Bouvet Island ,Bouvetøya (Bouvet Island), Olavtoppen, 780 , 2559 , 3.3565 , -54.4104, 3.3565, -54.4104, 10, 3, 01/03/2018, 31/12/2099, 0, , ,
*/

Hah, not my problem this time :slight_smile: Grab the summitslist.csv from https://newsotadata.sota.org.uk/summitslist.csv

Got it, I think.

The link you sent me to the Summitslist gets the file I would expect.

However on the link I used was from https://newsotadata.sota.org.uk/en/summit/list page,
The link on this page is to the .csv file on the old database hence I get the wrong file.
http://www.sotadata.org.uk/summitslist.csv

73 de
Andrew G4VFL

1 Like

Yeah, this is so it’s one less thing I have to fix when the transition occurs - and that for a while the newer version of the file wasn’t implemented yet.

Anyway, I’m not going to adjust it tonight. Apparently by having a fixed version of the summitslist I got hammered for breaking backwards compatibility, so my care factor to implement anything is rapidly approaching zero.

Many thanks for your efforts with the database.

Upgrades are such a pain, I worked on BlackBerry servers, so software troubles = 1000s of users complaining their mail and calendar wasn’t working. I was an admin rather than a developer so always had to escalate things with at least an 8 hour delay to Canada at that time.

There is no rush on this after all this is a hobby.

Should I private message if I have further database issues rather than put it through the reflector for general information ? It is that fine balance between informing the user base and endless messages to nail the issue.

73 de
Andrew G4VFL

I’m not fussed either way. Perhaps if it’s of general interest, public is fine, but PM still gets to me. I’d say bug reports come in about 60/40 reflector/PM