Sampling from a Text File

[kparadise]

Hello,

I have 10 texts files. These text files contain a different amount of records within them....anywhere from 100,000 records, to 6,000,000 records.

I need to select 16,000 random records from each of the 10 text files.

For those text files which have under 1,000,000 records, I can easily load them into excel...do a RAND() function and then sort to pull my sample. But what or how would I do this for the text files which contain more that the excel limit?

OR

Is there another way easier, that I am not aware of?

 

[Fennek]

Hello,

in the case of more than 1 mio lines, either PowerQuery or the following function could help:

Function ReadTxtFile(ByRef strPath As String) As String
    Dim intFF As Long
  
    intFF = FreeFile()
    Open strPath For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] 
        ReadTxtFile = Space$(LOF(intFF))
        Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] , , ReadTxtFile
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF]#intFF[/URL] 
End Function

THe next step to apply the split-function with the linebreak-character (vblfcr, vblf, vbcr). In the resulting array a sampling per line is possible.

I am not really aware of the limitations (depending on ram 4-6 GB??), so please report if successful.

regards

 

[kparadise]

I am sorry, but I do not have an idea of where to enter this code.

I have zero knowledge about this type of execution.

More information, the texts files contain two columns, with headers. In my sample files, I will need the header (or not) and both columns of data. I do not see in that code about the number 16,000 which is my sample number.