Annoying "External table is not in the expected format" message (1 Viewer)

[Stm]

Dear all,

I have two Access Forms which each run few queries and then send out the results by email.
There are few linked tables to excel *.xlsx and some local tables, each query will join these tables to compute the results.

This program runs fine for long time until about one year ago, the program will sometime failed due to error "External table is not in the expected format".
If I resume the program, most of the time it will success without error.

At first I wonder if the problem is because of the content of the file, but I note that the error occur randomly, each time the error query may be different, but every time when I resume it, it will success.

I've tried to search for answer, but no luck still have no idea how to fix it.

If you have similiar experience, could share to me??

Thanks.

 

[CJ_London]

Is the file actually a.xlsx or a .csv or.txt file?

If not a .xlsx then opening either of the other two formats as a .xlsx can change the format.

personally I never import from excel formats and use .csv as it is more stable

as for testing, rather than just continuing, abort the process and inspect the table and file for issues

 

[Alodge]

Dear all,

I have two Access Forms which each run few queries and then send out the results by email.
There are few linked tables to excel *.xlsx and some local tables, each query will join these tables to compute the results.

This program runs fine for long time until about one year ago, the program will sometime failed due to error "External table is not in the expected format".
If I resume the program, most of the time it will success without error.

At first I wonder if the problem is because of the content of the file, but I note that the error occur randomly, each time the error query may be different, but every time when I resume it, it will success.

I've tried to search for answer, but no luck still have no idea how to fix it.

If you have similiar experience, could share to me??

Thanks.

Hi! Were you able to resolve this issue? My colleague and I both started experiencing the same issue around the same time as your post. It’s seems to align with a recent Windows update but I don’t know that, that is related. These databases have been running great for 5 years and now suddenly they all error when interacting with files (xlsx and text) on a network drive. When manually pushed, sometimes multiple times, eventually it will connect. But these are set to run on a schedule so the daily manual intervention has become quite annoying.

 

[TacoTetradactyl]

Signed up to chime in. I have had this issue and have a resolution that has worked for me.

Context: When exporting from MS access to excel I would get 'table is not in the expected format'. In my scenario I did not export to linked tables. I delete and re-write fresh spreadsheets each time. In my scenario I am using the DoCmd.TransferSpreadsheet method for exporting multiple tabs to these files. The error was periodic and inconsistent.

Suspicions: I suspected this was happening because our files system was updated so that all files were synced to cloud services. (OneDrive) This means that when my database is saving/writing/deleting files, odd conflicts are created with read/write.

Scenario 1: In the first scenario I had a loop of small files being created based off different facilities. For each facility, create filtered export, loop. These files are relatively small. To test my suspicion, I added a 15 second wait after deleting the existing file before creating the new one. This fixed my issue as it gave the cloud sync enough time to delete the file off the cloud before I tried to write to the file (to create it). The tab creation in these files was under a second so the exports went well after adding in an 'add time do while loop' so that the cloud sync would not be fussy. Janky solution, but problem solved.

Scenario 2: In the second scenario, my file to be exported was much larger. It did not fail as often, but enough to be annoying (and enough to where I could not hand off the task of 'push that button' to someone else). In this case I suspected the issue might also be that when the query was running between tab creation, the cloud sync saw the file as inactive and tried to sync it about the same time that my database wanted to write the next tab. To resolve this, I wrote code that staged all my queries to temp tables first, then ran the export. This prevented any downtime between writing to the file. This resolved my issue.

I guess that is all to say, my issues stemmed from OneDrive trying to sync my files before I was done writing to them. My options were to give it time between each action (which was necessary when deleting the file) and then get rid of any gaps in 'touch time' on each file so that OneDrive would not try to nab the file while I was still working in it.

I hope this helps other folks. - Good luck!

Also for added context: Why excel vs csv? In our use case the files feed BI. Having the all the data in one tabbed file simplifies re-connecting the file (one connection vs 10+). Also our super users want to take the files for their own use. In short, having a multi-tabbed excel file greatly reduces general operational chaos.

 

[The_Doc_Man]

I will offer the opinion that if the cloud is involved i.e. the external file is on the cloud, you might have a protocol mismatch error. Access uses the Windows File and Printer Sharing protocol called Server Message Block (SMB), which is a partial-file-transfer method, but cloud-based services perform only whole-file transfers via either FTP or HTTPS or sub-variants thereof.

I cannot say with any certainty that this protocol incompatibility IS the problem being described here, but the intermittency would be expected for a protocol interference problem. @TacoTetradactyl commented "my issues stemmed from OneDrive trying to sync my files before I was done writing to them". His post shows an example of overlapping whole-file vs partial-file updates. I take his report to likely be very accurate, certainly highly credible. And easily explainable.

 

[Stm]

Signed up to chime in. I have had this issue and have a resolution that has worked for me.

Context: When exporting from MS access to excel I would get 'table is not in the expected format'. In my scenario I did not export to linked tables. I delete and re-write fresh spreadsheets each time. In my scenario I am using the DoCmd.TransferSpreadsheet method for exporting multiple tabs to these files. The error was periodic and inconsistent.

Suspicions: I suspected this was happening because our files system was updated so that all files were synced to cloud services. (OneDrive) This means that when my database is saving/writing/deleting files, odd conflicts are created with read/write.

