Rebuilding CSV and using a field value for the exported file name

Question

I'd like to read a string from a line and then put it into a variable which gets used as a filename afterwards. The string is located in a .csv file at the end of the second line. The first line needs to be removed due to unnecessary headings. Also the ';' used in the old .csv file needs to be replaced by a real comma ','. So far I've got the following piece of PowerShell script:

Powershell -command "(gc file1.csv | select -skip 1) -replace ';', ',' | Foreach-Object{ $_ + ',' } | Out-File file2.csv"

Description of code:

gc file1.csv | select -skip 1 gets the content of the csv file. and removes the first line from the temporary array.

-replace ';', ',' replaces all ';' with ','.

Foreach-Object{ $_ + ',' } adds commas to the end of all lines (which was necessary).

Out-File file2.csv creates an output file named "file2.csv".

The input file is in the following format: (first 2 lines of the .csv)

123.256.862;;0;568.153.135;12;0;;255.253.233

123.633.582;;0;568.588.533;12;0;;255.253.233

The output file generated by the script is the following: (first 2 lines of the .csv)

123.256.862,,0,568.153.135,12,0,,255.253.233,

123.633.582,,0,568.588.533,12,0,,255.253.233,

Next questions rise when programming in PowerShell:

1. How can I program this code into multiple lines for readability?

2. How can I read the last element from the second line and put it into a variable?

3. Is $Out-File $variable & '.csv' correct to generate a new file using this variable as a name? I've tried this method with a dummy variable, but it didn't seem to work. To be more specific: the new filename needs to be 255.253.233.csv.

I've already googled a lot about how to initialize any variable in PowerShell, but it still isn't clear how to exactly declare it afterwards. I hope I'm on the correct path for a solution. Any help is greatly appreciated.

Answer 1

It is not clear what you are doing with the header in the new output file but I wanted to show you about converting your file into a CSV object to make processing easier. This would be especially useful if you plan on doing anything else with this data in PowerShell before you export it.

$path = "c:\temp\file1.csv"
# Drop the header which is apparently useless.
$content = Get-Content $path | Select-Object -Skip 1
# Figure out how many fields we have
$maxHeaderIndex = @([char[]]$content[0] -match ";").Length
# Convert the file into a csv object
$csvData = $content | ConvertFrom-Csv -Delimiter ";" -Header (0..($maxHeaderIndex + 1))
# Write this to file now.
$csvData | Export-Csv -NoTypeInformation -Path "$($csvData[0].$maxHeaderIndex).csv"

I used multiple lines as you seem to favor those for readability. This could easily be done with less lines but if it works it does not matter.

We drop the first line from the file and then convert it into a CSV. Need to make an arbitrary header for this to work so we use numbers. To get the trailing comma we add raise the number of columns by one. PowerShell will create an empty field for each row now. When exported you will now have a trailing comma.

If you didn't want that header in the output that is not hard either. Change the last line above to the following and you would get CSV data without a header.

$csvData | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content "$($csvData[0].$maxHeaderIndex).csv"

Answer 2

You can use import-csv with custom headers (-headers) and for im/exporting with different delimiter use the -delimiter parameter. For saving use export-csv -path $filename (if $filename is already with .csv) or export-csv -path $(".\$filename.csv").

Edit: Maybe post first 2(+) lines of your input file for a improved answer.