WSPR – Weak Signal Propagation Reporter: how to handle its large CSV files

If you landed on this page you must have tried, with no joy, to crunch the data contained in the .csv files downloadable from the WSPRnet website. The problem is that Excel is unable to manage data volumes in excess of about one million lines. What follows is a workaround of this limitation that will enable you to open and manage this large amount of data in Excel. I used Excel 2016 to test my method and I am not sure of what the equivalent steps are on older versions (or newer versions, if you are reading this article a long time after I published it).

The wspr compressed .csv file for May 2016 is very large and in excess of 500MB; once decompressed, it becomes about 2.4GB in volume and – sit down before you read this – about 29.6 million lines! I opened large files in the past, at the time when Excel would only be able to deal with 65k lines, however nothing as large as this file.

The way around this problem is to create a query linked to the .csv file, this way you can handle more then the 1 million or so lines that Excel is able to load in a worksheet – however I confess that until I tried I wasn’t sure that it would work with nearly 30 million lines. This is how to proceed:

  1. Open a new blank document in Excel
  2. Click on the Data tab and then open the New Query option
  3. A drop-down menu will appear, pick “From File” and “From CSV”
  4. Locate your file and press Import.
  5. When the new window opens, select the Load drop-down and pick “Load To…”
  6. This will lead you to yet another window. Here you are going to select “Only Create Connection” and tick the box “Add this data to the Data Model”.
  7. This will set your computer to work. You’ll need a beefy machine to do this in reasonable time, I’ve done this on an i7 6700HQ CPU laptop with an SSD and 16GB RAM, it has done the job in about 5 minutes. You can go and brew a coffee at this point but remember to make sure that the cooling fans of your computer work well.
  8. When done, go to Power Pivot at the top of your tabs. If you don’t have Power Pivot at the top, press the Manage Data Model button under Data and you’ll end up with something similar to a spreadsheet with filtering options.

The rest is just ordinary data management. You can filter, pivot and use some other basic spreadsheet functionalities. Once you filtered the lines that you need, you can copy and paste in an Excel sheet. In my example, I extracted all the entries for reporter K9AN which, unsurprisingly, returned about 500k entries. This “small” filtered amount of data (and I doubt that any average user will approach K9AN’s volume) can now be easily handled in Excel.

I hope you will find this useful.

Skyloop Antenna (delta configuration)

At last, I had a chance of testing my new loop antenna. The loop consists of a copper wire hooked to three chimneys positioned in an almost equilateral triangle configuration, the length of the wire is about 42-43 metres and about 7 metres above ground level.

I fed the antenna with approximately 7-8 metres of 450Ω twin line and terminated this feeding section with a 1:1 current balun. The unbalanced side of the balun is connected to approximately 2 metres of RG8 Mini coaxial cable and, through it, to an automatic matching unit and then my IC-7100.

Despite the not at all accurate configuration, the whole system appears to work reasonably well. The QRM is very low, aside from the noise generated by some cheap LED bulbs in the house. I am learning the hard way how much noise certain bulbs produce – if you like LED light and have an antenna just above the house, buy one first and test it for noise across the RF spectrum. However, bulbs apart, the antenna is very quiet on pretty much all bands when compared to other types of wire antennae.

I am not yet able to confidently tell how well it performs as I still have to compare it with something else. I am now looking at installing an OCF dipole nearby so that I can then switch from one to the other and see which one performs better. I tested it a bit these days with WSPR. With 5W TX power, the only place on the globe that I haven’t reached is South America. From Vladivostok to Cape Town, going through Australia, the US and New Zealand, I managed to collect reports but I have no idea of how many reports I could get, say with a magnetic loop. I know I don’t sound very enthusiastic about it, but this is pretty much what everyone with a wire in their backyard and 5W claims when they use WSPR. In the end, a side-by-side comparison with other antennae will tell me how good it is.

With this hobby you never get bored!

Diamond X200N Antenna

I installed a Diamond X200N on the roof and I have to say I am rather impressed by this antenna. The installation is the typical Diamond setup, you see one you see them all. It’s very simple to install.

