Topics

WSPR data analysis #wspr #qcx40


Ted 2E0THH
 

I'm trying to do some WSPR analysis on transmissions my QCX made during April and May.
I've downloaded the csvs for those periods  but I am finding that MS Excel cannot deal with the colossal amount of information and only partly imports it.
Does anyone know of a program that can cope?

73s Ted
2E0THH


Alan G4ZFQ
 

I've downloaded the csvs for those periods  but I am finding that MS Excel cannot deal with the colossal amount of information and only partly imports it.
Ted,

A Database program is needed eg. Access for Windows.
Also this https://groups.io/g/ProgramsByW2JDB might work, I've not tried it.

73 Alan G4ZFQ


geoff M0ORE
 

I agree with Alan, a database program might be better however Access was an add on for the Microsoft Office suite. You could have a look at the OpenOffice version which is almost identical to Microsoft and includes the database as standard and is free to all. dot csv files can be imported into the database.


On 10/06/2020 16:39, Alan G4ZFQ wrote:
I've downloaded the csvs for those periods  but I am finding that MS Excel cannot deal with the colossal amount of information and only partly imports it.

Ted,

A Database program is needed eg. Access for Windows.
Also this https://groups.io/g/ProgramsByW2JDB might work, I've not tried it.

73 Alan G4ZFQ




Ted 2E0THH
 

Alan & Geoff

Thank you!
That should have occurred to me, I have MS Access too. 

73s Ted

2E0THH


Alan G4ZFQ
 

Ted,

This expands on the link I sent

https://wsjtx.groups.io/g/main/message/11343

As I say I've not tried it but it looks a useful program for those who worry about WSPR/PSKReporter data.

Also this https://groups.io/g/ProgramsByW2JDB is where you find it
73 Alan G4ZFQ


 

Ted :

The simple approach that I have used to deal with these massive WSPR .csv files is to pre-process the file on a Linux machine
to extract the relevant data into a new file, discarding all of the records that I don't care about. Then I transfer that  new file to my
Windows machine and then open it with Excel.

The grep utility on Linux/Unix is line-based so it reads in one line at a time and processes that very efficiently. It can search using any 
regular expression so it is a simple matter to grep the .csv searching for your callsign and then use ">" to redirect the output
to a new text file.   Something like :  grep "VE3WMB"  wspr.csv  > newcsvfile.csv    would search wspr.csv for VE3WMB and output
only those lines. The '>' takes those output lines and redirects them to a new file named newcsvfile.csv . 

You can do this on any Linux/Unix box, even a Raspberry Pi.  If you don't have a Linux machine you could create a USB stick
with a live Linux distribution and boot from that without installing Linux on your computer. Or if this seems to difficult find someone
you know who is a Linux user and get them to pre-process the file for you.  

The advantage of this approach is that you separate the "wheat from the chaff" before you try to do anything with the data.  
Using a database you are still loading in a pile of information that you really just want to discard.

Cheers

Michael VE3WMB 


Graham, VE3GTC
 

I do just what Michael has described.

You don't need a computer with Linux however but it is easier to get going.

On a Windows computer you can install many of the common *NIX command line tools available in 



and there are other WIN32 GNU command line tools sources as well.

BUT, these are not for everyone.

an alternative already exists with Windows.

There is a command line tool called FINDSTR.

Open a cmd.exe window and enter:  help findstr and you fill a simple listing of how to use this tool

findstr will search a specified file for a specified string just like using grep in Michaels example.

for example, at the c: prompt:   findstr callsignToSearchFor wsprFile.csv > results.csv

This will search wsprFile.csv for the string CallsignToSearchFor and will put the results in file results.csv

This will reduce that very large .csv file down to something more manageable that Excel can import.

Also, SQLite is a very good alternative to using Microsoft Access if you wished to go that way.

cheers, Graham ve3gtc


On Thu, Jun 11, 2020 at 1:24 PM Michael Babineau <mbabineau.ve3wmb@...> wrote:
Ted :

The simple approach that I have used to deal with these massive WSPR .csv files is to pre-process the file on a Linux machine
to extract the relevant data into a new file, discarding all of the records that I don't care about. Then I transfer that  new file to my
Windows machine and then open it with Excel.

