[600MRG] WSPRnet database

[Bob & Jane]

Larry -

Chris pointed out to me that LibreOffice has a row limit of 1,000,000. I had 
searched on "OpenOffice". A search on LibreOffice Calc shows that the later 
versions can handle 1,048,576 rows. Still not large enough for a whole months 
worth of WSPR data but would certainly work if the non-600M data were stripped 
out.

The Basic code I wrote to get the 6,000,000 file down to 200,000 records is 
pretty simple and could be run in Excel. But, there are DOS commands (as 
explained in other E-mails) that do the same thing.
Excel won't handle the resulting 200,000 record file, so all Excel would be used 
for is to provide a platform to run the Basic program ( a macro in MS 
terminology). You would still want to use LibreOffice or some other database 
tool to manage the resulting data.

I see the LibreCalc has a completely different macro language than MS Excel. I 
don't think I want to bother to learn that as I can do what I want with Visual 
Basic, either inside Excel or in the MS Visual Basic programming development 
platform.

Bob, WB8ILI

----- Original Message ----- 
From: "Larry" <larry at w7iuv.com>
To: "Bob & Jane" <bobjane99 at sbcglobal.net>; <600mrg at w7ekb.com>
Sent: Friday, October 31, 2014 12:26 PM
Subject: Re: [600MRG] WSPRnet database


> Bob,
>
> See below:
>
>
> On 31-Oct-14 7:32 AM, Bob & Jane wrote:
>> Not sure I can add anything to what has already said but here goes.
>>
>> I downloaded the October file yesterday, so it is not the complete
>> October data.
>>
>> As stated, there are about 6.4 million records so far and the .csv file
>> is 551 MB when unZipped.
>>
>> I wrote a Visual Basic program to strip out all but the 600m records. I
>> am down to 510,150 records and about 50 MB file size.
>
>
> ###  This can be done with a one line "grep" or "find" command but VB would 
> certainly allow more sort options assuming one has the resources to deal with 
> VB.
>
>
>>
>> Then, I stripped out all but USA transmitting stations. Now I am down to
>> 200,487 records and 20 MB file size.
>>
>> Then I stripped out a few of the fields that I didn't think would be
>> necessary (only stripped out 4). Of course, I still have 200,487 records
>> but the file size is now 15 MB.
>
> ### This is where VB or some other high level programming is valuable, but 
> once the file size is reduced with the simple commands, everything else could 
> be done in a spreadsheet.
>
>
>> Open Office was suggested, but an Internet search indicates that has a
>> limit of 65,536 rows per sheet. 200,000 records is too many.
>
> ####  OO installed on a 64 bit windows 7 box has a one mega row limit, 32 bit 
> systems much less. I believe current Microsoft Office/EXCEL versions are the 
> same.
>
>
>>
>> One possibility (which I haven't tried) is to segment each transmitting
>> stations records into a separate Excel sheet. My old version of Excel
>> (2000) states the limit on the number of sheets in a workbook is limited
>> by available computer memory. Open Office has a limit of 256 sheets per
>> workbook. This would limit the types of analysis that could be performed.
>
>
> #### This is the sort of analysis that is beyond my present capabilities. I 
> understand the concepts but the details of implementation are lost in the old 
> age CRS dust......
>
> I was really hoping some spreadsheet guru would come forward with a few simple 
> macros and help out but it looks like all "modern" solutions are well beyond 
> the capabilities of us RF hardware guys. Oh Well.
>
> 73
>
> Larry - W7IUV / WH2XGP
>
>