[gdal-dev] OGR SQL on CSV file with field name with parentheses?

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

[gdal-dev] OGR SQL on CSV file with field name with parentheses?

Moskovitz, Bob@DOC

Hello gdal_dev,

 

I am trying to use ogrinfo on a set of CSV file that has a field with the name “Vs30(m/s)”, but change the name to “Vs30”.  Is this possible or do I have to edit a bunch of files headers to do this? 

 

I tried:

ogrinfo -al "Data_directory" -dialect sqlite -sql "SELECT Vs30(m/s) as Vs30 FROM file1"

but as expected ogrinfo gave the error message “no such function: Vs30”.

 

Thanks in advance!


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR SQL on CSV file with field name with parentheses?

John Baumgartner
Try surrounding the field name with square brackets:

ogrinfo -al "Data_directory" -dialect sqlite -sql "SELECT [Vs30(m/s)] as Vs30 FROM file1"  

On Fri, 19 Apr 2019 at 09:32, Moskovitz, Bob@DOC <[hidden email]> wrote:

Hello gdal_dev,

 

I am trying to use ogrinfo on a set of CSV file that has a field with the name “Vs30(m/s)”, but change the name to “Vs30”.  Is this possible or do I have to edit a bunch of files headers to do this? 

 

I tried:

ogrinfo -al "Data_directory" -dialect sqlite -sql "SELECT Vs30(m/s) as Vs30 FROM file1"

but as expected ogrinfo gave the error message “no such function: Vs30”.

 

Thanks in advance!

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR SQL on CSV file with field name with parentheses?

Even Rouault-2
On vendredi 19 avril 2019 14:52:13 CEST John Baumgartner wrote:
> Try surrounding the field name with square brackets:
>
> ogrinfo -al "Data_directory" -dialect sqlite -sql "SELECT [Vs30(m/s)] as
> Vs30 FROM file1"

Brackets won't work with OGR SQL or SQLite. You must use double quotes

On a Bash shell, if you use double quotes for the SQL statement itself,
you need to escape them

ogrinfo test.csv -sql "select \"VS30(m/s)\" as VS30ms from test"

On Windows cmd.exe, you might also apparently try the above in most cases,
but there are some subtleties and dangers in some cases (not for the
above one), as underlined by
https://blogs.msdn.microsoft.com/twistylittlepassagesallalike/2011/04/23/everyone-quotes-command-line-arguments-the-wrong-way/
(paragraph "cmd.exe" and following)

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR SQL on CSV file with field name with parentheses?

Moskovitz, Bob@DOC
In reply to this post by John Baumgartner

Thanks John, it worked perfectly!

 

From: John Baumgartner <[hidden email]>
Sent: Thursday, April 18, 2019 9:52 PM
To: Moskovitz, Bob@DOC <[hidden email]>
Cc: [hidden email]
Subject: Re: [gdal-dev] OGR SQL on CSV file with field name with parentheses?

 

Try surrounding the field name with square brackets:

 

ogrinfo -al "Data_directory" -dialect sqlite -sql "SELECT [Vs30(m/s)] as Vs30 FROM file1"  

 

On Fri, 19 Apr 2019 at 09:32, Moskovitz, Bob@DOC <[hidden email]> wrote:

Hello gdal_dev,

 

I am trying to use ogrinfo on a set of CSV file that has a field with the name “Vs30(m/s)”, but change the name to “Vs30”.  Is this possible or do I have to edit a bunch of files headers to do this? 

 

I tried:

ogrinfo -al "Data_directory" -dialect sqlite -sql "SELECT Vs30(m/s) as Vs30 FROM file1"

but as expected ogrinfo gave the error message “no such function: Vs30”.

 

Thanks in advance!

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR SQL on CSV file with field name with parentheses?

Even Rouault-2
On vendredi 19 avril 2019 16:05:14 CEST Moskovitz, Bob@DOC wrote:
> Thanks John, it worked perfectly!

oh, indeed. Turns out SQLite is rather permissive in that area:
https://www.sqlite.org/lang_keywords.html
(that said, OGR SQL only accepts double quotes when identifier quoting is
needed)

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR SQL on CSV file with field name with parentheses?

Moskovitz, Bob@DOC
Good to know why this syntax works.  It brings back fond memories of using MS Access 😊

-----Original Message-----
From: Even Rouault <[hidden email]>
Sent: Friday, April 19, 2019 10:42 AM
To: [hidden email]
Cc: Moskovitz, Bob@DOC <[hidden email]>; John Baumgartner <[hidden email]>
Subject: Re: [gdal-dev] OGR SQL on CSV file with field name with parentheses?

On vendredi 19 avril 2019 16:05:14 CEST Moskovitz, Bob@DOC wrote:
> Thanks John, it worked perfectly!

oh, indeed. Turns out SQLite is rather permissive in that area:
https://gcc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Flang_keywords.html&amp;data=02%7C01%7CBob.Moskovitz%40conservation.ca.gov%7Cf0ba36de77384ea4297108d6c4ee6494%7C4c5988ae5a0040e8b065a017f9c99494%7C0%7C0%7C636912925503570904&amp;sdata=8dT7SGvGNq3lscnpcOkOhA4t2QNkh7UL9B7eiDW7Sxk%3D&amp;reserved=0
(that said, OGR SQL only accepts double quotes when identifier quoting is
needed)

--
Spatialys - Geospatial professional services
https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.spatialys.com&amp;data=02%7C01%7CBob.Moskovitz%40conservation.ca.gov%7Cf0ba36de77384ea4297108d6c4ee6494%7C4c5988ae5a0040e8b065a017f9c99494%7C0%7C0%7C636912925503570904&amp;sdata=2PT287%2BeOJIl9i73dhes%2BVuh4AD%2BPnn1MswgWO3eHk0%3D&amp;reserved=0
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev