Exporting worksheet to csv while keeping excel formats (where applicable) about importexcel HOT 9 OPEN

Thanks for trying ImportExcel.

so if the the Excel sheet has "$1,234.00" that is what you want in the csv?

from importexcel.

Yep! Or say a (1,000) instead of -1000. Or if we append some text suffix to a value...such as the value is 2 but the custom format text is 2 days.

from importexcel.

Off the top of my head, I don't recall having that. I'll think about if it is in there. Took a quick scan, didn't see anything.

Need to take a deeper look. If it is not there, will think about how hard to add.

from importexcel.

You're awesome! The functionality is to try and replicate if I 'save as' my xlsx worksheet to csv. It prompts me that I'll lose some unsupported stuff (e.g. background color)...but everything else seems to work as expected. Wondering if there is a way to mimic this but obviously without having to click through prompts about saving/losing features.

from importexcel.

Ah, so the save as in Excel when choosing csv, it keeps the $1,235.00?

Without looking at the code I do believe Export-Excel returns the value. I think text has the formatting. Not sure how much.
Not sure how much may break if a param enables returning either one.

from importexcel.

Yes observationally it seems to keep all the desired format variations.

from importexcel.

Hey there @jhoneill. I'm mixing up params in my head between Import and Export -excel. There was no switch on Export-Excel to grab the .text of a cell rather than .value?

Time to do a spike test on this.

from importexcel.

Hey there @jhoneill. I'm mixing up params in my head between Import and Export -excel. There was no switch on Export-Excel to grab the .text of a cell rather than .value?

Time to do a spike test on this.

No ... we're generally sticking number / text / date / boolean and then applying formatting as distinct operation with Export.

Import is bringing each row in as an object so each cell in that row comes in as the number/date/string in .Value which is almost always what we want. Getting .text instead of .value would be easy enough

from importexcel.

@jhoneill thanks, yeah, thinking of adding something like [Switch]$AsText then `$targetProperty = if($AsText) {'text'} else {'value'}.

Hopefully off to the races.

from importexcel.