I have an excel file (.xls) containing the following columns:
objectid, adresses, x, y
where x= x coordinate and y= y coordinate.
I would to create a new table in Oracle with the following columns:
objectid, adresses, geom
where geom is a SDO_GEOMETRY type. The geometry objects are created from the values in the x and y columns from the excel file.
How is it possible to do that with FME?
Thank you,
Fabienne
create a writer for Oracle Spatial and define a feature type (or import an existing one).
To convert your x,y attributes into a geometry, pass them through a 2DPointReplacer before you send them into the writer. You do not have to do anything particular because of either Oracle or SDO_GEOMETRY.
David
To automatically create the attributes in your xls you can generate a workspace in dynamic mode and follow David's advice.
That will take care of creating your xls attributes in the oracle table.
Hope this helps
Thank you for your answer.
I should not generate a Workspace as my excel file as Reader and "Oracle Spatial Object" as Writer?
Morevoer, I don't understand how add x and y as input for the 2DpointReplacer. the arrow before "Input" is red and I cannot add x and y as input with arrows.
Could you list the steps I should do please?
Thank you,
Fabienne
here's a small screenshot:
Hope that gives you the general idea.
David
Thank you for your screenshot. I made this but I received an error. Here is the error indicated in the log:
...
...
...
FME API version of module 'LogCount_func' matches current internal version (3.7 20111219) MS Excel Reader: Now reading features from table DGARNE_TEST_EXCEL, 10 record(s) at a time Loaded module 'EvaluateExpression_func' from file 'C:\\apps\\FME\\plugins/EvaluateExpression_func.dll' FME API version of module 'EvaluateExpression_func' matches current internal version (3.7 20111219) 2DPointReplacer: Failed to evaluate TCL expression: C:\\apps\\DGARNE_TEST_EXCEL.xls 2DPointReplacer: TCL Error Message: invalid bareword "C" in expression "C:ppsDGARNE_TEST_EXCEL..."; should be "$C" or "{C}" or "C(...)" or ... Storing feature(s) to FME feature store file `mapping_log.ffs' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Feature Type: `2DPointReplacer_POINT' Attribute(encoded: utf-16le): `ADRESSE' has value `CHAUSSEE DE FLEURUS, 90' Attribute(64 bit real) : `OBJECTID' has value `15551' Attribute(64 bit real) : `X' has value `155757.361000001' Attribute(64 bit real) : `Y' has value `127976.307' Attribute(string) : `db_type' has value `db_none' Attribute(string) : `fme_feature_type' has value `DGARNE_TEST_EXCEL' Attribute(string) : `fme_type' has value `fme_no_geom' Geometry Type: Unknown (0) =========================================================================== ORACLE Writer: Translation aborted -- rerun specifying "ORACLE8I_1_START_TRANSACTION 0" MS Excel Reader: Closing `C:\\apps\\DGARNE_TEST_EXCEL.xls' . Read operation complete 2DPointReplacer: XValue function cannot convert 'C:\\apps\\DGARNE_TEST_EXCEL.xls' to floating point value Stored 1 feature(s) to FME feature store file `mapping_log.ffs' FME Session Duration: 0.5 seconds. (CPU: 0.1s user, 0.2s system) END - ProcessID: 2252, peak process memory usage: 37556 kB, current process memory usage: 35424 kB 2DPointReplacer: XValue function cannot convert 'C:\\apps\\DGARNE_TEST_EXCEL.xls' to floating point value Program Terminating Translation FAILED.
Thank you so much for your help!
Fabienne
this could be an issue where some machines are configured with different decimal separators. Example: some countries use the . (point) and some use the , (comma) so separate decimal separators. FME uses the decimal separator that your machine is configured with, but this can cause problems if the input data uses a different decimal separator.
Try to insert a StringReplacer before the 2DPointAdder, where you replace the point with a comma. Do this for both the X and the Y attributes.
David
Here is a example of data in the excel file:
OBJECTID ADRESSE X Y 15551 CHAUSSEE DE FLEURUS, 90 155757,361 127976,307 15552 ROUMONT 235049,529 85089,465 15553 RUE WARICHET, 34 191559,329 156049,183 15554 RUE DE PRIESMONT, 120 162421,645 137478,979 15555 RUE DE HALTINNE 200758,976 127202,114 15556 RUE DU CHÂTEAU DE RUYFF, 68 262135,605 152673,922 15557 RUE DE FRANCE 139803,669 94742,825 15558 RUE DU BAS COMOGNE, 40 170236,236 128257,699 15559 RUE D'OSTIN 183910,711 137647,466 15560 RUE SAINT-VITU, 39 220052,67 131026,569 15561 CHEMIN DU CARNOIS, 2 87460,981 157653,581 15335 RUE HAUTE, 27 136596,358 140507,744 15336 RUE DU TOUQUET, 147 - LE BIZET 45681,29 156296,702
In FME, I added a StringReplacer between the Reader and 2DpointReplacer. In the StringReplacer, the parameters are:
Attributes: X,Y
Text to Find: ,
Replacement Text: .
User regular Expressions: no
Case Sensitive: no
And I had the following error:
FME API version of module 'EvaluateExpression_func' matches current internal version (3.7 20111219) 2DPointReplacer: Failed to evaluate TCL expression: C:\\apps\\DGARNE_TEST_EXCEL.xls 2DPointReplacer: TCL Error Message: invalid bareword "C" in expression "C:ppsDGARNE_TEST_EXCEL..."; should be "$C" or "{C}" or "C(...)" or ... Storing feature(s) to FME feature store file `mapping_log.ffs' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Feature Type: `2DPointReplacer_POINT' Attribute(encoded: utf-16le): `ADRESSE' has value `CHAUSSEE DE FLEURUS, 90' Attribute(64 bit real) : `OBJECTID' has value `15551' Attribute(encoded: utf-8) : `X' has value `155757.361000001' Attribute(encoded: utf-8) : `Y' has value `127976.307' Attribute(string) : `db_type' has value `db_none' Attribute(string) : `fme_feature_type' has value `DGARNE_TEST_EXCEL' Attribute(string) : `fme_type' has value `fme_no_geom' Geometry Type: Unknown (0) =========================================================================== ORACLE Writer: Translation aborted -- rerun specifying "ORACLE8I_1_START_TRANSACTION 0" MS Excel Reader: Closing `C:\\apps\\DGARNE_TEST_EXCEL.xls' . Read operation complete 2DPointReplacer: XValue function cannot convert 'C:\\apps\\DGARNE_TEST_EXCEL.xls' to floating point value Stored 1 feature(s) to FME feature store file `mapping_log.ffs' FME Session Duration: 0.6 seconds. (CPU: 0.1s user, 0.2s system) END - ProcessID: 4164, peak process memory usage: 36988 kB, current process memory usage: 34864 kB 2DPointReplacer: XValue function cannot convert 'C:\\apps\\DGARNE_TEST_EXCEL.xls' to floating point value Program Terminating Translation FAILED.
I also tried with Attributes: X,Y
Text to Find: .
Replacement Text: ,
User regular Expressions: no
Case Sensitive: no
but I received the same error.
Thank you so much for your help,
Fabienne
I copy-pasted your data into an Excel file and the following works for me here:
Bonne chance!
David
It works!
Thank you so much!
Best Regards,
Fabienne
http://evangelism.safe.com/fmeevangelist115/