DMT Error - External Table is not in the expected format

Kinetic ERP
1

Hi

Frustrating to say the least…

I have an Excel file that is used to pull data from a database and convert into a DMT journal format for loading.

Using Excel VBA SaveAs the file is saved a XLSX file but DMT complains that the “External Table is not in the expected format”. Weirdly if I do nothing more than open up that saved file and resave (no movement, no editing, no changes), the file will now load via DMT.

I’m trying to put this on automatic daily schedule but this sort of stuff makes it impossible.

Epicor DMT 11.2.300.7 (and 11.2.300.8 - 2023.1.8 Pilot)
Excel for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20100) 64-bit

I’m not sure if this is a DMT or Excel issue. More likely to be Excel I think

Previous topics (DMT Error - External Table is not in the expected format) are old and DMT/Epicor and Excel have moved on a bit.

Ideas.

2

May I see your saveas code line?

3

Hi
The save subroutine looks like this

Sub SaveJournalWorksheet()
    Dim saveDate As String
    Dim savePath As String
    saveDate = Format(Application.Range("reportDate"), "YYYYMMDD")
    shtJnl.Activate
    shtJnl.copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\Daily Journals\Daily_Sales_Journal_Combined_" & saveDate, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    shtGroup.Activate
    shtGroup.copy
    ActiveWorkbook.SaveAs Filename:="C:\Temp\Daily Journals\Daily_Sales_Journal_Group_" & saveDate, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    shtLookup.Activate
    ThisWorkbook.Save
End Sub

have also tried

 FileFormat:=xlDefaultWorkbook

with no difference.

Cheers

4

I was going to suggest xlOpenXMLWorkbook but that’s what you got.

xlDefaultWorkbook is whatever your default is.

Try a plain old .xls file → xlWorkbookNormal

5

Try adding this before the saves Application.CalculateUntilAsyncQueriesDone (hail mary)

6

I also found some stuff about copying the workbook first. Maybe look into that .

7

Cheers Kevin

Application.CalculateUntilAsyncQueriesDone

makes no difference. As it is I tend to refresh queries synchronously as I have other processes that need the data before they can do their thing (and they tend to crash otherwise)

ThisWorkbook.Connections("Query1").OLEDBConnection.BackgroundQuery = False ThisWorkbook.Connections("Query").OLEDBConnection.Refresh ThisWorkbook.Connections("Quert2").OLEDBConnection.BackgroundQuery = False ThisWorkbook.Connections("Query2").OLEDBConnection.Refresh

Saving a XLS (xlWorkbookNormal) works but I still prefer XLSX (they are smaller files) and a bit easier to fix if things go wrong.

I have had this working before to an XLSX file.

Will look into copying workbook first.

Thanks for your help.

8

You see that? Quert2 vs Query2

9

Poor renaming on my part.

10

Yes, but they aren’t the same between commands. you set one name and refreshed another

11

Technically only csv is supported, even though xls/xlsx sometimes works. Have you tried outputting it as csv instead?

12

@aosemwengie1 may have a point. I have a terrible time with some XLSX files and DMT because (depending on what you do/how you create it) Excel will ‘hold’ columns and rows in the sheet that DMT can see, but which don’t look like they are part of the dataset on the worksheet.

Converting to CSV will quickly show blank columns and rows as a series of “,” and may lead you to your answer.

13

I usually work with XLS files. It keeps me from struggling against the leading zeros that CSV files usually do. XLSX has never worked consistently for me.

14

And XLSX used to work quite regularly.

Given DMT offer XLSX as an acceptable file format it should just work.

I can use XLSX files in other environments with no issues and yet Epicor and DMT seem to be unable to process consistently.

15

I’ve been told by support multiple times that csv is the only supported format for DMT.

16

Then they need to mark it as experimental or remove it from the program.

If it is programmed to accept excel files, they need to support it.

17

Yea, I get that all the time with XLSX. I save it as XLS and it works fine. You can even have the file open with xls!!

18

DMT has been using csv, xls and xlsx for a long time. xlsx is a moving target that they do not control and if you just make a simple sheet it will normally work. But as @MikeGross pointed out Excel will make fields appear empty, but in the background they still are in the dataset. I know if I delete a lot of rows and then do a copy to the bottom Excel will go past the data and copy to the end of the sheet, so that is out of Epicor’s control.

From a support perspective csv is the cleanest since it removes all of the wrapping and if you are having issues then that would be a place to start.

19

These are nothing fancy sheets. No formulas, no formatting, single sheet. I’ve even reset the used range to just the area I want to load.

DMT headers in row 1 and data in the rest.

I would have thought the XLSX would have been easier to handle (unless Epicor is using some weird third party library that itself is buggy) than XLS as XLSX it is an open format. I’ve explored the underlying XML files (worksheet1.xml) and there is nothing there that looks like phantom columns or rows.

Have opened a case with Epico but more to re-raise the issue. The is an old knowledge base article from Aug-19 suggesting just to use CSV.

If they aren’t going to properly support it, then remove it (or at least flag as developmental)

20

@Edge Sorry to bring you in on this, but do you have any advice on this?