Bdtjtk

I am trying to import a CSV file. It has some random data on top and I cannot skip the lines as I am not sure where the random data ends. There will be 2 empty lines and after that, there are headers and the desired data.

Lets say the random data looks like this
"AD Group","USER ID","Property",
"NTDOMAINDoctors","sattar""Enabled"
"NTDOMAINDoctors","shayla""Disabled"
"NTDOMAInNurses","mjane","Enabled"

"AD Group","GivenName", "ObjectClass","SURname","Name" ...morecolumns

"NTDOMAINUsers","user1","user","User","1"
"NTDOMAINUsers2","User1","User","User1_1","2"

How can I import the data that is under the headers (GivenName, SURname, Name). Also I just want to select some columns?

Provided the header line is fixed and can be used as a separator,

I'd use a RegEx with a positive lookahead :

$Header = "Name,Phone_Number,City"

$CsvData = (Get-Content '.inputfile.csv' -raw) -Replace "(?s).*?(?=$Header)" | ConvertFrom-Csv

$CsvData

Sample output based on above data (assuming a comma separated file) :

Name Phone_Number City

---- ------------ ----

Nick 800-524-8763 NewYork

Mike 112-223-2222 Richmond

## Read the file into an array, line-terminated

$f = [System.Io.File]::GetAllLines('path-to-csv')



## Stage the array to receive the filtered results

$clean = @()



## Loop through each entry in the file

for ($i = 0; $i -lt $f.count; $i++) {



   ## When the CSV header is found, filter the results and exit

   if ($f[$i] -eq "Name Phone_Number City") {

      $clean = $f[($i..($f.count - 1))]

      break

   }

}



## Turn the string array into and object that can be used in PowerShellese

$clean = $clean | convertfrom-csv

Something like this is a pattern you can test with using here strings as input rather than a large file. Very similar to answer from thepip3r. Also, it appears your input is not using Comma as delimiter, so specified Space.

$csvtext = @"

A B  C

w DD q

u o  p

p u  o

Name Phone_Number City

Nick 800-524-8763 NewYork

Mike 112-223-2222 Richmond

"@



# replace with Get-Content of your file...

# $data = Get-Content ...

$data = $csvtext -split "`r`n"



$i = 0;

foreach($line in $data)

{

    if ($line.StartsWith("Name Phone_Number City")) { break; }

    $i++

}



$subdata = $data[$i..($data.Count-1)]



$csv = $subdata | ConvertFrom-Csv -Delimiter ' '



$csv