+1-888-319-3663

COMMUNITY FORUM

E1_EXCEL_GL_JOURNAL map fails to connect to source Workbook

@GP_Beat asked 5 years ago

We upgraded recently to SC 2016 and had more or less success with our existing GL Entry maps that were upgraded.. We used to be able to define Name Ranges for the data input of a SC map that uses Excel as source.. Somehow, this doesn’t work anymore as it always try to include all the possible available columns that are next to the name range, whereas we don’t want them to be included, but no matter how you try, it finds them and and wants them to be mapped, which of course they are not. .This worked perfectly before in release 2013 and now has stopped working.
Based on that, I downloaded the template maps from eOne and wanted to give a try to the very simplistic map E1_EXCEL_GL_JOURNAL workbook. After importing the source file definition in SC 2016 console, I followed all the instruction in the Excel file, but can’t seem to be able to connect to the source file.. SC map editor keeps throwing an error that the connection source file is an invalid format.. the file has macros, thus the extension is .xlsm and the instructions clearly state that you need to select that, but you can’t get the list of the tabs, and when trying to validate you get several errors :
ERROR [HY000] [Microsoft][ODBC Excel Driver] External table is not in the expected format.
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames
ERROR [01S00] [Microsoft][ODBC Excel Driver]Invalid connection string attribute FirstRowHasNames
This is just silly, as it looks like eOne is publishing template maps that haven’t even been tested with the latest release.. The latest build of SC 20.16.0.31 is rather disappointing, since it’s full of bugs.. Date formats are not recognized properly, the Excel Addin doesn’t support name ranges anymore correctly, and this is causing major headaches since the adjacent columns often carry validation formulas in Excel that SC considers as input data, which it is not!
Please eOne fix those bugs and bring us a fully working SC 2016 back.. otherwise we’re just going to stick with the old release.. sad but true.

Answers

0 Vote Up Vote Down

Jared Dux Staff answered 5 years ago

To use named ranges on an Excel sheet in SmartConnect you need to use an ODBC Connection for your data source and choose Excel XXXX when setting up your connection string. Choose your Excel file and then click OK on the ODBC Microsoft Excel Setup window to return to the Map Setup. Now you can’t write your query to select only from your named range as: select * from [your_named_range].
The template errors you are receiving is because SmartConnect cannot find the specified source file. You need to update the path to the Excel Workbook in SmartConnect to the location you saved it to from the download. The Excel File also cannot be open when trying to connect to it from SmartConnect.
If you need any assistance with this setup I would recommend sending in an email to our support team.

@GP_Beat replied 5 years ago

Thanks Jared,
I had selected the proper path where the Excel file template was located in the Map editor.. still it didn’t wanted to recognize the format.. I’m fully aware that you can’t have the file open while trying to edit the map.. this has to do with the exclusive access.. which I find sometimes really annoying because when you start with a new map, you don’t necessarily have all the data in mind and need to go back to check the source all the time.. Due to this constraint, you can only do this by taking a copy of the Excel data file to work with.
I’m going to try the template out with an ODBC connection and get back here..
What about the date format that is not recognized correctly in Excel ? is that bug somewhere on the near-to-release fix-list ? This is really annoying considering that most of the input data in Excel comes with date fields, and having to do an extra step to convert those fields in text string before running the map is kinda stupid..

Jared Dux Staff replied 5 years ago

When you select the ellipses button to choose the Excel Workbook do you not have the .xlsm file type available in the Files of type dropdown?
I am not sure exactly what you are seeing with the dates not being formatted correctly based on your previous post. I would advise you send an email into support for this so you can provide a screenshot and any necessary attachments.

@GP_Beat replied 5 years ago

Jared,
The connection thru an ODBC link is also rejected by SC… when you try to select the file in the link for the connection string build, it throws an error : “External table is not in the expected format”.. my guess is that this is because of the .xlsm file extension..
I tested the exact same template in my SC 20.14.0.18 and this works perfectly… What’s missing or wrong with SC 2016 and/or Excel 2013 ?

Jared Dux Staff replied 5 years ago

Can you try installing these drivers: https://www.microsoft.com/en-us/download/details.aspx?id=39358 This is what SmartConnect uses to make the connection to Excel files. The full version of Excel technically doesn’t need to even be on the machine.

@GP_Beat replied 5 years ago

Thank you Jared,
Tried that too, but no success.. the latest build of SC 2016 just seems to not want to cooperate with .xlsm type of Excel files.. (with Macros embedded).
Also I tried to create an ODBC connection string rather than opening directly the Excel file with SC map editor, and when trying to select the same macro template (.xlsm) file, SC throws an error message “External table is not in the expected format.”
There is no way that a macro-enabled template can be used with SC 20.16.0.31 so far.. if that worked with previous releases, it doesn’t anymore.

If you would like to submit an answer or comment, please sign in to the eOne portal.