Error message: "Insufficient available rows (0) to expand table."
Altair EmployeeHi all,
I have come across an error message that I can not resolve by myself. A project export to an MS Excel (.xlsx) file worked properly until the end of 2016. Since then it won't export to the same file and gives the message ""Insufficient available rows (0) to expand table." The excel file counts less than 8000 rows. Size of the file is only 1,118 KB.
If I change the name of the export file and therefore create a different file it exports properly. So, the workaround that I am currently using is export it to a different file and then copy to the file where I want the data but this is not a satisfactory solution.
I am not sure if the date is the issue because the last time the export was successful was on 2nd January 2017.
Ideas anyone?
Thanks,
Find more posts tagged with
Altair EmployeeHello Peter,
I guess you're appending data to a worksheet in Excel? Maybe someone changed the name of the worksheet? Or if you're using named ranges, perhaps a user has inadvertently changed the name or shape of these?
Best wishes
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
Hi Olly,
Yes, the project export appends new data to a worksheet in excel. I don't see any changes made to the file name and I am not using named ranges.
I just tried something different: I renamed the excel sheet and changed the project export to append the data in the new file. Results in the same error message.
I am using Monarch 11.8
Altair EmployeeHi Olly,
Yes, the project export appends new data to a worksheet in excel. I don't see any changes made to the file name and I am not using named ranges.
I just tried something different: I renamed the excel sheet and changed the project export to append the data in the new file. Results in the same error message.
I am using Monarch 11.8
Hello Peter,
I think xlsx can in theory have a million rows in the table, and I presume you haven't hit that limit.
I have a hunch that it might be field types - that Excel has decided that a column is numeric or character and when Monarch tries to write to it, Excel is not liking it.
11.8 is about four years and two Office versions old, and no longer supported. Has the flavor of XLSX changed from eg Office 2007 to 2013 in your environment?
Best wishes,
Olly
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
Altair EmployeeHello Peter,
I think xlsx can in theory have a million rows in the table, and I presume you haven't hit that limit.
I have a hunch that it might be field types - that Excel has decided that a column is numeric or character and when Monarch tries to write to it, Excel is not liking it.
11.8 is about four years and two Office versions old, and no longer supported. Has the flavor of XLSX changed from eg Office 2007 to 2013 in your environment?
Best wishes,
Olly
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
Hi Olly,
I am still on office 2010 and this has not changed since I was supplied with Monarch 11.8.
Your suggestion that it might be field types would mean that it would also not export to other files would it not? It will just not export beyond the current 7775 rows regardless of the file name. Exporting to an empty excel file is no problem though. Number of rows to export is low: roughly between 1 and 100 lines max.
I appreciate your help with this!
Altair EmployeeHi Olly,
I am still on office 2010 and this has not changed since I was supplied with Monarch 11.8.
Your suggestion that it might be field types would mean that it would also not export to other files would it not? It will just not export beyond the current 7775 rows regardless of the file name. Exporting to an empty excel file is no problem though. Number of rows to export is low: roughly between 1 and 100 lines max.
I appreciate your help with this!
Hello Peter,
No - that file type issue is specifically Excel. One workaround would be to append the data to an Access database, and then in a separate Monarch project open the Access database and from this overwrite the Excel.
Best wishes,
Olly
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
Altair EmployeeHello Peter,
No - that file type issue is specifically Excel. One workaround would be to append the data to an Access database, and then in a separate Monarch project open the Access database and from this overwrite the Excel.
Best wishes,
Olly
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
Thank for Olly for your help and suggestions. For now I will just create a new '2017' file.
I am assuming though that there is no limit in the number of rows that can be appended...
Altair EmployeeThank for Olly for your help and suggestions. For now I will just create a new '2017' file.
I am assuming though that there is no limit in the number of rows that can be appended...
Hello Peter,
You're only limited by the format of XLSX - which I think is a million rows and 2GB of data in total, so one million rows of 2 kilobytes per row, or a hundred thousand rows of 20 kilobytes per row etc.
Best wishes,
Olly
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
Altair EmployeePeter,
I suspect this will be something to do with Excel settings.
I ran a search and came across this information.
Excel Table Doesn't Expand For New Data - Contextures Blog
It may or may not have a solution (there are several suggestion for things that can be problematic in this sort of way) that applies to your specific case but may suggest some things to look at or ways to try to discover what is going on.
The original post is a bit "left-field" and has some additions that are more main stream so it is worth reading it all.
There may well be still more to discover from other sources ...
HTH.
Grant
I realize this is almost 8 years after the original post, but we have recently encountered and resolved the same error. For us it was caused by trying to write to an existing spreadsheet, after manually adding a new column in both Monarch and the Excel file. Monarch would create a new tab with the same name and a "1", and if we tried renaming that tab, it would throw the error.
We ended up fixing it by copying the existing Excel rows into a new file, and we did have to copy the full rows by clicking on the row numbers on the left to select them - copying just the cells that actually had data in them did not work, and continued to receive the error. Something about the way Monarch reads the Excel is causing it to think there is no room to expand if you copy just the cells with values. But copying and pasting the full rows removed the error for us.
I hope someone in the future finds this helpful.
Hello Peter,
I guess you're appending data to a worksheet in Excel? Maybe someone changed the name of the worksheet? Or if you're using named ranges, perhaps a user has inadvertently changed the name or shape of these?
Best wishes
Olly Bond
MONARCH ? | ? | ? | ? EXPERTS
www.monarchexperts.com<http://www.monarchexperts.com>
olly@monarchexperts.com<mailto:olly@monarchexperts.com>