The grep utility on Linux/Unix is line-based so it reads in one line at a time and processes that very efficiently. It can search using any 
regular expression so it is a simple matter to grep the .csv searching for your callsign and then use ">" to redirect the output
to a new text file.   Something like :  grep "VE3WMB"  wspr.csv  > newcsvfile.csv    would search wspr.csv for VE3WMB and output
only those lines. The '>' takes those output lines and redirects them to a new file named newcsvfile.csv . 

You can do this on any Linux/Unix box, even a Raspberry Pi.  If you don't have a Linux machine you could create a USB stick
with a live Linux distribution and boot from that without installing Linux on your computer. Or if this seems to difficult find someone
you know who is a Linux user and get them to pre-process the file for you.  

The advantage of this approach is that you separate the "wheat from the chaff" before you try to do anything with the data.  
Using a database you are still loading in a pile of information that you really just want to discard.

Cheers

Michael VE3WMB 


Hans Summers
 

Hi Ted, all

I have taken a different approach whenever I needed to analyze a download of a month's worth of WSPRnet data. I used Excel with some VBA code, that opens the WSPRnet downloaded csv file and reads it sequentially one line at a time. Then I implement a filter for whatever rows I want to look at, and any row that matches, I write that out into another csv file. 

I've always done it this way. 

To an extent it depends what software you have available and what you're familiar with. I had Excel available and am familiar with VBA having used it professionally for many years. So that was the solution I chose. 

Ted let me know if you're interested and I can find the VBA code I used and email it to you.

73 Hans G0UPL 

On Thu, Jun 11, 2020, 16:51 Graham, VE3GTC <colonelkrypton@...> wrote:
I do just what Michael has described.

You don't need a computer with Linux however but it is easier to get going.

On a Windows computer you can install many of the common *NIX command line tools available in 



and there are other WIN32 GNU command line tools sources as well.

BUT, these are not for everyone.

an alternative already exists with Windows.

There is a command line tool called FINDSTR.

Open a cmd.exe window and enter:  help findstr and you fill a simple listing of how to use this tool

findstr will search a specified file for a specified string just like using grep in Michaels example.

for example, at the c: prompt:   findstr callsignToSearchFor wsprFile.csv > results.csv

This will search wsprFile.csv for the string CallsignToSearchFor and will put the results in file results.csv

This will reduce that very large .csv file down to something more manageable that Excel can import.

Also, SQLite is a very good alternative to using Microsoft Access if you wished to go that way.

cheers, Graham ve3gtc


On Thu, Jun 11, 2020 at 1:24 PM Michael Babineau <mbabineau.ve3wmb@...> wrote:
Ted :

The simple approach that I have used to deal with these massive WSPR .csv files is to pre-process the file on a Linux machine
to extract the relevant data into a new file, discarding all of the records that I don't care about. Then I transfer that  new file to my
Windows machine and then open it with Excel.

The grep utility on Linux/Unix is line-based so it reads in one line at a time and processes that very efficiently. It can search using any 
regular expression so it is a simple matter to grep the .csv searching for your callsign and then use ">" to redirect the output
to a new text file.   Something like :  grep "VE3WMB"  wspr.csv  > newcsvfile.csv    would search wspr.csv for VE3WMB and output
only those lines. The '>' takes those output lines and redirects them to a new file named newcsvfile.csv . 

You can do this on any Linux/Unix box, even a Raspberry Pi.  If you don't have a Linux machine you could create a USB stick
with a live Linux distribution and boot from that without installing Linux on your computer. Or if this seems to difficult find someone
you know who is a Linux user and get them to pre-process the file for you.  

The advantage of this approach is that you separate the "wheat from the chaff" before you try to do anything with the data.  
Using a database you are still loading in a pile of information that you really just want to discard.

Cheers

Michael VE3WMB 


Ted 2E0THH
 

Hi Hans

That would be terrific, thank you! I was just pondering where to start this weekend.

I have kept the same rig power/antenna setup for a few months now and I just want to compare my WSPR data against other propagation data available in the vain hope that I will begin to understand this particular branch of science a little more.

Thanks to all that have offered advice on this issue.

73s Ted
2E0THH


Andy Brilleaux [O.B.E. pending] <punkbiscuit@...>
 

On Thu, Jun 11, 2020 at 02:09 PM, Michael Babineau wrote:
grep "VE3WMB"  wspr.csv  > newcsvfile.csv
Worked a treat here, thanks Michael.

