Hi ​@Hasna Nizam,

There seems to be a problem in the syntax of your SQL query. Could you please share your SQL query also that you are using?

It does appear to be a connection string issue. There is no "perfect" connection string. They change from computer to computer. Use sites like "connectionstrings.com" to figure out what works for your computer. Also know that using Excel as a database may not support all standard SQL syntax.

It's best if you migrate your data to a real database. 

I have been using the same connection string for over 2 years. The connect command works with a newly downloaded excel file. But if i open the excel file or add two columns using set cell action, I will get error.

my connection string - Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$sProcessFilePath$;Extended Properties="Excel 12.0 Xml;HDR=YES";

Hi ​@Hasna Nizam ,

The error "External table is not in expected format" typically occurs when there is an issue with the format of the Excel file you are trying to access using the OLEDB connection.

If your Excel file contains mixed data types (e.g., text and numbers in the same column), consider adding IMEX=1 to your connection string.

Additionally, consider checking the below points if the above one didn’t help at all.

1. Check File Extension: Ensure that the file extension matches the actual format of the file. If your file is saved as .xlsx, make sure it is indeed in the Excel 2007 or later format. If it is actually in a different format (like .xls), you may need to update your connection string accordingly. 

2. Update Connection String: If you are working with an .xls file, update your connection string to use the appropriate provider:

   • For Excel 97-2003 files (.xls):

     Provider=Microsoft.OLEDB.4.0;Data Source=$sProcessFilePath$;Extended Properties="Excel 8.0;HDR=YES";
     

   • For Excel 2007 and later files (.xlsx):

     Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$sProcessFilePath$;Extended Properties="Excel 12.0 Xml;HDR=YES;";
     

3. File Integrity: Ensure that the Excel file is not corrupted. You can try opening the file in Excel and saving it again to ensure it's in the correct format.

4. Check for Hidden Sheets or Data: Sometimes, hidden sheets or unsupported data types can cause this error. Open the file in Excel and check for any unusual formatting or hidden sheets. 

5. Remove Unused Columns: If you added columns using the set cell action, make sure those columns do not contain incompatible data types or formatting that might confuse the OLEDB provider.

Thankyou Padmakumar. As I mentioned, the excel works after its been downloaded from an application. We download the .xlsx type.  It stops working if we open it or add additional columns- gets this error. 

I will check all possibilities with the solution u provided.

​@Hasna Nizam 

Could you please try copying the same data into a newly opened Excel file and give it a try?

 

Yes I tried.

I tried using Excel Open - Get multiple cells - Write from data table to new excel -

The new excel gives same error using Excel as DB connect action.

Now also getting error while trying to open Excel Advanced: Open Action

Hi ​@Hasna Nizam 

In addition to my previously given recommendations, could you please also try this after changing the DB action package version?

Yes I tried.

I tried using Excel Open - Get multiple cells - Write from data table to new excel -

The new excel gives same error using Excel as DB connect action.

 

Now also getting error while trying to open Excel Advanced: Open Action

 

Are you able to open the new Excel file manually after writing data from the data table?

yes, i tried downgrading the DB packages as well. But it didnt work -

NB: Im using AA version 31.

Yes I tried.

I tried using Excel Open - Get multiple cells - Write from data table to new excel -

The new excel gives same error using Excel as DB connect action.

 

Now also getting error while trying to open Excel Advanced: Open Action

 

Are you able to open the new Excel file manually after writing data from the data table?

Yes, there is no issue in opening excel manually. The issue is with the connecting using Excel as DB

Hi ​@Hasna Nizam ,

Can you please try to repair the Microsoft O365 Apps from Control Panel and reinstall the Access 2016 Runtime.

This troubleshooting may resolve the issue.

Thanks