Import csv file into Excel without losing leading zeroes-CodePudding

I am trying to import a CSV file into Excel. There are lots of examples of how to do this and most of them have you open the CSV in Excel and then copy the data out and then paste it into the workbook wherever you want it. I am doing this, and it works great ... until you have a column where you have fields with leading zeroes and Excel truncates the leading zeroes, so I added a command to set the number of zeroes:

$sh1_wb1.Range("L:L").NumberFormat = "000000000"

This appears to work great...but when this sheet is being processed by another system, it does not see the leading zeroes. When I open the sheet and click into the field, the leading zeroes are missing. (The other system sees the actual value in the cell, not the displayed value.)

In other words, even though I see: 000012345, when I actually click into the cell, it shows that what is there is 12345.

I know what is happening is that when I open the .csv in Excel (to do the copy), Excel drops the leading zeroes. It's just a thing that Excel does.

I've tried building the Excel sheet by looping through all the cells and moving them individually from the .csv file, but that process is very slow. Here is the code that I am using to do this. It works, but it takes about 5 seconds per Excel row and I have like 30,000 rows per worksheet (into an Excel that needs about 15 tabs populated, each with 30,000 rows), so even though this works, it would take about 41 hours just to process one sheet. :

(

    $inputfile = $csvpath
    $outputfile = $xlpath
    
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $wb = $excel.Workbooks.Add()
    $ws = $wb.Sheets.Item(1)
    $ws.Cells.NumberFormat = "@"
    write-host "Opening $inputfile"
    $i = 1
    Import-Csv $inputfile | Foreach-Object { 
    $j = 1
    foreach ($prop in $_.PSObject.Properties)
    {
        if ($i -eq 1) {
            $ws.Cells.Item($i, $j).value = $prop.Name
        } else {
            $ws.Cells.Item($i, $j).value = $prop.Value
        }
        $j  
    }
    $i  
    }
    $wb.SaveAs($outputfile,51)
    $wb.Close()
    $excel.Quit()
    write-output "Success"

The only way I've found to efficiently (manually) bring a .csv file with numeric values with leading zeroes into Excel "properly" is to connect to it as a data source, and before importing it, transform it to make that column Text.

Does anyone know if an Import Text into Excel, with modified format values for certain columns can be done using PowerShell?

Thank you very much for any advice or assistance!

CodePudding user response：

If you don't want Excel to treat the values as a number (forget formatting, formatting is just for display purposes not for actual value), you can set the NumberFormat for the column to text before you paste the data in, then it won't strip any leading zeros. Something like:

$inputfile = $csvpath
$outputfile = $xlpath

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$wb = $excel.Workbooks.Add()
$ws = $wb.Sheets.Item(1)
$ws.Cells.NumberFormat = "@"
write-host "Opening $inputfile"
$i = 1
Import-Csv $inputfile|ConvertTo-Csv -NoTypeInformation -Delimiter "`t"|clip
$excel.ActiveCell.PasteSpecial()
$wb.SaveAs($outputfile,51)
$wb.Close()
$excel.Quit()
write-output "Success"