Powershell delete column from CSV

Question

I want to write a script for our Telephone system (3CX) that should convert a xlsx to a csv (this works fine), but I don't know how to replace a column
with a "," if its a certain value.

The format of our Contacts in the xlsx:

Name, Surname, Company, Mobile, Zip Code, Territory, Language, Telephone, Country, Email, Minor, Nr., Sellerscode, Currency

John;Doe;Microsoft;+49 (0)170 11223344;24558;;DEU;+49 (0)89 777778;DE;[email protected];Nein;KT001122;;

I need this format:

FirstName;LastName;Company;Mobile;Mobile2;Home;Home2;Business;Business2;Email;Other;BusinessFax;HomeFax;Pager

John,Doe,Microsoft,004917011223344,,,,00494193750860,,[email protected],,,,

Can anyone help me please, to replace the following columns with a ","?

Zip-Code, Territory, Language, Country, Minor, Nr., Sellerscode, Currency

This is what I get if I run it:

FirstName,LastName,Company,Mobile,Mobile2,Home,Home2,Business,Business2,Email,Other,BusinessFax,HomeFax,Pager

john,doe,Microsoft,0049 151 119842547,24558,,DEU,0049 4193 546860,DE,[email protected],Nein,KT001122,,

jane,doe,Apple,0049 151 1215362547,24558,,DEU,0049 4193 8464860,DE,[email protected],Nein,KT001122,,

max,doe,Microsoft,0049 151 11864357,24558,,DEU,0049 4193 535163560,DE,[email protected],Nein,KT001122,,

This is what I need to get:

FirstName,LastName,Company,Mobile,Mobile2,Home,Home2,Business,Business2,Email,Other,BusinessFax,HomeFax,Pager

John,doe,Microsoft,0049151119842547,,,,0049419546860,,[email protected],,,,

Jane,doe,Apple,00491511215362547,,,,004941938464860,,[email protected],,,,

max,doe,lenovo,004915111864357,,,,00494193535163560,,[email protected],,,,

This is my script:

$xlsx = 'U:\Tools\3cx_contacts\Kontakte-3cxExport.xlsx'

# Excel should be installed on the system
$InFile = Get-Item $xlsx
$OutFile= $InFile.FullName.replace($InFile.Extension,".csv")
$Excel = new-object -ComObject "Excel.Application"
$Excel.DisplayAlerts = $True
$Excel.Visible = $False # $True while testing
$WorkBook = $Excel.Workbooks.Open($InFile.FullName)
$WorkBook.SaveAs($OutFile, 6) # 6 -> type csv
$WorkBook.Close($True)
$Excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

$file  = 'U:\Tools\3cx_contacts\Kontakte-3cxExport.csv'
$file2 = 'U:\Tools\3cx_contacts\Kontakte-3cxExport2.csv'
$file3 = 'U:\Tools\3cx_contacts\Kontakte-3cxExport3.csv'

$search1 = '  '
$replace1 = ' '
$search2 = ';'
$replace2 = ','
$search3 = '\(0\)'
$replace3 = ''
$search4 = 'Ä'
$replace4 = 'Ae'
$search5 = 'ä'
$replace5 = 'ae'
$search6 = 'Ü'
$replace6 = 'Ue'
$search7 = 'ü'
$replace7 = 'ue'
$search8 = 'Ö'
$replace8 = 'Oe'
$search9 = 'ö'
$replace9 = 'oe'
$search10 = "\+"
$replace10 = '00'



# remove first two lines
get-content $file |
    select -Skip 2 |
    set-content "$file-temp"
move "$file-temp" $file2 -Force


#Import-Csv -Path $file -Delimiter "," | 
#    Select-Object -ExcludeProperty "PLZ-Code", Gebietscode, Sprachcode, Länder-/Regionscode, Minderjährig, Nr.,    Verkäufercode, Währungscode | 
#        Export-Csv -Path  $file2 -Delimiter "," -Encoding UTF8 -NoTypeInformation


# insert specific first line
$x = Get-Content $file2
$x[0] = "FirstName;LastName;Company;Mobile;Mobile2;Home;Home2;Business;Business2;Email;Other;BusinessFax;HomeFax;Pager"
$x | Out-File $file2

