hello.  thanks for the time.  I had a question in regards to how to spit out a result.  i am exporting computer systems information for all users. when i export using export-csv some cell values get overwritten by Excel as like a numeric value (i.e. if the serial number is like 12346789449 ... excel changes that to 1.4E2 ... I don't want since it is ruining the results.  When i export to text file it the result works however it is not in a column format.  so i am looking for either to excel to stop converting the value or how to export in txt in a column format.  I tried format-table but no go.

MachineType          : desktop
MachineName  : mycomputer
MachineModel         : dell
MachineSerial            : 134rfh44433
MachineOS            : windows

so how can i get that into a column format?  thanks.

Excel displays big numbers by default in the scientific format. You could change that programmatically, but that's a bit to much overhead. I recommend to do that in Excel (only a few clicks); for details how to do that you may refer to this link: http://www.techonthenet.com/excel/questions/remove_scientific.php

wizend

Hi,

Any update about the issue? Please let us know if you would like further assistance.

In additionhow about use Export-csv c:\results.csv  NoTypeInformation?

Regards

Yan Li

If you have any feedback on our support, please click here .

With Text Import Wizard in Excel, you can explicitly define the data format/type of each column of a csv file. Otherwise, if handling of specific numberic fields should be treated as text, you could prefix such a numeric value with a single quote for use with Export-CSV e.g. '3.142

Beyond that, you can Format Cells / Number, select the category (General, Number, Currency, etc.) and define the desired format by hand or programmatically in Excel (PowerShell, VBScript, etc.).

Please share with us if this helps. Thanks.

instead on export-csv try convertto-csv and pipe to out-file with ASCII encoding

eg:

Convertto-csv -notypeinformation|out-file .\output.csv -Encoding ASCII

I find I don't need to use the import wizard to import the data to excel when using this method

This is no PowerShell Problem! This is an Excel issue!

Excel automatically convert some formats to an other format during CSV import.
This includes Numbers with leading Zeros, Date / Time conversions, currency and so forth....

The only way to prevent Excel of doing that for all this, is to tell Excel to Import the values as Type of text:

http://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates

http://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text

http://www.howtogeek.com/howto/microsoft-office/how-to-import-a-csv-file-containing-a-column-with-a-leading-0-into-excel/

http://risingline.com/use-excel-read-csv-without-reformatting.php

You can use my Function Export-XLS for that.

http://poshcode.org/4369

Description:

Many users believe CSV is just another type of Excel file, however this is not the case.
Microsoft Excel will automatically convert data columns into the format that
it thinks is best when opening a CSV or a Tab delimited data file.
For example, Excel will remove leading Zeros of Numbers, change Data/Time Formats or uses
the sientific numberformat for large Numbers and others.
This can go unnoticed in large data sets.
If you enclose each data field in double quotes and putt an '=' before the double quotes
this will force the Excel parser to Import the data as type of Text.
Example: ="<data>"
So the data will leaved unchanged / unconverted.
Excel 2007 and above need a slight different data masking which looks like so: "=""<data>""".
This data format also works with Excel 97.

If you dont want that Excel changes / converts the data, you can use the -NoReformat parameter of this function.
This will store the Tab delimited file with the the described dataformat "=""<data>""" .
So you can open the .xls file without changing Data

Hi,

Just checking in to see if the suggestions were helpful. Please let us know if you would like further assistance.

If you have any feedback on our support, please click here .