Loading an excel spreadsheet

[Massimo]

Hello to all the group.

by using Spatialite_gui.exe vers. 1.5.0 I've noted that problem of
having NULL values transformed to zero values seems to be solved if
loading directly an excel file, not a .csv!

But a new problem comes out: numeric field is transformed to a "not
specified type" field, exactly as I had experimented when I told you
that when running a "grouping" spatial view query by grouping on a
field and summarizing on another (numeric) field, the latter was
transformated to a not specified type in the output table.

Oviously, I may open the SpatiaLite layer anyway in QGIS, transform it
to a sahepfile (needed because otherwise I would have "ERROR" in any
cell fo the attribute table) and add a several new numeric fields (by
using the field calculator) where copying the values. Good trick but
it is time consuming.

Will Libspatialite v.3.0.2 (with these little problems solved) be
inserted (as stated by Sandro) into the next version of
Spatialite_gui.exe? is it expected by this month? As usual, thank you
very much!

Massimo

[Massimo]

If using method suggested by Micha
(http://www.surfaces.co.il/?p=1112), i.e. 1) creating a virtualXLS
table (not loading a XLS table), 2) creating a table with the correct
field type, 3) using the SQL construct “INSERT INTO … SELECT FROM…” in
order to copy all data from the linked table into your structured
table, and 4) run the spatial funtions AddGeometryColumn() and
MakePoint() in order to convert the table to a spatial layer,
finally I obtain a table with the correct field type and the correct
NULL values as in the input xls file :-)

But, after inserting the obtained Spatialite table into QGIS (where
the features are correctly showed), I need save it as a shapefile, as
usual, otherwise I see the "ERROR" value in each cell of the attribute
table. And the problem is that the obtained shapefile while correctly
keeps the field type, have transformed the NULL values to ZERO!

I've read the second recommandation of the article:
"the table is “cleanly” formated – only data in rows, no empty rows,
etc."
Although, I would like to find a way to keep the input empty rows in
the output shapefile too!

Thank you in advance!

Massimo

[a.fu...@lqt.it]

> And the problem is that the obtained shapefile while
> correctly keeps the field type, have transformed the
> NULL values to ZERO!
>

Massimo, the Shapefile format doesn't support "NULL" values;
at least, not in a standard universal mode clearly defined.
this usually means that NULL-TEXT will be represented as SPACE,
and NULL-NUMBER will be represented as ZERO.

you can easily check this simply using Open Office Calc;
just create a simple spreadsheet, then save as DBF, and
finally reopen again the same DBF spreadheet: any NULL
in a numeric column will be shown as ZERO

bye Sandro

[Massimo]

Thank you Sandro for remembering it to me, I had forgotten it.

Then, the only chance I have to keep the NULL values when I show my
data in QGIS would be .... just opening the Spatialite layer into
QGIS, without saving it as a shapefile, which presently it seems
needed because of the "ERROR" values in the attribute table.
Unless I omitted to do something.

Massimo

[Massimo]

Thanks to Micha, the problem is fixed this way: in the new version of
spatialite_gui there's an option to export a table to a shapefile.
Columns preserve the NULL value when using this method (no automatic
conversion of NULL to zero)!