span8
span4
span8
span4
This article applies toFME 2013 SP1 (build 13450) and earlier only.In FME 2013 SP2,the Excel ADO based reader & writer (XLS_ADO) was deprecated and replaced by the a new Excel reader & writer based on libXL libraries (XLSX and XLSX2).For details on the current Excel reader/writer visit theGetting Started with ExcelTutorial
Excel is a popular spreadsheet application by Microsoft.It is read by FME as non-geometric (or non-spatial) data.When you select an Excel file (.xls) for input,you should go into the settings and select the desired worksheet (table).
The Microsoft Excel Reader/Writer works through ADO.
ADO is used to directly read and write Microsoft based technology usingActiveX Data Objects (ADO)interfaces,rather than going through ODBC.The result is improved functionality and performance (in some cases,the improvement is dramatic).
For Excel,ADO specifically permits:
The default ADO settings for Excel have a high probability of misidentifying column types and silently dropping data.For example,if the first 8 data rows of a column contain numbers,any non-numeric data that follows will not be read.Modern versions of FME try to lessen the likelihood of this occurring,and there are further steps users can take to improve the situation.
The general algorithm used by ADO to determine the type of an Excel column is as follows:
The ADO provider has a limitation which allows a maximum of 255 columns to be read/written.
There are multiple engines available for reading Excel files with ADO.‘Jet' only considers the data in a column when performing type scanning.‘ACE' (Access Connectivity Engine) considers both the data and the data formatting when performing type scanning.In practice,this means ACE is more likely to choose the 亚搏在线safer type of ‘text' for columns.
FME 2009 and earlier only support the Jet engine.FME 2010 supports both Jet and ACE,and defaults to ACE.In order to resolve concurrent reading/writing issues,FME 2011 changed the default engine back to Jet but retained support for ACE if Jet is not present or the file extension is .xlsx or .xlsb.All of these versions of FME are vulnerable to the "first 8 rows" scenario described above,but because ACE is more likely to choose text for column types,there are files that will be correctly read in FME 2010 but not FME 2011.
As of FME 2012 beta 12055,FME temporarily modifies the registry settings that affect the Jet engine such that TypeGuessRows=0 and ImportMixedTypes=Text.This will ensure mixed-type columns are identified as text and no data is lost on read.ACE continues to work as before,but warnings are produced if incorrect registry settings are detected.
Q)FME seems to read more Excel features than I have records in the spreadsheet - why?
A)The library we use to read Excel files will return a record for any rows that have null values - the problem is that on opening a spreadsheet in Excel it is not easily apparent whether a cell is null or non-existent.
The 亚搏在线safest way to avoid this problem is when you are using Excel make sure any data deletions are carried out by right-clicking the row number and choosing 'delete';i.e.delete the row.If you simply select the cells within the row and press the delete key you risk deleting the cell contents but leaving the row intact - it is these rows which will be read by FME as blank records.
In order to remove these extra features aTestercan be used.
Excel Provider error (-2147467259) Spreadsheet is full (FME 2013 and earlier)
Overview of Excel Reader Parameters
MS Excel Error: Failed to open writer (FME 2013 SP1 and earlier)
Overview of Excel Writer Parameters
Using the Qlik data eXchange (QVX) Writer
Reading or Writing to Microsoft Access Database (accdb) when MS Office is not installed
Build Road Lines from GPS Points (CSV to Shape)
© 2019 亚搏在线Safe Software Inc |Legal