Problem exporting table using Collect Export

[Amit Narnoli]

Hi All,
I am generating big tables (more than 150 rows) using table command from a large dataset (more than 400,000 obs). When I try to export the tables, I encounter with error codes such as below:

could not write cell format halign() to file
could not write cell format border() to file

These errors appear completely randomly. Sometimes I see first error for one table, sometimes exporting the same table does not produce any error but exporting another table gives any of the above errors.

Could someone help me with these random errors? The errors force my dofile to exit again and again. I don't know what is happening with collect export commands.

Thanks,
Amit

[Amit Narnoli]

Any help in this regard is much appreciated.

[Jeff Pitblado (StataCorp)]

It is not mentioned above, but it seems you are trying to export to MS
Excel (.xls and .xlsx files). You are encountering a limit
on the number of unique formats allowed by putexcel, which is
used by collect export to produce Excel files.
putexcel formats include numeric and string formats, borders,
background colors, font styles, ...

We are aware that collect export suffering from these
putexcel limits for larger tables, and hope to have a solution to
this soon.

However, have you considered using collapse and export excel?

[Amit Narnoli]

Hi Jeff,
Thank you for your response. Yes, I was trying to export to Excelsheets. I did not do it by collapsing the data; I am aware of the collapse way, but I did not want to do it for some reason.

It would be great to see the solution in coming STATA upgrades.

Best, Amit

[Abdan Syakura]

Hello Jeff and Stata team,

When using collect export command, I also received the same error message:
could not write cell format halign() to file
could not write cell format border() to file

Has there been any solution for this error? Thank you.

Best,

Abdan

[Kehinde Atoloye]

I understand this is an old post but I also experienced same issue now but was able to overcome it in a crude way. I thought of sharing my little crude method, perhaps it may assist.

I suspected that it might be that Stata was having issues opening the file itself if the file is existing, so I had to delete the existing file manually first before running the code and it works. This approach of manually deleting the file is NOT the best as it negates modification, but I believe sharing it here may assist you in the right direction towards solving the problem.

And it may not necessarily be a Stata problem but a system problem like memory issues or some other unknown interfering system issues.

Update: My suspicion of interfering system issues may likely be the problem because after a while, other files that were having such issues just stopped being problematic without deleting them.

Thanks.