Re: [Qgis-user] editing and saving very large table
Hi, you can use the DB Manager for custom layer queries. You can create SQL queries and add those to the layer tree, more information can be found here https://docs.qgis.org/3.34/en/docs/user_manual/plugins/core_plugins/plugins_db_manager.html And of course, ordering queries based on custom criteria can be done there via ORDER BY clause ... HTH Frank Von: QGIS-User im Auftrag von Gordon Wakelin-King via QGIS-User Gesendet: Mittwoch, 13. März 2024 00:20 An: chris hermansen ; Michael.Dodd Cc: Frederik-Matti Bartels (CAPSA) ; qgis-user@lists.osgeo.org Betreff: Re: [Qgis-user] editing and saving very large table Surely this is something that can be done easily with an SQL Select with Order-by, in one of the SQL variants that QGIS can use? I’m asking for my own benefit too. Having come from the MapInfo environment I would reach for an SQL Select to do this task, but I’m less familiar with SQL in QGIS. Gordon Wakelin-King From: QGIS-User On Behalf Of chris hermansen via QGIS-User Sent: Wednesday, March 13, 2024 8:02 AM To: Michael.Dodd Cc: Frederik-Matti Bartels (CAPSA) ; qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] editing and saving very large table Michael and list, On Tue, Mar 12, 2024 at 11:48 AM Michael.Dodd mailto:michael.d...@open.ac.uk>> wrote: In this particular instance the data are not spatial data, at this stage at least. There is no link to location. The data does have a hierarchy according to several columns, by sorting I can put the data into a suitable order for editing in further information into an extra column. Without sorting the places where the extra info and which specific extra info are impossible to find and check as they are scattered all over. I tried putting the information in using the field calculator and even though the simple expression seemed correct and there was no error message it did not put the information in. So I was even thinking of putting the information in by hand once sorted although suspect there may be issues with this too. I was wondering about sorting the whole file then copying out/exporting chunks of a million rows at a time to go into a normal spreadsheet. If the copying out/export would not work directly then saving the sorted version of the data and chopping up using e.g. notepad++ and editing with spreadsheet. It is important to do the sorting before chopping up into the 5 separate files. It is only later that spatial information is attached, that is not relevant at this stage as the spatial data will not be attached to all rows. Ok so you are sorting a text file, presumably something like a .CSV. Perhaps you are working on Windows. What are your options to sort the file before you put it in QGIS? It seems your spreadsheet won't accommodate the number of lines in your file; what about the "sort" utility in say Windows PowerShell? Here's an article that talks about sorting .CSV files that way. https://stackoverflow.com/questions/23603397/sorting-csv-using-windows-sort which in turn refers to this article https://devblogs.microsoft.com/scripting/hey-scripting-guy-how-can-i-use-windows-powershell-to-sort-a-csv-file/ Alternatively, if you're using Linux or (I would imagine) OS/X, you can use the Posix "sort" utility. -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
Surely this is something that can be done easily with an SQL Select with Order-by, in one of the SQL variants that QGIS can use? I’m asking for my own benefit too. Having come from the MapInfo environment I would reach for an SQL Select to do this task, but I’m less familiar with SQL in QGIS. Gordon Wakelin-King From: QGIS-User On Behalf Of chris hermansen via QGIS-User Sent: Wednesday, March 13, 2024 8:02 AM To: Michael.Dodd Cc: Frederik-Matti Bartels (CAPSA) ; qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] editing and saving very large table Michael and list, On Tue, Mar 12, 2024 at 11:48 AM Michael.Dodd mailto:michael.d...@open.ac.uk>> wrote: In this particular instance the data are not spatial data, at this stage at least. There is no link to location. The data does have a hierarchy according to several columns, by sorting I can put the data into a suitable order for editing in further information into an extra column. Without sorting the places where the extra info and which specific extra info are impossible to find and check as they are scattered all over. I tried putting the information in using the field calculator and even though the simple expression seemed correct and there was no error message it did not put the information in. So I was even thinking of putting the information in by hand once sorted although suspect there may be issues with this too. I was wondering about sorting the whole file then copying out/exporting chunks of a million rows at a time to go into a normal spreadsheet. If the copying out/export would not work directly then saving the sorted version of the data and chopping up using e.g. notepad++ and editing with spreadsheet. It is important to do the sorting before chopping up into the 5 separate files. It is only later that spatial information is attached, that is not relevant at this stage as the spatial data will not be attached to all rows. Ok so you are sorting a text file, presumably something like a .CSV. Perhaps you are working on Windows. What are your options to sort the file before you put it in QGIS? It seems your spreadsheet won't accommodate the number of lines in your file; what about the "sort" utility in say Windows PowerShell? Here's an article that talks about sorting .CSV files that way. https://stackoverflow.com/questions/23603397/sorting-csv-using-windows-sort which in turn refers to this article https://devblogs.microsoft.com/scripting/hey-scripting-guy-how-can-i-use-windows-powershell-to-sort-a-csv-file/ Alternatively, if you're using Linux or (I would imagine) OS/X, you can use the Posix "sort" utility. -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
Michael and list, On Tue, Mar 12, 2024 at 11:48 AM Michael.Dodd wrote: > In this particular instance the data are not spatial data, at this stage > at least. There is no link to location. > > > > The data does have a hierarchy according to several columns, by sorting I > can put the data into a suitable order for editing in further information > into an extra column. Without sorting the places where the extra info and > which specific extra info are impossible to find and check as they are > scattered all over. > > > > I tried putting the information in using the field calculator and even > though the simple expression seemed correct and there was no error message > it did not put the information in. So I was even thinking of putting the > information in by hand once sorted although suspect there may be issues > with this too. > > > > I was wondering about sorting the whole file then copying out/exporting > chunks of a million rows at a time to go into a normal spreadsheet. If the > copying out/export would not work directly then saving the sorted version > of the data and chopping up using e.g. notepad++ and editing with > spreadsheet. It is important to do the sorting before chopping up into the > 5 separate files. > > > > It is only later that spatial information is attached, that is not > relevant at this stage as the spatial data will not be attached to all rows. > > Ok so you are sorting a text file, presumably something like a .CSV. Perhaps you are working on Windows. What are your options to sort the file before you put it in QGIS? It seems your spreadsheet won't accommodate the number of lines in your file; what about the "sort" utility in say Windows PowerShell? Here's an article that talks about sorting .CSV files that way. https://stackoverflow.com/questions/23603397/sorting-csv-using-windows-sort which in turn refers to this article https://devblogs.microsoft.com/scripting/hey-scripting-guy-how-can-i-use-windows-powershell-to-sort-a-csv-file/ Alternatively, if you're using Linux or (I would imagine) OS/X, you can use the Posix "sort" utility. -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
In this particular instance the data are not spatial data, at this stage at least. There is no link to location. The data does have a hierarchy according to several columns, by sorting I can put the data into a suitable order for editing in further information into an extra column. Without sorting the places where the extra info and which specific extra info are impossible to find and check as they are scattered all over. I tried putting the information in using the field calculator and even though the simple expression seemed correct and there was no error message it did not put the information in. So I was even thinking of putting the information in by hand once sorted although suspect there may be issues with this too. I was wondering about sorting the whole file then copying out/exporting chunks of a million rows at a time to go into a normal spreadsheet. If the copying out/export would not work directly then saving the sorted version of the data and chopping up using e.g. notepad++ and editing with spreadsheet. It is important to do the sorting before chopping up into the 5 separate files. It is only later that spatial information is attached, that is not relevant at this stage as the spatial data will not be attached to all rows. From: chris hermansen Sent: Tuesday, March 12, 2024 2:49 PM To: Michael.Dodd Cc: Frederik-Matti Bartels (CAPSA) ; qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] editing and saving very large table External email: if the sender or content looks suspicious, please click the Report Message icon, or forward it to report-phishing Michael and list, On Tue, Mar 12, 2024 at 5:31 AM Michael.Dodd via QGIS-User mailto:qgis-user@lists.osgeo.org>> wrote: Thanks, can you suggest how as the refactor fields help is not clear, to say the least, about saving after sorting. I sorted the data (5 million rows) by a couple of the columns, just by clicking on them, then wanted to save in the new order but this 'save in the new order' seems impossible. Michael, I for one am puzzled as to what you sorted. Was this a point, polygon, line attribute table? Or some .csv layer? Or...? And how did you sort it? Opened it in the attribute table editor and clicked on columns? If so, as far as I know, sorting in this editor is just for convenience in reviewing data. The physical data order is determined by the link to the spatial features. Maybe you could also tell us what you're trying to achieve by sorting? -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
Michael and list, On Tue, Mar 12, 2024 at 5:31 AM Michael.Dodd via QGIS-User < qgis-user@lists.osgeo.org> wrote: > Thanks, can you suggest how as the refactor fields help is not clear, to > say the least, about saving after sorting. > > I sorted the data (5 million rows) by a couple of the columns, just by > clicking on them, then wanted to save in the new order but this 'save in > the new order' seems impossible. > Michael, I for one am puzzled as to what you sorted. Was this a point, polygon, line attribute table? Or some .csv layer? Or...? And how did you sort it? Opened it in the attribute table editor and clicked on columns? If so, as far as I know, sorting in this editor is just for convenience in reviewing data. The physical data order is determined by the link to the spatial features. Maybe you could also tell us what you're trying to achieve by sorting? -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
Thanks, can you suggest how as the refactor fields help is not clear, to say the least, about saving after sorting. I sorted the data (5 million rows) by a couple of the columns, just by clicking on them, then wanted to save in the new order but this 'save in the new order' seems impossible. -Original Message- From: Frederik-Matti Bartels (CAPSA) Sent: Tuesday, March 12, 2024 11:17 AM To: qgis-user@lists.osgeo.org; Michael.Dodd Subject: Re: [Qgis-user] editing and saving very large table External email: if the sender or content looks suspicious, please click the Report Message icon, or forward it to report-phishing Hello Michael, How did you sort the columns? If you have ordered them in the QGIS attribute table, they will not change their order when you export them. For this, you must use the "Refactor Fields" tool. Cheers, Matti -Ursprüngliche Nachricht- Von: QGIS-User Im Auftrag von qgis-user-requ...@lists.osgeo.org Gesendet: Montag, 11. März 2024 16:01 An: qgis-user@lists.osgeo.org Betreff: QGIS-User Digest, Vol 217, Issue 31 Send QGIS-User mailing list submissions to qgis-user@lists.osgeo.org To subscribe or unsubscribe via the World Wide Web, visit https://lists.osgeo.org/mailman/listinfo/qgis-user or, via email, send a message with subject or body 'help' to qgis-user-requ...@lists.osgeo.org You can reach the person managing the list at qgis-user-ow...@lists.osgeo.org When replying, please edit your Subject line so it is more specific than "Re: Contents of QGIS-User digest..." ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
Hello Michael, How did you sort the columns? If you have ordered them in the QGIS attribute table, they will not change their order when you export them. For this, you must use the "Refactor Fields" tool. Cheers, Matti -Ursprüngliche Nachricht- Von: QGIS-User Im Auftrag von qgis-user-requ...@lists.osgeo.org Gesendet: Montag, 11. März 2024 16:01 An: qgis-user@lists.osgeo.org Betreff: QGIS-User Digest, Vol 217, Issue 31 Send QGIS-User mailing list submissions to qgis-user@lists.osgeo.org To subscribe or unsubscribe via the World Wide Web, visit https://lists.osgeo.org/mailman/listinfo/qgis-user or, via email, send a message with subject or body 'help' to qgis-user-requ...@lists.osgeo.org You can reach the person managing the list at qgis-user-ow...@lists.osgeo.org When replying, please edit your Subject line so it is more specific than "Re: Contents of QGIS-User digest..." ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] editing and saving very large table
Hi Michael! I would try it with ogr2ogr which is shipped in all qgis installations. Best regards Abel Gonzalez Den mån 11 mars 2024 kl 12:27 skrev Michael.Dodd via QGIS-User < qgis-user@lists.osgeo.org>: > I have a very large (5 million rows) table in qgis and have sorted some of > the columns etc and want to save the sorted version but whatever format I > try (.csv, geopackage or other formats) it just saves the original order > not the sorted order. It also will not allow me to click the button to > ‘save edits’ whether or not the table is editable. Trying to copy large > chunks of the data out by highlighting it in sorted order is also very > flaky – occasionally it works but most often does not. > > > > I know a database would be more suitable for doing this but I want to know > if this is possible in qgis itself or is simply impossible. > > > ___ > QGIS-User mailing list > QGIS-User@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] editing and saving very large table
I have a very large (5 million rows) table in qgis and have sorted some of the columns etc and want to save the sorted version but whatever format I try (.csv, geopackage or other formats) it just saves the original order not the sorted order. It also will not allow me to click the button to ‘save edits’ whether or not the table is editable. Trying to copy large chunks of the data out by highlighting it in sorted order is also very flaky – occasionally it works but most often does not. I know a database would be more suitable for doing this but I want to know if this is possible in qgis itself or is simply impossible. ___ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
