0

I want to search the register of associations in France for Chinese organizations. Since I cannot install anything on my office computer, I built the following MS powershell search query based on another post in this forum:

$file = "import.csv"
$csv = Get-Content $file
$csv | Select-String 'chinois' | Out-File "output.csv"

Unfortunately, the output I get has line breaks where there should not be any. Can anyone point out a better way to do this? Ideally, it would also include the option to search for multiple strings at once, combined with a logical OR. Being a first-time user of powershell, I highly appreciate any comment or idea!

  • 2
    Are those line breaks in the input as wel? Select-String allows use of regex so searching for multiple strings is just a regex away. – Lieven Keersmaekers May 3 '18 at 9:56
  • I'm seeing that this might actually be a problem of excel which I am using to open the exported file: even if I set the same delimiter (semicolon), the output file will only have 17 columns instead of the former 24 columns. Line breaks shouldn't have any effect anyways, right? Thanks for the hint to regex! – dufei May 3 '18 at 10:21
  • Read this Avoid line breaks when using out-file – LotPings May 3 '18 at 11:01
0
  • Handling of csv as text won't preserve the header, do it manually by copying 1st line.
  • To exclude the search pattern as part of a longer word enclose it in \b
  • The or in a RegEx is denoted by a vertical bar |

## Q:\Test\2018\05\03\SU_1319374.ps1
$InFile = ".\rna_import_20180403.csv"
$OutFile = ".\output.csv"
$pattern = "\bchinois\b|\bassociation\b"  # to exclude chinoise etc.

Get-Content $InFile | Select -First 1 >$OutFile
Get-Content $InFile | Select-String $Pattern | Add-Content $OutFile

Sample output 2 of 962 lines from above $InFile

id;id_ex;siret;gestion;date_creat;date_publi;nature;groupement;titre;objet;objet_social1;objet_social2;adr1;adr2;adr3;adrs_codepostal;libcom;adrs_codeinsee;dir_civilite;telephone;siteweb;email;observation;position;rup_mi;maj_time
751P00052945;00052945;"";751P;1979-11-06;0001-01-01;D;S;CENTRE DE DOCUMENTATION SUR LE CINEMA CHINOIS;développer toutes les activités tendant a faire connaître le cinemachinois en france;006025;000000;"";71        RUE Galande;"";75005;PARIS;75105;PM;"";"";"";W751052945;R;"";2014-10-06 10:29:39
751P00185382;00185382;"";751P;2008-02-21;2008-03-15;D;S;AFAI ASSOCIATION FRANCAISE DES ARTISTES INTENATIONAUX;promouvoir la culture chinoise a paris et les échanges culturels avec les artistes chinois et internationaux;006000;000000;"";4         RUE Georges berger;"";75017;PARIS;75117;PM;"";"";"";"";A;"";2009-05-25 05:00:00
  • This seems to do the trick, thank you so much! I deleted "\bassociation\b" though since I do not want any entries that only contain this word. – dufei May 3 '18 at 15:50
  • That was only a sample for the or condtion it matches chinois or association as a word not part of another word . – LotPings May 3 '18 at 15:54
1

Instead of using Get-Content consider using Import-CSV and Where-Object in order to read and filter the file. Afterwards you can use Export-CSV to export the file.

It's probably going to look similar to this:

$inputCsv = Import-Csv -Delimiter ';' -Path "file.csv"
$inputCsv = $inputCsv | Where-Object {$_.Attribute -eq 'x' -or $_.Attribute -eq 'y'}
Export-Csv -Path 'output.csv' -InputObject $inputCsv

You can use Get-Help and the shorthand -? to get help on the PowerShell CLI. So for example you could run Export-Csv -? to show the help for Export-Csv.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.