add column of text to beginning of csv using powershell

Question

I am converting a spreadsheet into csv format using the script below. What I need to do is add a column at the beginning that is labeled 'Order Type Code' and each entry under it will simply say 'SO'

#CBRCust,StoreNum,StoreName,Load,Stop,Item,Quantity,Pack,Size,ItemDescription,UPC

$headers = "CBRCust","StoreNum","StoreName","Load","Stop","Item","Quantity","Pack","Size","ItemDescription","UPC"
$source = Import-Csv Sample.csv -Header $headers

#setup Output Table
$tabName = "Output"

#Create Table object
$table = New-Object system.Data.DataTable “$tabName”

#Define Columns
$col1 = New-Object system.Data.DataColumn ColumnName1,([string])
$col2 = New-Object system.Data.DataColumn ColumnName2,([string])

#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)

#Index the source and create a new table.foreach ($row in $source)
{
    $newrow = $table.NewRow()
    $newrow.ColumnName1 = $row."CBRCust"
    $newrow.ColumnName2 = $row."StoreName"
   $table.Rows.Add($newrow)
}

    #output Table
$table | format-table -AutoSize 
#save file
$table | Export-Csv Output.csv -NoTypeInformation

I tried using the script below but it didn't work

#Define Columns
$col1 = New-Object system.Data.DataColumn OrderTypeCode,([string])
$col2 = New-Object system.Data.DataColumn ColumnTest1,([string])
$col3 = New-Object system.Data.DataColumn ColumnName2,([string])


#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)

#Index the source and create a new table.
foreach ($row in $source)
{
    $newrow = $table.NewRow()
    $newrow.OrderTypeCode = 'SO' 
    $newrow.ColumnTest1 = $row."CBRCust"
    $newrow.ColumnName2 = $row."StoreName"
    $table.Rows.Add($newrow)
}

When you say that something didn't work, you have to say how it didn't work. — Bill_Stewart
– Bill_Stewart, Commented Mar 2, 2018 at 17:31
I get an error saying "Exception setting 'OrderTypeCode' cannot be found on this object. Verify that the property exists and can be set." — IowaMatt
– IowaMatt, Commented Mar 2, 2018 at 17:35
I'm just curious why you feel the need to use a DataTable? Are you doing something with it? If not, then you can just Import-CSV, Add-Member, Select-Object, Export-CSV. — EBGreen
– EBGreen, Commented Mar 2, 2018 at 17:58

EBGreen · Accepted Answer · 2018-03-02 18:19:48Z

1

Well, if you don't need the DataTable see if this does something close to what you need:

$headers = "CBRCust","StoreNum","StoreName","Load","Stop","Item","Quantity","Pack","Size","ItemDescription","UPC"
$source = Import-Csv Sample.csv -Header $headers
$source | Select CBRCust, StoreName, @{n='OrderTypeCode'; e={'SO'}} | Export-CSV Output.csv -NoTypeInformation

answered Mar 2, 2018 at 18:19

EBGreen

38k12 gold badges69 silver badges86 bronze badges

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

5 Comments

EBGreen Over a year ago

That is untested and off the cuff but it should be close.

EBGreen Over a year ago

If this does work for you let me know and I'll add some explaination for you.

IowaMatt Over a year ago

I'm not 100% sure but I think I am using the dataTables because I need to re-order the columns from the Source. Basically SourceCOlumnA = OutputColumnB, SourceColumnB = OutputColumnD, etc...

EBGreen Over a year ago

Select will put them in whatever orde you want. Also one more reason to tell the company that wrote the software that you are preparing the import for that they need to write their crap better.

IowaMatt Over a year ago

Ugh...This is a nightmare from all angles. Would love to tellt he vendor to just code a simple import. I guess this is what I get for looking bored ;)

IowaMatt · Accepted Answer · 2018-03-02 19:20:25Z

So I figured out how to do it. Just had to define the column as a text field instead of a datacolumn

#CBRCust,StoreNum,StoreName,Load,Stop,Item,Quantity,Pack,Size,ItemDescription,UPC

$headers = "CBRCust","StoreNum","StoreName","Load","Stop","Item","Quantity","Pack","Size","ItemDescription","UPC"
$source = Import-Csv Sample.csv -Header $headers

#setup Output Table
$tabName = "Output"

#Create Table object
$table = New-Object system.Data.DataTable “$tabName”

