Error when imported data starts with equals sign ("=")

[An-dir]

I have noticed that there are problems importing data if the imported text starts with a "=". There may be a better solution than the current one.

#How I did my tests with unsatisfactory results
# Testresult: Errors
"Col1;Col2`r`ntext;=|0" | ConvertFrom-Csv -Delimiter ";" | Export-Excel test1.xlsx -AutoFilter -AutoSize
# Testresult: Displays the '-character
"Col1;Col2`r`ntext;'=|0" | ConvertFrom-Csv -Delimiter ";" | Export-Excel -AutoFilter -AutoSize
# works but with ugly interpretation as text in a formula
"Col1;Col2`r`ntext;=`"=|0`"" | ConvertFrom-Csv -Delimiter ";" | Export-Excel -AutoFilter -AutoSize

In an old issue you wrote ( https://github.com/dfinke/ImportExcel/issues/1007#issuecomment-798961673 ) that Excel interprets this as a formula, but the following test looks promising that there may be a better solution:

# Here is how I expect it to be handled (delimiter is language dependend in your test):
"Col1;Col2`r`ntext;=|0" | ConvertFrom-Csv -Delimiter ";" | Export-Csv test.csv -Delimiter ";" -NoTypeInformation
start excel test.csv

Then save as xlsx has no problem with displaying it as string and reopen the xlsx without errors. The recognition as a formula starts when you try to make changes - then you can decide what you want to do with the data.

There can be more characters than the "=" character or "'" at the beginning, wich can cause problems when importing.

It looks like in the "sharedStrings.xml" is missing "=|0" and maybe some other errors, like counter in this and maybe other files.

Maybe you can fix it so that when you import, it will not crash, but handle it like the xlsx via csv did

[dfinke]

please could you post a repro of the issue. data, script etc

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[saviourofdp]

A simple example that I have come across is using the output of Compare-Object

Compare-Object -ReferenceObject 1 -DifferenceObject 1 -IncludeEqual

Expected output

 InputObject SideIndicator
 ----------- -------------
 1           ==           

The file is created as normal but when opened, Excel offers to recover the file and lists the error in the recovery log

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error248800_01.xml</logFileName><summary>Errors were detected in file 'test.xlsx'</summary>
    <removedRecords>
        <removedRecord>Removed Records: Formula from /xl/worksheets/sheet1.xml part</removedRecord>
    </removedRecords>
</recoveryLog>

[dfinke]

Yes. You need to handle that in your PowerShell script. Translate == to equal for example or prefix it with a' which is how native Excel enables you to start data with an = and it does not try to execute it as a function.

[saviourofdp]

Unless I'm doing something wrong, the ' solution is not the same. If I create a workbook manually with a field "Test" with the value "'=" and create a second workbook with the script

[PSCustomObject]@{Test="'="} | Export-Excel -Path "test2.xlsx"

and then import them both, I get

Get-ChildItem . | select -ExpandProperty "FullName" | Import-Excel

Test
----
=   
'=

[dfinke]

Sorry, not sure what you are attempting. I was focused on the == if you try to enter that in the Excel UI it gives a message. I believe if you use it via PS Excel will see it as corrupt. So the '= is to prevent Excel from interpreting it as a formula

[saviourofdp]

Sorry what I was trying to get across was that if you type = into Excel and let Excel correct it to '= when you read that using this PowerShell module you get = as the value (ie no '). However if you set the value as '= using the PowerShell and then read it back using PowerShell you get '= (ie with a ')

[dfinke]

got it. hmm, try using the -AsText parameter on ImportExcel. Next would be to unzip the xlsx and inspect how XL is storing that. May not be sovable if the EPPlus layer is translating that.