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/
