Import csv into excel and specify cell format

Question

I am trying to import multiple csv files into their own tabs in 1 excel workbook. I am having an issue with long number fields being displayed as exponential data and changing the last digit to 0. For example I have a 16 digit account number (1234567890123456) it is being displayed in excel as an exponential number (1.23457E+15). When I look at the actual number in the cell it is (1234567890123450). I assume if I make the column text before I bring it in, it will work, but I'm not sure how to do that. Here is my code.

$excel = New-Object -ComObject excel.application 
$excel.visible = $False 
$excel.displayalerts=$False 
$workbook = $excel.workbooks.add()
$sheets = $workbook.sheets
$sheetCount = $Sheets.Count
$mySheet = 1
$mySheetName = "Sheet" + $mySheet
$s1 = $sheets | where {$_.name -eq $mySheetName }
$s1.Activate()
If($sheetCount -gt 1)
{
#Delete other Sheets
$Sheets | ForEach
    {
    $tmpSheetName = $_.Name
    $tmpSheet = $_
    If($tmpSheetName -ne "Sheet1"){$tmpSheet.Delete()}
    }
}
#import csv files
$files = dir -Path $csvDir*.csv
ForEach($file in $files){
If($mySheet -gt 1){$s1 = $workbook.sheets.add()}
$s1.Name = $file.BaseName
$s1.Activate()
$s1Data = Import-Csv $file.FullName 
$s1data | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Clip
$s1.cells.item(1,1).Select()
$s1.Paste()
$mySheet ++
if (test-path $file ) { rm $file }
}

$workbook.SaveAs($excelTMGPath)
$workbook.Close()
$workbook = $null
#$excel.quit() 
while ([System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel)) {}
$excel = $null

johnny · Accepted Answer · 2014-05-29 13:59:36Z

1

Try

If $s1 is pointed correctly,

$s1.cells.item(1,1).NumberFormat="@"

If that does not work, use NumberFormat where necessary. Use the format you prefer.

Change the name of your file extension from .csv to .txt. Adjust your filename in the code,

$files = dir -Path $csvDir*.txt

edited May 29, 2014 at 13:59

answered May 29, 2014 at 13:20

johnny

19.9k54 gold badges169 silver badges271 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3266792 Over a year ago

That throws this error: Property 'NumberFormat' cannot be found on this object; make sure it exists and is settable. At C:\Users\xxxx\Desktop\Powershelltest\FullScript.ps1:line:213 char:28 + $s1.cells.item(1,1).Select.N <<<< umberFormat="General"

user3266792 Over a year ago

I still get the property 'NumberFormat' Cannot be found error

johnny Over a year ago

@user3266792 I removed the selection.

user3266792 Over a year ago

That no longer throws an error, but the field is still coming in as an exponential format where it changes the last digit to a 0. I also tried NumberFormat="text", but that didn't work either.

johnny Over a year ago

@user3266792 Beyond above, I cannot help. You will have to fidget with the code, perhaps try a Range object or another cell location to test.

|

Collectives™ on Stack Overflow

Import csv into excel and specify cell format

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related