Skip to main content
Solved

How to import mutiple xls files with different schemas and output those same files to xls with new xy coordinates?

L
Forum|alt.badge.img

Hi, I am working in FME 2014. I am trying to create a workspace that will have a reader grab all xls files from one folder (with multiple schemas), re-project the xy coordinates to two new attributes (x, y), and export the data to multiple xls files based on file name and new projection. Before the workspace is run, the user must manipulate the xls files to have columns with original coordinate system and original x and y coordinates. The headings "Original_Coordinate_System" and "SourceX" and "SourceY" are the only common column names between all the excel files.

When I run the workspace I get an error saying

"Error fetching dynamic schema definitions from reader `XLSXR_1"

Attached are two xls worksheets as an example, and two screenshots to show a portion of the script.

Would anyone have any suggestions that I could try? Thanks in advance.

Best answer by stalknecht

Set the Attribute Definition of the writer to automatic.

View original
Did this help you find an answer to your question?

22 replies

stalknecht
Contributor
Forum|alt.badge.img+19
  • stalknechtContributor
  • Contributor
  • July 24, 2017

When i take a look at reader.png I don't see a generic reader.

Try to set Merge Feature Type at the reader.

Can you share your workspace?

L
Forum|alt.badge.img
  • liz
  • Author
  • July 25, 2017

Thanks for the suggestion @stalknecht, I did try that in one of the earlier versions (I have many), and I still kept getting the reader error.

stalknecht
Contributor
Forum|alt.badge.img+19
  • stalknechtContributor
  • Contributor
  • July 25, 2017
liz wrote:

Thanks for the suggestion @stalknecht, I did try that in one of the earlier versions (I have many), and I still kept getting the reader error.

Please share your workspace.

 

 

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 25, 2017

Hi @liz,

Can you read just one file without fanning out?

Cheers.

Lyes

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • July 25, 2017

@liz

Hi,

Maybe it is due to the column names in your sheets.

Your sample sheet nr.2 9601 Has invalid character in the first 4 columns.

Might this cause the problem when using it a dynamic writer in FME 2014? (can't test it as I no longer have 2014 installed)

It does not cause problem using 2016, fme just removes the invalid characters.

In fme I use the dynamic writer schema definition with schema source

"96* [XLSXR]" or

"Schema From Schema Feature"

both work correctly
L
Forum|alt.badge.img
  • liz
  • Author
  • July 25, 2017
mygis wrote:

Hi @liz,

Can you read just one file without fanning out?

Cheers.

Lyes

Hi Lyes, yes the script will read one excel file. The issues seems to occur when I'm reading 2 xls files with different schemas. Thanks for the reply.

 

 

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 25, 2017
mygis wrote:

Hi @liz,

Can you read just one file without fanning out?

Cheers.

Lyes

In this case Liz please share the workspace thanks. (If you can)

 

 

L
Forum|alt.badge.img
  • liz
  • Author
  • July 25, 2017

Thanks everyone for the responses! I've attached my workspace to this post.... I'm fairly new to FME so I'm guessing I'm making a beginner mistake.. However any further suggestions would be amazing! I've also attached the two excel files this workspace is running with. Thanks again!

@gisinnovationsb, @gio, @stalknecht

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • July 25, 2017

Hi @liz, have you tried adding the Excel files using advanced browser to select multiple files or folders? I've been able to read in both of your attached spreadsheets in FME 2014 using this configuration for the Reader.

L
Forum|alt.badge.img
  • liz
  • Author
  • July 25, 2017
fmelizard wrote:

Hi @liz, have you tried adding the Excel files using advanced browser to select multiple files or folders? I've been able to read in both of your attached spreadsheets in FME 2014 using this configuration for the Reader.

Hi Tia, thanks for the response. I finally got the outputs working thanks to the screenshots above, but my output excel files are now blank, with no attributes. Any suggestions here? Thanks again for your time! @TiaAtSafe

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 26, 2017
liz wrote:

Hi Tia, thanks for the response. I finally got the outputs working thanks to the screenshots above, but my output excel files are now blank, with no attributes. Any suggestions here? Thanks again for your time! @TiaAtSafe

HI @liz , do you mean no fields or empty fields?

 

 

L
Forum|alt.badge.img
  • liz
  • Author
  • July 26, 2017

The output excel files are completely blank; they don't even contain column headers @gisinnovationsb. It's like opeining up a new worksheet.

stalknecht
Contributor
Forum|alt.badge.img+19
  • stalknechtContributor
  • Contributor
  • July 26, 2017
liz wrote:

The output excel files are completely blank; they don't even contain column headers @gisinnovationsb. It's like opeining up a new worksheet.

Please provide your current workspace.

 

 

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 26, 2017
liz wrote:

The output excel files are completely blank; they don't even contain column headers @gisinnovationsb. It's like opeining up a new worksheet.

hi @liz, as mentioned by @stalknecht please provide the workspace. Thanks.
L
Forum|alt.badge.img
  • liz
  • Author
  • July 26, 2017

fmehelp.fmw

Here you go... thanks again for looking taking the time to look at this! @gisinnovationsb, @stalknecht

L
Forum|alt.badge.img
  • liz
  • Author
  • July 26, 2017

ihstest3.xlsxihstest2.xlsx

And here are the current excel sheets I'm working with. @stalknecht, @gisinnovationsb

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 26, 2017

Hi @liz ,

1. I would set the drop table/sheet to yes in the writers parameters to start with, this is if you are creating new excel files all the time

2. Also the dynamic schema definition might have to be set on in the writers property

Try the first one first.

stalknecht
Contributor
Forum|alt.badge.img+19
  • stalknechtContributor
  • Contributor
  • Best Answer
  • July 26, 2017

Set the Attribute Definition of the writer to automatic.

L
Forum|alt.badge.img
  • liz
  • Author
  • July 26, 2017

Thank you for the suggestions! My output excel file now has the original attributes, but not the new x, y attributes, but this is progress!

WRT custom transformers: is there one transformer you would replace with a custom ones? (I'm assuming my Esri Re-project transformers?) Or would you create a number of custom transformers? @gisinnovationsb

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 26, 2017
liz wrote:

Thank you for the suggestions! My output excel file now has the original attributes, but not the new x, y attributes, but this is progress!

WRT custom transformers: is there one transformer you would replace with a custom ones? (I'm assuming my Esri Re-project transformers?) Or would you create a number of custom transformers? @gisinnovationsb

Sorry I deleted that part on the original reply not to get carried away ... you just have to select a few transformers, then go to transformers menu then "create custom transformer". A new transformer containing the selected transformers will appear and it can be edited by clicking on one of the tabs above the canvas (the canvas being the place where the art putting readers/ writers and transformers is performed). If you are not happy with the result just undo by pressing ctrl+z in your keyboard.

 

In short it is to make space in the main window.

 

 

mygis
Supporter
Forum|alt.badge.img+13
  • mygisSupporter
  • Supporter
  • July 26, 2017
liz wrote:

Thank you for the suggestions! My output excel file now has the original attributes, but not the new x, y attributes, but this is progress!

WRT custom transformers: is there one transformer you would replace with a custom ones? (I'm assuming my Esri Re-project transformers?) Or would you create a number of custom transformers? @gisinnovationsb

try to create new x,y attributes with the attributeCreator.

 

 

L
Forum|alt.badge.img
  • liz
  • Author
  • July 26, 2017

Just wanted to say thanks so much for the help, thanks to all the suggestion from you two I've finally got the script working! Cheers!!

@gisinnovationsb, @stalknecht

Reply


Helpful Members This Week

Community Stats

31,105
Posts
117,929
Replies
38,858
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.