Working with CSV - DataSplits

Question

I have a script written that pulls a number of CSV files from an FTP server and downloads to a network location.

The content of this CSV file follows the example I have provided in this link File Example

In short working with this file I need to:

• Using the 12 characters (alpha-numeric) which follow Ords: on line two define a variable which will used later in a query. (A)

  GB0000000001

  Would become

  $OrderVariable = "GB0000000001"
  

  I have read about

  .TrimStart([Characters_to_remove])
  

  but am unsure how it would skip the first row and then how I would remove everything following the next 12 letters.

• Using the entire line two information excluding the Ords: define this as a variable e.g.

  GB0000000001 – Promotion Event

  would become

  $TitleEvent = "GB0000000001 – Promotion Event"
  

The CSV contains all the customers that an email needs to be sent to e.g.

D|300123123|BBA
D|300321312|DDS
D|A0123950|BBA 
D|A0999950|ZZG

These items I would expect to be written into a hashtable which I thought would be simple enough except I cannot find any way to exclude everything which precedes it.

$mytable   = Import-Csv -Path $filePath -Header D,Client,Suffix
$HashTable = @{}
foreach ($r in $mytable) {
    $HashTable[$r.Client] = $r.Data
}

UPDATE

I have managed to get most of this element into a variable with the following

$target = "\\Messaging"

cd $target
$Clients = Import-Csv example.txt | where {$_ -like "*D|*"} 

$Clients = $Clients[1..($Clients.count - 1)]
$Clients | Export-Csv "Test.csv" -NoTypeInformation

But I cannot get it to import with custom headers or without the first "H|" delimitation...

End of update 1

I believe this is roughly what is going to be required as the only element that I will need to define and use in a later query is the Client themselves.

• The next would define all the text that remains as the message content

  This is a Promotion Event and action needs to be taken by you. The

  deadline for your instruction is 2pm on 12 September 2016.

  The deadline for this event has been extended.

  To notify us of your instruction you can send a secure message.

  This can differ on each occasion massively so cannot simply be a removal of X numbers of lines the content will always follow the Ords: (line two) and End at the start for the D| delimitation.

Most of the other code I need to put together I am 'fairly confident' with (famous last words) and have a fully working script that is pulling the files I need, I am just not great at working with .csv's when I have them.

Answer 1

The data format is flexible without a global table/grid structure so let's use regexps (the breakdown), which is quite a universal method of parsing such texts.

$text = [IO.File]::ReadAllText('inputfile.txt', [Text.Encoding]::UTF8)

$data = ([regex]('ORDS: (?<order>.+?) [-–—] (?<title>.+)[\r\n]+' +
                 '(?<info>[\s\S]+?)[\r\n]+' +
                 '(?<clients>D\|[\s\S]+?)[\r\n]+' +
                 'T\|(?<T>\d+)')
    ).Matches($text) |
    forEach {
        $g = $_.groups
        @{
            order = $g['order'].value
            info = $g['info'].value -join ' '
            clients = $g['clients'].value -split '[\r\n]+' |
                where { $_ -match 'D\|(.+?)\|(.+)' } |
                forEach {
                    @{
                        id = $matches[1]
                        suffix = $matches[2]
                    }
                }
            T = $g['T']
        }
    }

$data is now a record (or an array of records if the file has multiple entries):

Name                           Value
----                           -----
T                              000004
info                           This is a Promotion Event and action needs to be take...
order                          GB0000000001
clients                        {System.Collections.Hashtable, System.Collections.Has...

$data.clients is an array of records:

Name                           Value
----                           -----
id                             300123123
suffix                         BBA
id                             300321312
suffix                         DDS
id                             A0123950
suffix                         BBA
id                             A0999950
suffix                         ZZG