Took about 5 minutes to process a 6.4GB  CSV file and extract the almost 300,000 entries
that my 3 x QRP Labs rigs had generated back in May 2020 ;-)

That in turn generated a 25Mb file that was easily opened by Libre Office on Linux Mint PC.

I suspect that my Raspberry Pi3B+ would  take only marginally longer when comparing the
spec of my laptop to the Pi.

- Andy -


Trevor VK3PD
 

Dont know if this helps but  libreoffice has lots of  advantages over openoffice and it runs on linux


Ted 2E0THH
 

Hi Graham, Michael and Alan

I was just about to set about this task with MS Access and thought I would quickly try the Windows Command line approach before I started.

It's brilliant! Saved me a morning's work, thank you! The results loaded up into MS Excel without problem.

My example:
C:\Users\micro\Desktop>FINDSTR 2E0THH wsprspots-2020-05.csv > 202005_WSPR_DB_results.csv

For others, note that I performed the processing on my desktop. DOS processed files can get easily lost unless you define the directory before you start.

So first I copied the large WSPR download onto my desktop
Then in the command window, I changed the directory using the cd command (change directory) so that all actions would take place on my desktop, thus
C:\>cd C:\users\micro\desktop (you will need to modify this line to match your own desktop location. An easy way to find this is to right click any existing file on your desktop, click properties and a window opens. There you will see your desktop location)

So now, when the command executes, it places results csv on the desktop too.

Thanks and I hope this extra info helps.

73s Ted
2E0THH


Ted 2E0THH
 

I'm probably being incredibly dumb but having successfully extracted all my transmissions into separate csv file, for the life of me I cannot fathom the format of the first 2 fields. They appear in the list as scientific numbers.
I am assuming that the first field is probably a time or date but I cannot find a time/date format in Excel that correctly displays it in anything other than a huge row of hash symbols.  The second field value remains static for every record.

Any help gratefully received!

73s Ted
2E0THH


geoff M0ORE
 

Try changing the number format to general and make sure the column is wide enough to show all the information. If you click on a box showing all #'s, see what the entry line at the top of the sheet is showing, it may be a function or a formula.

On 14/06/2020 17:31, Ted 2E0THH wrote:
I'm probably being incredibly dumb but having successfully extracted all my transmissions into separate csv file, for the life of me I cannot fathom the format of the first 2 fields. They appear in the list as scientific numbers.
I am assuming that the first field is probably a time or date but I cannot find a time/date format in Excel that correctly displays it in anything other than a huge row of hash symbols.  The second field value remains static for every record.

Any help gratefully received!

73s Ted
2E0THH


Andy Brilleaux [O.B.E. pending] <punkbiscuit@...>
 

It's Unix time.


Andy Brilleaux [O.B.E. pending] <punkbiscuit@...>
 

Sorry,my silly phone editor list some text.

It's spot ID and Unix time.

There's a simple formula for converting Unix
time to real time somewhere in the download
pages iirc.


Tom N9GVP
 

Ted,

Info about first two fields of the  wspr csv files is given near the bottom of the page here 

The second column contains the date and time information of the spots.  But it's formatted as a "unix time stamp".  You can create another column in your spreadsheet to display this with a normal date and time formatting.  See, for example, this link

Hope this helps.

Tom
N9GVP


kq2q2000
 

Hello
Try increasing the width of the problem columbs
excel will put hash marks when the viewed data is to large for viewing. 

bob kq2q


Andy Brilleaux [O.B.E. pending] <punkbiscuit@...>
 

Not sure if this will help.

You should be able to use this spreadsheet to automatically convert Unix time to normal time.

Once you have your .csv file of wanted wspr spots, import the .csv and the correct time and date
will appear in column P.

Maybe other clever people could add other stuff, but I'm not that clever ;-)


Arv Evans
 

On Sun, Jun 14, 2020 at 5:09 PM Andy Brilleaux [O.B.E. pending] via groups.io <punkbiscuit=googlemail.com@groups.io> wrote:
Not sure if this will help.

You should be able to use this spreadsheet to automatically convert Unix time to normal time.

Once you have your .csv file of wanted wspr spots, import the .csv and the correct time and date
will appear in column P.

Maybe other clever people could add other stuff, but I'm not that clever ;-)