Scenario 1: In the first scenario I had a loop of small files being created based off different facilities. For each facility, create filtered export, loop. These files are relatively small. To test my suspicion, I added a 15 second wait after deleting the existing file before creating the new one. This fixed my issue as it gave the cloud sync enough time to delete the file off the cloud before I tried to write to the file (to create it). The tab creation in these files was under a second so the exports went well after adding in an 'add time do while loop' so that the cloud sync would not be fussy. Janky solution, but problem solved.

Scenario 2: In the second scenario, my file to be exported was much larger. It did not fail as often, but enough to be annoying (and enough to where I could not hand off the task of 'push that button' to someone else). In this case I suspected the issue might also be that when the query was running between tab creation, the cloud sync saw the file as inactive and tried to sync it about the same time that my database wanted to write the next tab. To resolve this, I wrote code that staged all my queries to temp tables first, then ran the export. This prevented any downtime between writing to the file. This resolved my issue.

I guess that is all to say, my issues stemmed from OneDrive trying to sync my files before I was done writing to them. My options were to give it time between each action (which was necessary when deleting the file) and then get rid of any gaps in 'touch time' on each file so that OneDrive would not try to nab the file while I was still working in it.

I hope this helps other folks. - Good luck!

Also for added context: Why excel vs csv? In our use case the files feed BI. Having the all the data in one tabbed file simplifies re-connecting the file (one connection vs 10+). Also our super users want to take the files for their own use. In short, having a multi-tabbed excel file greatly reduces general operational chaos.

Thanks @TacoTetradactyl for your findings....in my case however, all files are not on cloud, and I just read from them.
It's interesting when I use DoCmd.TransferSpreadsheet to export to excel, I never encounter "External table is not in the expected format" error!

 

[Stm]

I will offer the opinion that if the cloud is involved i.e. the external file is on the cloud, you might have a protocol mismatch error. Access uses the Windows File and Printer Sharing protocol called Server Message Block (SMB), which is a partial-file-transfer method, but cloud-based services perform only whole-file transfers via either FTP or HTTPS or sub-variants thereof.

I cannot say with any certainty that this protocol incompatibility IS the problem being described here, but the intermittency would be expected for a protocol interference problem. @TacoTetradactyl commented "my issues stemmed from OneDrive trying to sync my files before I was done writing to them". His post shows an example of overlapping whole-file vs partial-file updates. I take his report to likely be very accurate, certainly highly credible. And easily explainable.

Thanks but no Cloud involved in my situation.

 

[The_Doc_Man]

OK, in TacoTetradactyl's case, a cloud was involved, but for Stm a cloud was not indicated. The next most probable cause would be due to an errant patch. I wish I could say that Microsoft never publishes a bad patch, but I'd be lying through my teeth. The problem was first reported in April so it will be too old to be able to attempt a rollback.

@Stm's report is "There are few linked tables to excel *.xlsx and some local tables, each query will join these tables to compute the results." The error says "External table not in expected format" which is an Access error. Excel doesn't have that particular error - but Access trying to map a file as a table DOES have that error. So this leads back to the suggestion that if this problem is still occurring, it is imperative that you analyze the file created during this process when you have a failure. In the strictest sense, this is most likely to be a case of a file having been written so that one of the columns in the mapped Excel worksheet has something inconsistent with the Access table design that maps it. I.e. maybe some kind of data error that leads to a null in a column that cannot be empty/null. Or something like that.

I say it that way because as long as a file conforms to the row/column structure rules that separate one cell from another, an Excel file can never have an inherent "expected format" error about which it would complain. Each cell is an independent entity that contains its own formatting. There IS no overall format. (More precisely, what looks like uniform formatting is due to duplicating the formatting per cell for every cell that is supposed to have the same formatting.)

 

[Stm]

OK, in TacoTetradactyl's case, a cloud was involved, but for Stm a cloud was not indicated. The next most probable cause would be due to an errant patch. I wish I could say that Microsoft never publishes a bad patch, but I'd be lying through my teeth. The problem was first reported in April so it will be too old to be able to attempt a rollback.

@Stm's report is "There are few linked tables to excel *.xlsx and some local tables, each query will join these tables to compute the results." The error says "External table not in expected format" which is an Access error. Excel doesn't have that particular error - but Access trying to map a file as a table DOES have that error. So this leads back to the suggestion that if this problem is still occurring, it is imperative that you analyze the file created during this process when you have a failure. In the strictest sense, this is most likely to be a case of a file having been written so that one of the columns in the mapped Excel worksheet has something inconsistent with the Access table design that maps it. I.e. maybe some kind of data error that leads to a null in a column that cannot be empty/null. Or something like that.

I say it that way because as long as a file conforms to the row/column structure rules that separate one cell from another, an Excel file can never have an inherent "expected format" error about which it would complain. Each cell is an independent entity that contains its own formatting. There IS no overall format. (More precisely, what looks like uniform formatting is due to duplicating the formatting per cell for every cell that is supposed to have the same formatting.)

The problem is likely due to bad patch, as in past few months, I encounter significantly less such error than before. However, since Aug, this problem appears again more frequent. The saddest thing is that I don't have any control to apply/rollback any MS patch as it is a company policy.

 

[The_Doc_Man]

Even the U.S. Navy allowed us to roll back patches when they were having issues, and their IT folks were nearly paranoid about patching. However, it might take quite a long time to convince the IT folks at your shop.