Split CSV File Issue

0
votes

I currently have a CSV file that is generated every week, in which I use it to tell what servers have certain software installed. I want to write a script to automate this process, but I'm getting stuck when trying to split the file.

My file has 8 lines of documentation, and the real data doesn't start until line 9. Next, each row of data has 15 columns that I want separated into $temp[0], ..., $temp[14]. The total file is around 3,800+ lines long. My issue is, however, that my data has commas in it which are used to format dates, cells with multiple items (ie. "CE, _Linux, Windows"), etc. So what I'm running into is that I can't use $example.Split(","), as it separates each item by comma, when I really want it separated by column of my CSV file.

Any idea on how to go about this?

2
arrayspowershellpowershell-2.0comma
You should really look into PowerShell Import-Csv cmdlet. It has the -delimiter parameter also (in which you can specify a comma or any other delimiter). Just like Import-Csv -path \\YourCsvFilePathHere\CsvFile.csv -delimiter , - Vivek Kumar Singh
If the data fields have commas in them and the fields are not being quoted properly then the file is not following the standard and is not actually a valid CSV. In that case you should go back to whatever process is generating the file and find a way to have it generate valid CSV files. - EBGreen

2 Answers

0
votes

Like @vivek-kumar-singh mentioned, you can import your csv into an object via Import-Csv

Import-Csv -Path <pathToFile> -Delimiter ','

Following you can iterarte trough your object and adress certain lines (e.g. $import[0]) To adress a certain column of your csv in the Object just do something like $import.columnHeader[0]

0
votes

I had almost the same problem, this worked for me:

Import-CSV $Report -Delimiter ':'   | Select-Object * |  Export-CSV $Report2 -Encoding utf8 -Delimiter ':' -NoTypeInformation 
$Quotes = get-content -path $Report2
$Quotes | foreach {$_ -replace "`"", ""} | Set-Content $Report2

This parses the line and removes the double quotes. Hope works for you!