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
- Create a blank database
- 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
- 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, , ,
*/
