Manifold Community Site: SQL help please

RE: SQL help please

Rakau — Tue, 13 May 2025 18:24:39 GMT

I have a csv file with data that I want to join to a mesh block shp

The csv file has been formatted as txt for the attribute MB_2013 showing the leading 0's, however it seems these leading 0's are stripped when importing into Manifold 9 .

I have tried to add these back in using sql.

Here is my attempt:

UPDATE [2013-mb-Family]

SET [MB2013_a] = VALUE RIGHT('0000000' & [MB2013_a], 7);

This returns 'Cannot parse query'

Is there a better way to get the leading 0's to show

Thanks in advance

ps, the image shows MB_2013, while the code shows MB2013_a

This is because I created a new attribute in my experimenting

Attachments:
Screenshot 2025-05-14 055704.png

RE: SQL help please

Dimitri — Tue, 13 May 2025 19:18:41 GMT

How about just importing the CSV with all fields imported as text? Use Create New Data source as described in the CSV topic and check the "Read all fields as text" option.

Don't forget to add an index to the resulting table.

You can then use the copy transform to copy text fields you want as numbers into numeric fields that you create.

RE: SQL help please

apo — Wed, 14 May 2025 19:09:27 GMT

As long as M9 identified the column to contain integer values it will stick to it.

You have two solutions to overcome this :

in your csv add a letter before the list of 0 in the first value of the column. You import the file and it will identify the column to be nvarchar. Once imported you just remove the letter in the first value.
you import the file as you did, add a new nvarchar column and update it using your SQL slightly modified to convert the INT to NVARCHAR using CAST

UPDATE [2013-mb-Family]

SET [new_MB2013] = StringPadStart(CAST( [MB2013] AS NVARCHAR ),7,'0') ;

If my understanding of the problem is correct

RE: SQL help please

Rakau — Wed, 14 May 2025 20:05:57 GMT

Thank you apo & Dimitri

Both helpful and aided in my learning

RE: SQL help please

Dimitri — Thu, 15 May 2025 04:58:47 GMT

As long as M9 identified the column to contain integer values it will stick to it.

No, not if you check the "Read all fields as text" option.

RE: SQL help please

apo — Thu, 15 May 2025 05:14:32 GMT

sorry for my inaccuracy, my comment referred firstly to a direct import, not by means of a connection to the source data. Secondly, once you imported the data, the M9 SQL requires data type conversions to be explicit, and this is where I used the term stick to type.

In the case you need M9 to import part of the columns as int or float and part of text, the first trick is the more effective one, but everyone can choose its way.

The best would be a complete connector allowing to set all the columns types...

RE: SQL help please

Dimitri — Thu, 15 May 2025 09:26:53 GMT

Or, CSV format could be used correctly by the author of the data. The CSV convention is that fields which are intended to be imported as text are delimited by double quote " characters. If double quotes are used (or any other character used as a text qualifier when using create new data source) the column will be imported as nvarchar text.

You can also do the first trick by enclosing the first value of the desired column with double quote " characters. The automatic type deducer will take that to indicate the column should be type nvarchar. That works both with the current 181.0 build as well as the upcoming 181.1 build which changes the CSV dataport to be RFC 4180 compliant.

By the way, using File - Create - New Data Source, as discussed in the CSV topic doesn't mean you have to leave the data in the original source file. The point of using New Data Source is to utilize the greater set of options available to you to connect to the CSV. It takes but a moment then to copy the table created and paste it into the local part of your project, just like an import.

The OP mentions "The csv file has been formatted as txt" - it would be interesting to see the original file (or a representative first few dozen lines of it) to see if that field was indeed "formatted as txt" by using some delimiting character. In that case, there's no need for any workarounds, just a need to use the right import method in Manifold.

RE: SQL help please

apo — Thu, 15 May 2025 09:48:00 GMT

I fully agree with all those statements, but regarding the first one :

GIS users are not always the author of the data

Data producers are not always (should I say rarely) aware of what correct is

Mixing both as a standard reality, you have better to know all the tricks you have to overcome the problem. This is what I find interesting in M9, having lot of flexibility.