Query large CSV file as a data source?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Query large CSV file as a data source?

J. Landman Gay via use-livecode
Hi folks,
I’ve a 3GB CSV file with 18M rows from which I need to create a subset CSV file for the rows containing tString. The following script results in LiveCode becoming unresponsive, so I’m wondering if I’m missing an obvious trick or what alternative approach I might take...
on mouseUp pButtonNumber

-- Select CSV File

answer file prompt as sheet

put it into tFile

put tFile into field "File"

-- Process CSV extract

put URL ("file:" & tFile) into tText

put line 1 of tText into field "List"

filter lines of tText with "*tString*"

put tText into field tList

end mouseUp

I don’t normally deal with large data sets, so maybe LiveCode isn’t the best tool for this but Excel 365 seems to only offer CSV file import, not query.

Any thoughts greatly appreciated.

Thanks & regards,
Keith
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Query large CSV file as a data source?

J. Landman Gay via use-livecode
Couldn't you read the file partially

you would open the file for read

        open file tFile for read

then in a repeat loop you could read e.g. 10000 lines and process the data

        read from file tFile for 10000 lines


Just a thought.


-
Matthias Rebbe
Life Is Too Short For Boring Code

> Am 03.11.2020 um 13:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>
> Hi folks,
> I’ve a 3GB CSV file with 18M rows from which I need to create a subset CSV file for the rows containing tString. The following script results in LiveCode becoming unresponsive, so I’m wondering if I’m missing an obvious trick or what alternative approach I might take...
> on mouseUp pButtonNumber
>
> -- Select CSV File
>
> answer file prompt as sheet
>
> put it into tFile
>
> put tFile into field "File"
>
> -- Process CSV extract
>
> put URL ("file:" & tFile) into tText
>
> put line 1 of tText into field "List"
>
> filter lines of tText with "*tString*"
>
> put tText into field tList
>
> end mouseUp
>
> I don’t normally deal with large data sets, so maybe LiveCode isn’t the best tool for this but Excel 365 seems to only offer CSV file import, not query.
>
> Any thoughts greatly appreciated.
>
> Thanks & regards,
> Keith
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Query large CSV file as a data source?

J. Landman Gay via use-livecode
Thank you Matthias, I wasn’t aware of that ability to open the file and read its contents straight from the disk.

A quick experiment looks positive - no hanging and fast access - so I just need to work on the loop.

Much obliged.

Best,
Keith  

> On 3 Nov 2020, at 13:37, matthias rebbe via use-livecode <[hidden email]> wrote:
>
> Couldn't you read the file partially
>
> you would open the file for read
>
> open file tFile for read
>
> then in a repeat loop you could read e.g. 10000 lines and process the data
>
> read from file tFile for 10000 lines
>
>
> Just a thought.
>
>
> -
> Matthias Rebbe
> Life Is Too Short For Boring Code
>
>> Am 03.11.2020 um 13:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>>
>> Hi folks,
>> I’ve a 3GB CSV file with 18M rows from which I need to create a subset CSV file for the rows containing tString. The following script results in LiveCode becoming unresponsive, so I’m wondering if I’m missing an obvious trick or what alternative approach I might take...
>> on mouseUp pButtonNumber
>>
>> -- Select CSV File
>>
>> answer file prompt as sheet
>>
>> put it into tFile
>>
>> put tFile into field "File"
>>
>> -- Process CSV extract
>>
>> put URL ("file:" & tFile) into tText
>>
>> put line 1 of tText into field "List"
>>
>> filter lines of tText with "*tString*"
>>
>> put tText into field tList
>>
>> end mouseUp
>>
>> I don’t normally deal with large data sets, so maybe LiveCode isn’t the best tool for this but Excel 365 seems to only offer CSV file import, not query.
>>
>> Any thoughts greatly appreciated.
>>
>> Thanks & regards,
>> Keith
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Query large CSV file as a data source?

J. Landman Gay via use-livecode
Regarding the loop.

you could do a

put the detailed files

to get the file size of that csv file.

With that size you would now how often you have to run the loop





-
Matthias Rebbe
Life Is Too Short For Boring Code

> Am 03.11.2020 um 15:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>
> Thank you Matthias, I wasn’t aware of that ability to open the file and read its contents straight from the disk.
>
> A quick experiment looks positive - no hanging and fast access - so I just need to work on the loop.
>
> Much obliged.
>
> Best,
> Keith  
>
>> On 3 Nov 2020, at 13:37, matthias rebbe via use-livecode <[hidden email]> wrote:
>>
>> Couldn't you read the file partially
>>
>> you would open the file for read
>>
>> open file tFile for read
>>
>> then in a repeat loop you could read e.g. 10000 lines and process the data
>>
>> read from file tFile for 10000 lines
>>
>>
>> Just a thought.
>>
>>
>> -
>> Matthias Rebbe
>> Life Is Too Short For Boring Code
>>
>>> Am 03.11.2020 um 13:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>>>
>>> Hi folks,
>>> I’ve a 3GB CSV file with 18M rows from which I need to create a subset CSV file for the rows containing tString. The following script results in LiveCode becoming unresponsive, so I’m wondering if I’m missing an obvious trick or what alternative approach I might take...
>>> on mouseUp pButtonNumber
>>>
>>> -- Select CSV File
>>>
>>> answer file prompt as sheet
>>>
>>> put it into tFile
>>>
>>> put tFile into field "File"
>>>
>>> -- Process CSV extract
>>>
>>> put URL ("file:" & tFile) into tText
>>>
>>> put line 1 of tText into field "List"
>>>
>>> filter lines of tText with "*tString*"
>>>
>>> put tText into field tList
>>>
>>> end mouseUp
>>>
>>> I don’t normally deal with large data sets, so maybe LiveCode isn’t the best tool for this but Excel 365 seems to only offer CSV file import, not query.
>>>
>>> Any thoughts greatly appreciated.
>>>
>>> Thanks & regards,
>>> Keith
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Query large CSV file as a data source?

J. Landman Gay via use-livecode
Just read for 10,000 lines and check for empty each time.

Bob S


> On Nov 3, 2020, at 7:09 AM, matthias rebbe via use-livecode <[hidden email]> wrote:
>
> Regarding the loop.
>
> you could do a
>
> put the detailed files
>
> to get the file size of that csv file.
>
> With that size you would now how often you have to run the loop
>
>
>
>
>
> -
> Matthias Rebbe
> Life Is Too Short For Boring Code
>
>> Am 03.11.2020 um 15:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>>
>> Thank you Matthias, I wasn’t aware of that ability to open the file and read its contents straight from the disk.
>>
>> A quick experiment looks positive - no hanging and fast access - so I just need to work on the loop.
>>
>> Much obliged.
>>
>> Best,
>> Keith  
>>
>>> On 3 Nov 2020, at 13:37, matthias rebbe via use-livecode <[hidden email]> wrote:
>>>
>>> Couldn't you read the file partially
>>>
>>> you would open the file for read
>>>
>>> open file tFile for read
>>>
>>> then in a repeat loop you could read e.g. 10000 lines and process the data
>>>
>>> read from file tFile for 10000 lines
>>>
>>>
>>> Just a thought.
>>>
>>>
>>> -
>>> Matthias Rebbe
>>> Life Is Too Short For Boring Code
>>>
>>>> Am 03.11.2020 um 13:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>>>>
>>>> Hi folks,
>>>> I’ve a 3GB CSV file with 18M rows from which I need to create a subset CSV file for the rows containing tString. The following script results in LiveCode becoming unresponsive, so I’m wondering if I’m missing an obvious trick or what alternative approach I might take...
>>>> on mouseUp pButtonNumber
>>>>
>>>> -- Select CSV File
>>>>
>>>> answer file prompt as sheet
>>>>
>>>> put it into tFile
>>>>
>>>> put tFile into field "File"
>>>>
>>>> -- Process CSV extract
>>>>
>>>> put URL ("file:" & tFile) into tText
>>>>
>>>> put line 1 of tText into field "List"
>>>>
>>>> filter lines of tText with "*tString*"
>>>>
>>>> put tText into field tList
>>>>
>>>> end mouseUp
>>>>
>>>> I don’t normally deal with large data sets, so maybe LiveCode isn’t the best tool for this but Excel 365 seems to only offer CSV file import, not query.
>>>>
>>>> Any thoughts greatly appreciated.
>>>>
>>>> Thanks & regards,
>>>> Keith
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> [hidden email]
>>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>>
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Query large CSV file as a data source?

