Import Excel, Export CSV with PowerShell

Question

Due to restrictions I either need to use VB or PowerShell for this task.

I have an Excel that looks like:

ColumA,              ColumB,ColumC,ColumD,ColumE,ColumF
000|Txt,MoreTxt ,    ColumB,ColumC,ColumD,ColumE,ColumF

I read about import_csv -header, but I'm under to successfully do it. I'll post my script below. The export I expect is:

ColumA, ColumB, ColumC, ColumD, ColumE, ColumF
000,    ColumB, ColumC, ColumD, ColumE, ColumF

Only Colum gets modified, and I -only- need the digits from before that pipe. It also has to stay three digits, so 1 becomes 001, etc.

This is the script I modified based on some previous inquiries I saw, and the MS Tutorial.

   $file = import-csv "C:\path\to\my\file\test.csv"
    foreach ($row in $file){
        $tempfile = New-Object psobject -Property @{
            ColumA = $row. 'ListName'.substring(0,2)
            ColumB = $row. 'ColumB'
            ColumC = $row. 'ColumC'
            ColumE = $row. 'ColumE'
            ColumF = $row. 'ColumF'
        }
        $expandfile = @()
        $expandfile += $tempfile | select ColumA, ColumB, ColumC, ColumD,  ColumE, ColumF
    }

PS gives me both errors on not liking everything I have in quotes (Which I thought was the column name, but I guess not. And also a parse error on the entire array. Essentially the entire script.

UPDATE Providing real examples of source.

"Tiam
Name",SiamName,Siam,Ciam,Piam,Liam,Niam,Diam
"002|City, State","City, State - Some text (15092)",1,"3,408",99,"3,408",780,22.89%
"009|City, State","City, State - Some Text (E) (15450)",1,"1,894",81,"1,894",543,28.67%

TessellatingHeckler · Accepted Answer · 2016-10-29 02:15:25Z

1

Edit:

$expandfile = Import-Csv "C:\path\to\my\file\test.csv" | ForEach-Object {
    $_."Tiam`r`nName" = $_."Tiam`r`nName".SubString(0,3)
    $_
}

edited Oct 29, 2016 at 2:15

answered Oct 28, 2016 at 3:37

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

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

6 Comments

DNorthrup Over a year ago

Hey thanks for the tip. Your minimized version of mine is actually much closer. No, the imported file is an Excel file. I'm now getting a mass warning for 'You cannot call a method on a null-valued expression' then ~10 "The property Colum1 cannot be found. I essentially just have your shortened version but with my columns.

TessellatingHeckler Over a year ago

@DNorthrup sounds like you don't have the column names in the script matching the CSV file. Do the names have spaces in them? e.g. do they start or end with a space?

DNorthrup Over a year ago

2 of them had a space in the middle, but since I thought it might be that, I removed it. I also removed the non-sensical header I -was- telling it to ignore to see if that helped.

TessellatingHeckler Over a year ago

@DNorthrup "a,b,c`n1,2,3`n4,5,6" | ConvertFrom-Csv | % {$_.b = 7; $_} demonstrates it working in principle. I can only suggest you edit your original question with an actual copy/paste of the top couple of lines of your CSV, hopefully someone can see exactly what's going on.

DNorthrup Over a year ago

That's true - Fair enough. I updated the original post with exact samples of the Import. Since it is an Excel by default they will all have double quotes - However it seemed to completely ruin the formatting when I put the quotations in the update.

|

Collectives™ on Stack Overflow

Import Excel, Export CSV with PowerShell

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related