The reason I chose this model over the others in the Diamond range (and all their clones) is that it looked like a good compromise between size and performance. The X50, slightly smaller at circa 170cm, was another candidate however I read in the specification that in VHF it’s a single 6/8 wave element; the X300, slightly bigger at 300cm, was a 2x 5/8 wave in VHF and had a similar performance to the X200, also a 2x 5/8. There is 0.5 dB performance difference on the paper and apart from the fact that I don’t believe this difference exists, half dB can hardly make much difference. The extra 5/8 UHF element must be the selling point of the X300 but all in all I thought that 4x 5/8 wave elements in UHF would be more than enough. So I picked the X200. The “N” suffix means it comes with an N type socket, so you need to terminate your cable with an N type plug. I am not sure this makes a huge difference in the real world, however there was no SO239 option available so I went for the N option.

As I was saying, this antenna feels very impressive. I installed at about 7 metres above ground level with clear view of the countryside between N and E. I then ran a quick sweep of both the 2m and 70cm to bands to check the signal of the local repeaters. I was shocked when I managed to trigger a repeater located about 85Km N from where I live – with probably less than 5W (5% power out of my IC-7100). Well beyond my initial expectations. Other rather distant repeaters, say between 50Km and 100Km, analogue and digital, can be accessed with the same power level, both in VHF and UHF.

Under the X200 there is a horizontal Delta Loop, connected to a 450 Ohm balanced line. This will most likely be the subject of my next post. I haven’t properly tested it yet as I need to create some space for a new shack and properly fix the balanced feedline so that it doesn’t couple with other metal objects. Stay tuned.

XRF400 retired as of 16 March 2016

Due to other commitments I have been unable to develop and maintain XRF400 since mid 2015. I kept the reflector coasting for almost a year however due to lack of use and community interest I came to the conclusion that it is time to move on: XRF400 is officially retired as of 16 March 2016.

From Russia with love

Summer means travel, and this year I will be in Russia until Fri 31 July. I packed my IC-7100 in the suitcase and I hope today I will be able to find a pole for a temporary wire antenna on 10-15-20 metres (I’ll leave 40 and 80 for another day) to operate either from a fixed base or portable. All going well I should be able to operate as RA/M0ZZM and somehow advertise my presence on a DX cluster.

One thing I’d like to do is experiment with direct D-STAR contact in HF, this will have to be on 10 metres (I was thinking of the 29.4 MHz region) as it is the only band allowing the 6 KHz bandwidth required to operate. So if you are also looking forward to test D-STAR under propagation conditions, my QTH locator will be around KO82, which is approximately 400 Km south of Moscow or about 2000 Km distance from central and southern Europe.

If you wish to schedule a QSO, please add a comment to this post.

How to reconvert a Linux-formatted SD Card back to Windows

If you have been playing with a Raspberry Pi and the usual SD cards, chances are that you also found yourself unable to reconvert your SD card back to Windows for normal use. When you try to reformat the card in Windows, you will most likely get to a point where you have only one primary partition with all the rest of the space on the card free, but you are unable to expand the partition to cover the whole available space. You will have something like 50 to 100 MB space in your FAT32 partition and many GB space that can’t be utilised.

Well, your frustration is over. Here is how to proceed to repartition your card as you wish – in Windows. No extra tools or software is required. In the below explanation, when you see [square brackets] you don’t need to type the brackets, just the content.

Before you proceed, the usual disclaimer: I tried this on my system and it worked well, however you do this at your risk and peril and if anything happens to your computer or your card while following these instructions, I am not responsible for it.

STEP 1: insert the SD card in your reader so that the computer can recognise it, then open a command prompt and type [diskpart]. You will be taken to a window where the prompt says DISKPART> or something similar.

STEP 2: display the list of volumes by typing [list disk]. You will see a list of disks, probably something like Disk 0 and Disk 1, with the capacity in GB for each disk.

STEP 3: select the relevant volume. In my case, Disk 0 was the computer’s HDD, Disk 1 was the SD card. I recognised it because the capacity in GB matched that of the card and I had no other storage devices connected to the system other than my HDD, which I also could recognise the same way (by looking at the capacity in GB). If you are still not sure, try to run [diskpart] and [list disk] without the SD card in the slot, and then again with the card; you should notice the difference and be able to identify the correct volume. Back to business, to select the correct volume, assuming (once again, check carefully your system first when you try on your computer!) that the SD card is Disk 1, type [select disk 1].

STEP 4: clean the unit by typing [clean]. Make sure you selected the right disk when you type this, as this will clear all your partition table!

STEP 5: create a new partition by typing [create partition primary] and format it by typing [format fs=fat32 quick]. This will quick-format your partition as FAT32.

STEP 6: type [assign] and your card will immediately become ready to use.