Hi Wolfgang, Well, I can say it works for me using Workbench - is there any particular reason you are using a Mapping File?
At least now you know it can work. In this sort of situation what I ususally do is set it up in Workbench then save the translation as a mapping file. Then you can compare FME's version to what you have created to see what is different. Hope this helps Mark Mark Mark Ireland, Senior Product Specialist Safe Software Inc. Surrey, BC, CANADA [EMAIL PROTECTED] http://www.safe.com Solutions for Spatial Data Translation, Distribution and Access --- In [email protected], "avtmd" <[EMAIL PROTECTED]> wrote: > > Hi all, > > there is a coordinate list with / without a header line as an excel > sheet "Table1": > n x y z > 1 32768.421 5328653.421 755.120 > 2 32775.294 5328661.736 758.326 > .... > I tried to convert this to CSV by an FME mapping file as provided > below. > Reading without above header line drops the first line in output. > Reading with header line translation fails with the following message: > > **************************** start of log file part > Feature Manipulation Engine 2006 GB (O) (20060620 - Build 2651) > ..... > Using Dynamic Reader $Revision: 17.1 $ ( $Date: 2006/01/06 17:33:15 > $ ) with module XLS_ADO to read data from dataset `.\test.xls' > Using Dynamic Writer $Revision: 17.0 $ ( $Date: 2005/10/28 21:07:49 > $ ) with module CSV to write dataset `.' > Module `CSV' API version matches current core version (3.1 20031010). > MS Excel Reader: Query failed. Query Text `SELECT [F1], [F2], [F3], > [F4] FROM [Tabelle1$]'. Provider error `Für mindestens einen > erforderlichen Parameter wurde kein Wert angegeben.'. > **************************** end of log file part > > (Sorry, I'm using a German version of Excel; translation of above > text: "For at least one required parameter no value was provided.") > > Notes: > 1) The error message is independent from the definition of the > field names in the INP_DEF statement below (e. g., as text), which > corresponds to the documentation saying these specifications are > ignored. > 2) Changing the field names F1, .. F4 to n x y z in the mapping file > works fine for the header line case, but yields the same error > message as above for the case without header line. However, for > reasons mentioned below that solution is not possible in my case > anyway. > > > Mapping file as used, providing test.xls with table "Table1" with > above data with / without header line: > # ====================================== start of mapping file > LOG_STANDARDOUT yes > READER_TYPE XLS_ADO > READER_KEYWORD INP > WRITER_TYPE CSV > WRITER_KEYWORD OUT > > DEFAULT_MACRO InpFil ./test.xls > DEFAULT_MACRO OutFil ./test.csv > DEFAULT_MACRO TblNam Table1 > DEFAULT_MACRO TblSkp 0 > DEFAULT_MACRO CsvHdr no > > GUI TITLE Test: Export Excel to CSV > GUI FILENAME InpFil Excel-Files(*.xls)|*.xls|All_files(*.*)|*.* Input > Excel: > GUI FILENAME OutFil CSV-Files(*.csv)|*.csv|All_files(*.*)|*.* > Output CSV : > GUI TEXT TblNam Excel-Tablename: > GUI INTEGER TblSkp No of lines to skip: > GUI CHOICE CsvHdr yes%no Output CSV header lines? > > MACRO OutDir "[EMAIL PROTECTED](DIRNAME,$(OutFil))]" > MACRO OutNam "[EMAIL PROTECTED](ROOTNAME,$(OutFil))]" > > INP_DATASET "$(InpFil)" > INP_PROVIDER_TYPE XLS_ADO > INP_READ_CACHE_SIZE 10 > # The following are undocumented keywords seen in workbench... > INP_READER_META_ATTRIBUTES fme_feature_type > INP_START_FEATURE_NUM $(TblSkp) > > OUT_DATASET "$(OutDir)" > OUT_SEPARATOR (;) > OUT_FIELD_NAMES $(CsvHdr) > OUT_DUPLICATE_DELIMS no > OUT_QUOTE_OUTPUT no > > INP_DEF $(TblNam) \ > xls_where_clause "" \ > F1 integer \ > F2 double \ > F3 double \ > F4 double > > OUT_DEF "$(OutNam)" \ > F1 number(8,0) \ > F2 number(12,3) \ > F3 number(12,3) \ > F4 number(10,3) > > FACTORY_DEF * TeeFactory \ > INPUT FEATURE_TYPE * \ > OUTPUT FEATURE_TYPE "$(OutNam)" > > INP * > OUT * > # ====================================== end of mapping file > > Questions: > 1) The (undocumented?) attribute naming syntax F1, F2, ... seems to > work for data that look like numbers and for empty fields. It seems > to fail for text data in the above example. In opposite, providing > names different from F1, F2, ... seems to fail with numbers in the > first line of the Excel sheet. > > 2) Is there a possibility to assign names to the columns within a DEF- > line? The Manual suggests there is, but I receive above error message > even when using attribute the "default names" F1, ... I learned from > the Workbench. > > 3) In addition, is there a possibility not to interpret the first > line in an Excel sheet as field names? (Some error message I received > in between suggests there could be some HDR=NO, saying, "Connection > string `Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=...\test.xls;Extended Properties="Excel 8.0;IMEX=1;HDR=YES"'." > which does not seem to be user changeable, however). > > Background: My customer has header lines in his Excels containing all > sorts of special characters like newline, dot, minus sign, etc., > which even FME Workbench has troubles to deal with. Writing back to > Excel changes the first header line, and, worse, queries on some > attribute names do not seem to work correctly. Since the file has to > be written back as an Excel it is necessary to have the header lines > correct. So far we exported the tables as CSVs, which was a tedious > and error-prone process with many Excel files. > > > Thanks to all who can help, > > Wolfgang > For insights into what's up at Safe Software and what's on the development horizon, visit Safe's blog at spatial-etl.blogspot.com. Safe Software has also made slides available that outline enhancements planned for FME 2007. The slides are from the "Road Ahead" presentation given on Day 2 of the FME Worldwide Users Conference. To view these slides, visit www.safe.com/2006uc. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/fme/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/fme/join (Yahoo! ID required) <*> To change settings via email: mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