#Define Columns
$col1 = 'Order Type Code'
$col2 = New-Object system.Data.DataColumn OrderNo,([string])
$col3 = New-Object system.Data.DataColumn OrderNo2,([string])
$col4 = New-Object system.Data.DataColumn OrderNo3,([string])
$col5 = New-Object system.Data.DataColumn OrderNo4,([string])
$col6 = New-Object system.Data.DataColumn OrderNo5,([string])
$col7 = New-Object system.Data.DataColumn VendorCode,([string])
$col8 = New-Object system.Data.DataColumn AccountCode,([string])
$col9 = New-Object system.Data.DataColumn FromWarehouse,([string])
$col10 = New-Object system.Data.DataColumn ToWarehouse,([string])
$col11 = New-Object system.Data.DataColumn AddressOverride,([string])
$col12 = New-Object system.Data.DataColumn AddressLine1,([string])
$col13 = New-Object system.Data.DataColumn AddressLine2,([string])
$col14 = New-Object system.Data.DataColumn City,([string])
$col15 = New-Object system.Data.DataColumn State,([string])
$col16 = New-Object system.Data.DataColumn Zip,([string])
$col17 = New-Object system.Data.DataColumn Country,([string])
$col18 = New-Object system.Data.DataColumn ContactName,([string])
$col19 = New-Object system.Data.DataColumn ContactPhone,([string])
$col20 = New-Object system.Data.DataColumn ContactExt,([string])
$col21 = New-Object system.Data.DataColumn ContactFax,([string])
$col22 = New-Object system.Data.DataColumn OwnerCode,([string])
$col23 = New-Object system.Data.DataColumn CarrierCode,([string])
$col24 = New-Object system.Data.DataColumn LeavesDate,([string])
$col25 = New-Object system.Data.DataColumn ArrivesDate,([string])
$col26 = New-Object system.Data.DataColumn DeliveryInstructions,([string])
$col27 = New-Object system.Data.DataColumn Stop,([string])


#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)
$table.columns.add($col6)
$table.columns.add($col7)
$table.columns.add($col8)
$table.columns.add($col9)
$table.columns.add($col10)
$table.columns.add($col11)
$table.columns.add($col12)
$table.columns.add($col13)
$table.columns.add($col14)
$table.columns.add($col15)
$table.columns.add($col16)
$table.columns.add($col17)
$table.columns.add($col18)
$table.columns.add($col19)
$table.columns.add($col20)
$table.columns.add($col21)
$table.columns.add($col22)
$table.columns.add($col23)
$table.columns.add($col24)
$table.columns.add($col25)
$table.columns.add($col26)
$table.columns.add($col27)

#Index the source and create a new table.
foreach ($row in $source)
{
    $newrow = $table.NewRow()
    $newrow.'Order Type Code' = 'SO'
    $newrow.OrderNo = $row.'SO'
    $newrow.OrderNo = $row."CBRCust"
    $newrow.OrderNo2 = $row."StoreName"
    $newrow.OrderNo3 = $row.""
    $newrow.OrderNo4 = $row."" 
    $newrow.OrderNo5  = $row.""
    $newrow.VendorCode  = $row.""
    $newrow.AccountCode = $row.""
    $newrow.FromWarehouse = $row.""
    $newrow.ToWarehouse = $row.""
    $newrow.AddressOverride  = $row.""
    $newrow.AddressLine1  = $row.""
    $newrow.AddressLine2   = $row.""
    $newrow.City = $row.""     
    $newrow.State  = $row.""
    $newrow.Zip = $row.""
    $newrow.Country  = $row.""
    $newrow.ContactName  = $row.""
    $newrow.ContactPhone = $row.""
    $newrow.ContactExt  = $row.""
    $newrow.ContactFax  = $row.""
    $newrow.OwnerCode  = $row.""
    $newrow.CarrierCode  = $row.""
    $newrow.LeavesDate = $row.""
    $newrow.ArrivesDate  = $row.""
    $newrow.DeliveryInstructions  = $row.""
    $newrow.Stop  = $row.""
    $table.Rows.Add($newrow)
}

#output Table
$table | format-table -AutoSize 
#save file
$table | Export-Csv Output.csv -NoTypeInformation

#$table | ConvertTo-Csv -NoTypeInformation | foreach {$_.Replace('"','')} | Out-File 1output.csv

Still think you can do it without the DataTable :P. But it's a scripting language, at the end of the day if you did what you needed to do then you did it right.

Collectives™ on Stack Overflow

add column of text to beginning of csv using powershell

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related