by stasov at 2012-10-19 07:05:29
Hi !by poshoholic at 2012-10-19 07:16:43
I’m using EmailAndParser to connect to a pop3 mailbox and to parse e-mails,subjects etc into an excel xlsx file.
After the parsing is complete I read the info from the file with powershell.
The problem is that some times after the parsing is complete I try to open the Excel file manually and get the following error :
"excel found unreadable content in example.xlsx .do you want to recover the contents of this workbook? if you trust the source of this workbook, click yes."
After choosing ‘yes’ and Excel informs that it repaired the file :
"Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)"
when I choose to look at the log file , I get the following information :<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error064840_01.xml</logFileName><summary>Errors were detected in file ‘C:\all-mai2l.xlsx’</summary>-<repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)</repairedRecord></repairedRecords></recoveryLog>
I have no idea which entry is the problematic one,
I also don’t know if an entry was deleted by the Excel repair function. ( I parse through about 2000 e-mails , all are from different and unexpected sources )
when I try to open the file with Powershell , I get the following message :
Exception calling "Open" with "1" argument(s): "Open method of Workbooks class failed"At C:\Users\Administrator\Desktop\nagi-mail\bad_excel.ps1:7 char:40
+ $objWorkbook = $objExcel.Workbooks.Open <<<< ($destinationFilePath)
+ CategoryInfo : NotSpecified: (, MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
You cannot call a method on a null-valued expression.
At C:\Users\Administrator\Desktop\nagi-mail\bad_excel.ps1:8 char:54
+ $oracleBackupWorksheet = $objWorkbook.Worksheets.Item <<<< (1)
+ CategoryInfo : InvalidOperation: (Item:String) , RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At C:\Users\Administrator\Desktop\nagi-mail\bad_excel.ps1:13 char:19
+ $objWorkbook.Close <<<< ()
+ CategoryInfo : InvalidOperation: (Close:String) , RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
I’ve tried to open the file on different machines, and on different types of office ( 2010 , 2007 )
I didn’t try 2003 because the EmailAndParser App only writes to xlsx files.
This parsing process is automatic, I have to find a way to repair the damaged Excel file without manually choosing ‘YES’ and saving.
I don’t mind loosing an entry or two, as long as the process continues.
Is there a way powershell can handle this ?
Any help will be greatly appreciated !
Thanks,
I’m not sure how to automate the repair of the file using the Excel COM interface. You might be able to figure that out by recording a macro of what Excel does when you open and repair the file and then translating that into your interaction with Excel via COM.by stasov at 2012-10-19 07:55:09
Another option that might be easier is to customize Email&Parser (since it is customizable) so that it exports the contents in a csv file instead of as an xlsx file, so that you can simply invoke Import-Csv to get the contents into PowerShell for processing.
Thanks so much for the quick reply.by stasov at 2012-10-19 08:10:35
$objWorkbook = $objExcel.Workbooks.Open($destinationFilePath,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,1)
did the job.
CorruptLoad ,Optional ,XlCorruptLoad
Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad and does not attempt recovery when initiated through the OM.
http://msdn.microsoft.com/en-us/library/office/bb179167(v=office.12).aspx
Thanks poshoholic
I forgot.by poshoholic at 2012-10-19 08:32:37
you have to put this also :$missing = [System.Reflection.Missing]::Value
and then :$objWorkbook = $objExcel.Workbooks.Open($destinationFilePath,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,1)
Excellent, glad that helped you figure it out! I’ll have to remember that solution for later, I’m sure I’ll run into it myself at some point.