# remove dual spaces
for ($i = 0; $i -lt 10; $i++)
{
    (Get-Content -Path $file2) -replace $search1,$replace1 | Set-Content -Path $file2

}
   
# replace german chars
(Get-Content -Path $file2) -replace $search2,$replace2 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search3,$replace3 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search4,$replace4 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search5,$replace5 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search6,$replace6 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search7,$replace7 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search8,$replace  | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search9,$replace9 | Set-Content -Path $file2
(Get-Content -Path $file2) -replace $search10,$replace10 | Set-Content -Path $file2

$purchaser = 'Einkauf,'
$content = Get-Content $file2

Get-Content $file2 | 
    Where-Object { $_ -notmatch $purchaser } | 
    Set-Content $file3

could you put an example of xls file and the the result you wait? with a sample it will be easier to help you — Frenchy
– Frenchy, Commented May 10, 2021 at 7:17
As aside.. use a Hashtable for the replacements and chain them instead of the extremely inefficient way you do that now with numerous Get-Content calls on the same file over-and-over again. — Theo
– Theo, Commented May 10, 2021 at 7:42
so you dont want to suppress columns, you want to reset the values of some columns? — Frenchy
– Frenchy, Commented May 10, 2021 at 8:00

Frenchy · Accepted Answer · 2021-05-10 12:14:23Z

This is a solution using regex: as you see ,for your replace , i have created an object with key = value to search and value = replace, i have set just some couples (key,value)

$file2 ="C:\Documents\tel.csv"
#use -Raw to put all the file in a string instead of an array of string
$x = Get-Content $file2 -Raw
$OFS = "`r`n"
$title = "FirstName,LastName,Company,Mobile,Mobile2,Home,Home2,Business,Business2,Email,Other,BusinessFax,HomeFax,Pager"
$title += $OFS

#add your search,replace couples
$r = @{
    '  '= ' ';
    ";" = ",";
    "\(0\)"= "";
    "Ä" ="Ae";
    "\+" = "00"
}
#do the job for each couple
foreach ($k in $r.Keys) {
  $x = $x -replace $k, $r.$k
}

#work on final job, to reset some values
$option = [System.Text.RegularExpressions.RegexOptions]::Multiline 
$pattern = [regex]::new("^(?<four>(?:.*?,){4})(?:.*?,){3}(?<Business>.*?,).*?,(?<Email>.*?,).+$", $option)

#do the replace final
$x = $pattern.Replace($x, '${four},,,${Business},${Email},,,');

#add line of title
$x = $title + $x

explanation about final regex:

^                                    Beginning of the line
(?<four>(?:.*?,){4})                 captured group named four -> 4 times .*,
(?:.*?,){3}                          3 times .*, to discard
(?<Business>.*?,)                    captured group named Business
.*?,                                 1 time .*, to discard
(?<Email>.*?,)                       captured group named Email
.+$                                  rest of the line

If you want to work on other fields: use this pattern to help you

$pattern = ""
$pattern += "^(?<firstname>.*?,)"
$pattern += "(?<lastname>.*?,)"
$pattern += "(?<company>.*?,)"
$pattern += "(?<mobile>.*?,)"
$pattern += "(?<mobile2>.*?,)"
$pattern += "(?<home>.*?,)"
$pattern += "(?<home2>.*?,)"
$pattern += "(?<business>.*?,)"
$pattern += "(?<business2>.*?,)"
$pattern += "(?<email>.*?,)"
$pattern += "(?<other>.*?,)"
$pattern += "(?<businessfax>.*?,)"
$pattern += "(?<homefax>.*?,)"
$pattern += "(?<pager>.*)$"
  
# becareful: use simple quote
$newvalue =''
$newvalue += '${firstname}'
$newvalue += '${lastname}'
$newvalue += '${company}'
$newvalue += '${mobile}'
$newvalue += ','  #nothing for mobile2
$newvalue += ','  #nothing for home
$newvalue += ','  #nothing for home2
$newvalue += '${busisness}'
$newvalue += ','  #nothing for busisness2
$newvalue += '${email}'
$newvalue += ','  #nothing for other
$newvalue += ','  #nothing for businessfax
$newvalue += ','  #nothing for homefax
$newvalue += ''   #nothing for pager

$resultfinal = $pattern.Replace("textoffile", $newvalue)

Collectives™ on Stack Overflow

Powershell delete column from CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related