topic Re: Model builder: iterative Excel-file import and Shapefile output in Geoprocessing Questions

Model builder: iterative Excel-file import and Shapefile output

EricWehner — Tue, 17 Sep 2013 10:52:18 GMT

Hi!

I'm trying to automate the following process within the model builder:

1. Import Excel xls-files, that each contain point elevation data with x,y and z values
2. Convert them into separate shape files while using the original xls file name as the output file name

Therefore I've setup the following model:
[ATTACH=CONFIG]27506[/ATTACH]

The problem occurs while trying to copy the features to an output shape when using the expression for the new file name. Since for iterating the Excel files I need to activate the Recursive option to scan the worksheets within each xls file. The extracted "File" then contains a "$" at the end to mark the worksheet (so if for instance the input file is 001b.xls, the extracted name within "File" is 001b$).
Because of the error message "000354 : The name contains invalid characters" i assumed that the "$" is the problem and used the Calculate Value tool with r"%File%".replace("$","") as an Expression and string as the Data Type. Unfortunately this doesn't help, although the calculated value seems to be correct (without the "$")...

Hence my questions are:
a) What exactly is causing the error and how can I solve it?
b) What other possibilities would I have to manipulate the input file name for output, for instance if I want to use only a part of the name or add an automated counter?

Thanks in advance for any help!

Re: Model builder: iterative Excel-file import and Shapefile output

ChristopherBlinn1 — Tue, 17 Sep 2013 12:48:10 GMT

Eric,

Are you sure the Calculate Value process is running prior to the Copy process? I understand you have a precondition but Model Builder can be tricky with preconditions at times. I ask because I don't see the shade underneath the process as if it has ran.

A quick way to make sure of that is to delete and re-add the processes, adding the Calculate Value first, then the Copy.

As for your question regarding an automated counter %i% and/or %n% can be used to add the iteration number to a name. Here's more information on these variables.

Hope this helps!

Best,
Chris B.

Edit:
Also it just occurred to me that you are naming a file starting with a number (at least in your example). If you're storing this in a geodatabase, there will be naming issues for the file if it starts with a number.

Re: Model builder: iterative Excel-file import and Shapefile output

EricWehner — Thu, 19 Sep 2013 09:17:36 GMT

Thanks for the hint (Calculate Value is now "shaded" too) - unfortunately it didn't have any effect. I think the order of the processes works according to plan, but for some reason the string I'm trying to create by "Calculate value" does not seem to have a proper format.
How would you create the output file name, if you wanted to base it on the input file's name?
Or is there another/better way to import 50 files (csv or xls) into point shapes?

Regards,
Eric

Re: Model builder: iterative Excel-file import and Shapefile output

AkosKisreti1 — Thu, 19 Sep 2013 09:50:31 GMT

Hi,

First I would try to import the xls files into geodatabase tables. I experienced problems regarding xls tables.

Akos