E1_EXCEL_GL_JOURNAL map fails to connect to source Workbook
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.
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.
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..
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.
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 ?
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.
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.