• Home
  • Forums
  • FME Form
  • Authoring
  • How to import mutiple xls files with different schemas and output those same files to xls with new xy coordinates?
Solved

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

L
Badge

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.

icon

Best answer by stalknecht 26 July 2017, 16:18

View original
2 Attachments

22 replies

stalknecht
Rank
Userlevel 1
Badge +18

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
Badge

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
Rank
Userlevel 1
Badge +18

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
Badge +8

Hi @liz,

Can you read just one file without fanning out?

Cheers.

Lyes

G
Badge +3

@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
Badge

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
Badge +8

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
Badge

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
Rank
Userlevel 4
Badge +13

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
Badge

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
Badge +8

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
Badge

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

stalknecht
Rank
Userlevel 1
Badge +18

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
Badge +8

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
Badge

fmehelp.fmw

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

L
Badge

ihstest3.xlsxihstest2.xlsx

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

mygis
Badge +8

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
Rank
Userlevel 1
Badge +18

Set the Attribute Definition of the writer to automatic.

L
Badge

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
Badge +8

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
Badge +8

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
Badge

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


Community Stats

28,711
Posts
109,488
Replies
36,560
Members
Terms & ConditionsCookie 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.