Convert CSV file to txt file with REGEX

I would like to extract each cell data in a CSV file and export them to a txt file in a Regular Expression form.
Example:
A- Here is the CSV file
Devices
CITY-24460
CITY-24643
CITY-24768
CITY-24838
CITY-24903
CITY-25061
CITY-25064
CITY-25065
CITY-30221
CITY-30226
CITY-30254
CITY-30318

B- Here is expected output in txt file:
^(CITY-24460|CITY-24643|CITY-24768|CITY-24838|CITY-24903|CITY-25061|CITY-25064|CITY-25065|CITY-30221|CITY-30226|CITY-30254|CITY-30318)$

Any help will be greatly appreciated.

This forum is for scripting questions rather than script requests. We do not write customized and ready to use scripts or solutions on request.

We expect you to make own attempt to solve your problems first.

… and BTW: When you post code or sample data or console output please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.
Thanks in advance

Understood. I will take that in consideration next time.
Thank you.

$CSVsource =Import-Csv -Path ‘C:\Temp\CSVData.csv’
#$outFile = ‘C:\Temp\REGEXData.txt’
$CSVData =
@’
Devices
CITY-24460
CITY-24643
CITY-24768
CITY-24838
CITY-24903
CITY-25061
CITY-25064
CITY-25065
CITY-30221
CITY-30226
CITY-30254
CITY-30318
'@ | ConvertFrom-Csv
#Output in text file with REGEX: ^(CITY-24460|CITY-24643|CITY-24768|CITY-24838|CITY-24903|CITY-25061|CITY-25064|CITY-25065|CITY-30221|CITY-30226|CITY-30254|CITY-30318)$

foreach ($row in $CSVsource) {
$splits = $row -split ‘,’

$Output = @"
^($row|$row+1)$

"@

$Output | Out-File -Path 'C:\Temp\REGEXData.txt' -Append 

}

Honestly I tried to script but it failed.

Please … when you post code or sample data or console output please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.
Thanks in advance

:thinking:

And now you gave up or what? :smirk:

When you import your CSV file with …

$CSVsource =Import-Csv -Path 'C:\Temp\CSVData.csv' 

What happens when you simply output …

$CSVsource.Devices

?

Now you should read

and

Please read it completely including the examples! … you’re almost there!! :wink:

Output
I got that result.

I haven’t give up.

#Desirable output
#Output in text file with REGEX: ^(CITY-24460|CITY-24643|CITY-24768|CITY-24838|CITY-24903|CITY-25061|CITY-25064|CITY-25065|CITY-30221|CITY-30226|CITY-30254|CITY-30318)$
$CSVsource =Import-Csv -Path 'C:\Temp\CSVData.csv'
#$outFile = 'C:\Temp\REGEXData.txt'

For ($i=0; $i -lt ($CSVsource.count); $i++) {
foreach ($row in $CSVsource) {
   
   $CSVsource.Devices += @("$_[$i] -replace '|')")

}
}

Great. :+1:t4:

Brilliant. :+1:t4:

Did you read the help topic I posted?

Just read it and I am making some progress. :slight_smile:

$CSVsource =Import-Csv -Path 'C:\Temp\CSVData.csv'
(-split $CSVsource.Devices) -join "|"

The result is: CITY-24460|CITY-24643|CITY-24768|CITY-24838|CITY-24903|CITY-25061|CITY-25064|CITY-25065|CITY-30221|CITY-30226|CITY-30254|CITY-30318
Now I need to figure out both ends: Beginning ^( and ending )$

1 Like

Great. That’s how I like it. :+1:t4: :+1:t4: :grinning:

The last part is easy. You don’t need the split …
You could do it this way …

"^(" + $($CSVsource.Devices -join '|') + ")$"

… or even a little shorter but harder to read …

"^($($CSVsource.Devices -join '|'))$"
1 Like


I am getting the desirable output, but it gives me error about the csv file path.
I have also added the parentheses (highlighted yellow).
I think I learned one BIG thing today… about split command. :slight_smile:
Thank you Olaf

So you should check if the file is really where you expect it. I cannot see your environment. :wink:

Yes … I realized that I forgot them in the first place seconds after I posted my answer … I already corrected my code sugesstion above. :wink:

:rofl: :rofl: :rofl:

… AND you didn’t even need REGEX for all this. :wink:

1 Like

Yep! No REGEX needed :sweat_smile:
Again thank you Olaf!

This is the final version;

$CSVsource =Import-Csv -Path 'C:\Temp\CSVData.csv'
# "^($($CSVsource.Devices -join '|'))$"
"^(" + $($CSVsource.Devices -join '|') + ")$" |  Out-File C:\Temp\CSVDatatoREGEX.txt
1 Like

Thanks for sharing. :+1:t4: :slightly_smiling_face: