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

Database - Import CSV

Any experts on this one?

Gary G0HJQ, our Database manager, has said he will help me with it when he returns from his holiday, but it would be nice to be able to do it myself before then.

I have used the “Save to file” feature to take a copy of my Activator log. I have then edited this file to tidy up a few things and correct the occasional typo, keeping the file in CSV format throughout.

When I come to importing it back into the website, I get told to check and correct the errors highlighted in red. These tend to be all the times for an activation, occurring several times, and all the dates up to 12/7/03. However, I have checked these and they are all in the correct format, and indeed in the same format as the other dates and times not highlighted.

So I’m stuck! Any ideas?

Cheers, Tom M1EYP

In reply to M1EYP:

Hi, Tom.

You have got the leading zeros for times before 1000 have you? Whne you save a .csv it loses them. Try Cells > Custom Format with 0000 in the box to automatically return them to 0700-spec if you haven’t. I hope it’s as easy as that.

I have no idea about the dates. Before my time!

GL (and let us know please :))

73, Richard

I used this function several times and I always deleted the last 2 kommas in a row when I reloaded.

@Richard: time has leading zeros in the format such as 08:15

73, Mario

Thanks for the ideas. My times were already in hh:mm format, but I tried changing them to hhmm to see if that made any difference - it didn’t! I tried changing all the dates to a different format, then changing them all back to dd/mm/yyyy, to ensure they were all consistent, but this didn’t sort that problem either.

I usually edit my CSV file in Excel, always saving it as a CSV of course. When I open it in Notepad (so I get the commas), some of my rows have commas at the end, some don’t. These instances don’t correlate with the red-highlighted errors anyway.

Tom M1EYP

In reply to M1EYP:

Compare the .csv you downloaded with the .csv you are trying to upload. Use something like WinMerge (shareware) to diff the files. The changes between the two files will be highlighted, some will be your edits, others will be where the problem exists.

http://winmerge.org/

Andy
MM0FMF

In reply to M1EYP:
Interestingly, I have had a couple of occasions recently when the database didn’t import my activation file.

I just re-entered the same data that was highlighted as incorrect and it worked. I don’t know why it happened and assumed it was an issue with how the file was parsed first time as I didn’t change the data.

Might be worth Gary investigating sometime.

73 Marc G0AZS

In reply to M1EYP:

I have had a similar experience to you Tom using Excel to edit my .csv files. I have now adopted a process which usually avoids the hassle.

After downloading the master file, I delete just the activation entries from the database that have the errors in them. Then I paste from the master csv file into new and separate clean files the entries for those activations. I then make the corrections and upload each activation separately. You may find that entries in the csv file include such characters as 1,0 - I do not paste these into the new file. I have never had an issue with the activation callsign, date, time, summit, band, mode or callsign columns - it has always been something in the notes column that has caused the error to be flagged.

Hope this helps pending a more educated response from someone more educated!

73, Gerald

In reply to M1EYP:
Are the entries in the correct date/time order - ascending / descending. The wrong way gives an error.
Nigel. G6SFP.

Thanks for all the suggestions. I got over one hurdle by putting a couple of activations back in the right order - interesting, since I started with the order given to me by the Database in the first place, when I downloaded my log for editing (“Save to File”).

I got most of the rest on by separating them as suggested above. Time consuming and cumbersome, but effective.

However, I am left with three activations that the DB keeps spitting out at me. It highlights the dates as the problem, but the problem persists with all date formats tried. Everything appears to be correct. Oh well, only three activations and 28 QSOs - I’ll just use the normal manual entry to complete the task - and then start on my chaser log…!!!

Tom M1EYP

…mind you, it takes a while to toggle the calendars back to June 2003…! :frowning:

In reply to M1EYP:

Hi, Tom.

To help those of us who have similar problems and possibly allow someone to solve the problem, is it possible to copy and paste a couple of sample lines of the .csv here for us to peruse?

73, Richard

MW1EYP/P 14/06/2003 1015 GW/NW-054 144MHZ FM M0CGA/M
MW1EYP/P 14/06/2003 1019 GW/NW-054 144MHZ FM M3SMK
MW1EYP/P 14/06/2003 1023 GW/NW-054 144MHZ FM G0MRL/M
MW1EYP/P 14/06/2003 1027 GW/NW-054 144MHZ FM M1CVL

…from the CSV file as displayed in MS Excel.

When opened in Notepad, then you get commas between the fields.

Tom

Interestingly, the same has happened in my Chaser updates - the DB doesn’t like my dates before July 2003, even though they are, from every way I can tell, formatted exactly the same as the others it has accepted.

Tom

In reply to M1EYP:

MW1EYP/P 14/06/2003 1015 GW/NW-054 144MHZ FM M0CGA/M
MW1EYP/P 14/06/2003 1019 GW/NW-054 144MHZ FM M3SMK
MW1EYP/P 14/06/2003 1023 GW/NW-054 144MHZ FM G0MRL/M
MW1EYP/P 14/06/2003 1027 GW/NW-054 144MHZ FM M1CVL

…from the CSV file as displayed in MS Excel.

When opened in Notepad, then you get commas between the fields.

Tom

Hello Tom,

I use a simple normal texteditor and write line by line (below an excerpt from my last upload):

SM5KRI/P,09/06/08,0905,SM/DA-027,10MHz,CW,SM6PXJ
SM5KRI/P,09/06/08,0909,SM/DA-027,10MHz,CW,G4OBK
SM5KRI/P,09/06/08,0909,SM/DA-027,10MHz,CW,SP6LK
etc etc until last line- this works fine for me when uploading to the database.

as you see, the year is just 08, not 2008.

Mabye this works?

or my second thought:

There could be some kind of a limit 5 years (60 months or 1825 days) back of importing a log, if you try next month (Aug '08) import a log dated august 2003, would the same problem occur?

best 73 / Chris SM5KRI

I doubt there is a five year limit, but I cannot see anything else wrong with the data or formatting, so who knows? I don’t think the date formats matter (so long as they are valid date formats). I did try using a normal text editor instead of Excel, but still got the same problems.

Anyway…

I did manage to complete the job. The last few went in using the normal manual entry webpages, and now the activator and chaser logs of both M1EYP and M3EYP are tidied up with all the S2S notes in consistent and correct format. Not that they need to be of course; you can put whatever you want however you want in the optional ‘Notes’ field. But we like just the other SOTA reference (chaser’s summit on our activator logs, activator’s summit on our chaser logs) in the case of S2S QSOs.

Right, time to face up to the reality of 44 days off work…

Tom M1EYP