J. Landman Gay via use-livecode



> Am 03.11.2020 um 23:02 schrieb Bob Sneidar via use-livecode <[hidden email]>:
>
> Just read for 10,000 lines and check for empty each time.
>

To be honest, i've never used the read file for ... lines.
I was expecting that there might be an error if one tries to read more lines than there are left for read
Good to know that one can just read in a loop without calculating the loop count first.


-
Matthias Rebbe
Life Is Too Short For Boring Code

> Bob S
>
>
>> On Nov 3, 2020, at 7:09 AM, matthias rebbe via use-livecode <[hidden email]> wrote:
>>
>> Regarding the loop.
>>
>> you could do a
>>
>> put the detailed files
>>
>> to get the file size of that csv file.
>>
>> With that size you would now how often you have to run the loop
>>
>>
>>
>>
>>
>> -
>> Matthias Rebbe
>> Life Is Too Short For Boring Code
>>
>>> Am 03.11.2020 um 15:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>>>
>>> Thank you Matthias, I wasn’t aware of that ability to open the file and read its contents straight from the disk.
>>>
>>> A quick experiment looks positive - no hanging and fast access - so I just need to work on the loop.
>>>
>>> Much obliged.
>>>
>>> Best,
>>> Keith  
>>>
>>>> On 3 Nov 2020, at 13:37, matthias rebbe via use-livecode <[hidden email]> wrote:
>>>>
>>>> Couldn't you read the file partially
>>>>
>>>> you would open the file for read
>>>>
>>>> open file tFile for read
>>>>
>>>> then in a repeat loop you could read e.g. 10000 lines and process the data
>>>>
>>>> read from file tFile for 10000 lines
>>>>
>>>>
>>>> Just a thought.
>>>>
>>>>
>>>> -
>>>> Matthias Rebbe
>>>> Life Is Too Short For Boring Code
>>>>
>>>>> Am 03.11.2020 um 13:14 schrieb Keith Clarke via use-livecode <[hidden email]>:
>>>>>
>>>>> Hi folks,
>>>>> I’ve a 3GB CSV file with 18M rows from which I need to create a subset CSV file for the rows containing tString. The following script results in LiveCode becoming unresponsive, so I’m wondering if I’m missing an obvious trick or what alternative approach I might take...
>>>>> on mouseUp pButtonNumber
>>>>>
>>>>> -- Select CSV File
>>>>>
>>>>> answer file prompt as sheet
>>>>>
>>>>> put it into tFile
>>>>>
>>>>> put tFile into field "File"
>>>>>
>>>>> -- Process CSV extract
>>>>>
>>>>> put URL ("file:" & tFile) into tText
>>>>>
>>>>> put line 1 of tText into field "List"
>>>>>
>>>>> filter lines of tText with "*tString*"
>>>>>
>>>>> put tText into field tList
>>>>>
>>>>> end mouseUp
>>>>>
>>>>> I don’t normally deal with large data sets, so maybe LiveCode isn’t the best tool for this but Excel 365 seems to only offer CSV file import, not query.
>>>>>
>>>>> Any thoughts greatly appreciated.
>>>>>
>>>>> Thanks & regards,
>>>>> Keith
>>>>> _______________________________________________
>>>>> use-livecode mailing list
>>>>> [hidden email]
>>>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>
>>>>
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> [hidden email]
>>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>>
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode