Add character to column from imported csv

1

I have a script where I need to modify one section to identify if a specific column has a semi-colon as the last character and if not, add it. I think the action I need to do should be between these 2 lines, otherwise it truncates the last item by 1 character if the semi-colon is missing.

$option = [system.stringsplitoptions]::RemoveEmptyEntries
$Regions = $RegionsArray.split($separator, $option)

I am thinking to use something like $Regions.regions[-1] -notmatch, ";" to identify the ones that need to have that last semi-colon added. Not sure what the best way is to add it after that though. Below you can see the section that builds the array of unique regions. Any help would be greatly appreciated.

Foreach ($c in $Countries)
				{
					$c
					$RegionsArray = $Events | where{ ($_.Country -like $c) -and ($_.Region -notlike "") -and ($_.Region -notlike "All*") } | select -expandproperty Region
					
					If ($RegionsArray)
					{
						$RegionsArray = $RegionsArray.Trim()
						$RegionsArray = $RegionsArray -replace ".$"
						$separator = ";"
						$option = [system.stringsplitoptions]::RemoveEmptyEntries
						$Regions = $RegionsArray.split($separator, $option)
						
						foreach ($r in $Regions)
						{
							$Region = $r.Trim()
							
							If ($UniqueRegions -notcontains $Region)
							{
								$UniqueRegions += $Region
							}
						}
2

Hi. I’d probably just do a regex replace of the last character if it is not a semicolon

$Regions.regions -replace '[^;]$','$0;'

You could also use a negative lookbehind

$Regions.regions -replace '(?<!;)$',';'

However, based on your code it, it seems you are doing a lot of string acrobatics. Maybe you could back up and show us what you’re starting with and what you end up with and not have to do any or as many string operations.

1 Like
3

I can’t just do that unfortunately. The last character is part of the region name. So that column should look like this example below. When it does the script works correctly and creates the arrays that are needed for each individual region. When the last semi-colon is missing, it strips that last character off which in this example would result in an output file with the name 2025 Victori Holidays instead of 2025 Victoria Holidays. If only one row in the array has the missing semi-colon we end up with 2 output files, one with the incorrect spelling and one with the correct spelling. So it’s not as simple as just replacing that final character.

Western Australia; South Australia; New South Wales; Queensland; Victoria;

4

Can’t you just make this:

$RegionsArray = $RegionsArray -replace ".$"

this instead:

$RegionsArray = $RegionsArray -replace ";$"

So that instead of replacing any single character at the end of the string, you only replace a semi-colon? If the last character isn’t a semi-colon, nothing will be replaced.

5

The last character must be a semi-colon. The problem is when it isn’t a semi-colon and the last character of the last region in the column is stripped out. Example.

It should be (using the Australia regions for the example):

Western Australia; South Australia; Queensland; Victoria; New South Wales**;**

The data in the array created by this action feeds the title of the .ics files that are being created i.e.

2025 Western Australia Holidays.ics
2025 New South Wales Holidays.ics

When the last semi-colon is missing, then we get an additional file created that only has a single event in it with a name like:

2025 New South Wale Holidays.ics

6

That’s a good idea but it’s backwards, she wants there to be a semicolon at the end. Either way, I still think the OP should take a big step backwards and share the actual problem she’s trying to solve with his code as I think there may be much easier ways to get the desired result.

1 Like
7

I am the OP (it’s she btw) and I shared the actual problem I am trying to resolve. I am no longer the one using the script and providing the input files. So now white spaces and lack of semi-colons are appearing in the input files this script runs against. I have addressed the white space issues.

I honestly don’t know how much clearer I can be about the issue but I will try. The csv files are company holiday files for offices in different countries. Some countries have multiple regions where the holidays differ from other regions in the same country. For the countries that have regions, an .ics file is created for each individual region. The regions are listed in a column called ‘Region’, in the csv file. The regions are separated by a semi-colon and there must be a semi-colon after the last region listed.

The script pulls all the information from the csv file and creates several arrays, one of which is regions. Then a foreach runs for each region in the regions array and creates the ics file with all the holidays that match that region.

If there is NO semi-colon at the end of the list of regions in the region column, the last region is added to the array without the last character, so if the region is Victoria, it is added as Victori and an ics file containing only the holidays where the region is missing the semi-colon. That file is invalid and we don’t want it.

We want to handle this particular issue in the script by looking at each row in the event array and ensuring that the last character in the region property is a semi-colon. What I am looking for is the best way to do this. I already know that I can use the notmatch comparison operator against the last character in the $r.region to determine if it’s a semi-colon or not, I just need to determine the best way to add it if it’s not.

8

What’s wrong with the suggested solutions?

9

That’s the bit I’m not getting. Why must there be a semi-colon when you’re removing the last character with this line:

$RegionsArray = $RegionsArray -replace ".$"

That’s the line that removes the last character when it isn’t a semi-colon because . matches any character. Hence my suggestion of just matching the ‘;’.

If you really do need to add it, then Doug’s solution is perfect.

Add this before your first If statement.

$RegionsArray = $RegionsArray -replace '[^;]$','$0;'
2 Likes
10

Thanks Matt. Either of the examples I provided should achieved the desired result. Though, you won’t know if the last character is a semicolon or not, but that’s not really important if I’ve understood correctly.

Here is sample code for both examples.

Sample data

$arrayofstrings = 'some example; with ending semicolon;',
                  'another example; but without; ending semicolon',
                  'yet another; example with; ending semicolon;',
                  'final example without'

Example 1

$arrayofstrings -replace '[^;]$','$0;'

some example; with ending semicolon;
another example; but without; ending semicolon;
yet another; example with; ending semicolon;
final example without;

Example 2

$arrayofstrings -replace '(?<!;)$',';'

some example; with ending semicolon;
another example; but without; ending semicolon;
yet another; example with; ending semicolon;
final example without;
1 Like
11

Sorry, very long day and frustrated with another issue. Currently the csv files are created by downloading the items from a SharePoint list into Excel and then saving as a csv. The semi-colon is needed in the regions column in the SharePoint list because of how the items are displayed in a Power App.

Rather than asking the people who are downloading these files to go through possibly hundreds of rows to fix that issue by either adding or removing that semi-colon manually, which isn’t going to happen, I am handling it in the script.

I do appreciate all of the help and will be testing out the provided solutions.

Please forgive my lack of patience on this, as I said, frustrating day.

12

Thanks for the help. To answer the question, there must be a semi-colon because of the way the data comes to the script. The -replace “.$” was put in place to remove the final semi-colon. If it’s missing however it removes the last character of the Region name causing all sorts of other issues.

I find now that I also need to check the array for commas and if they exist to replace them with a semi-colon as well. How would I incorporate that properly into the solution you have already provided?

13

Can you provide sample data showing where the the comma might appear?

Would there be any situations where the comma should not be replaced, e.g. in a region name?

14

Here is an example of the Regions column in the data that we receive. I copied this directly from the csv file for Australia. The Regions column is the only one with delimited data and the only one that would have a comma in it. As you have probably figured out I am only barely familiar with Regex, so your help is greatly appreciated.

New South Wales; Victoria, Queensland; South Australia; Western Australia;
Queensland;
Western Australia
New South Wales; Victoria; South Australia;
Queensland;
Western Australia;
New South Wales; Queensland; South Australia

15

I think for the example data, you can simply chain the replace operators:

$RegionsArray = @(
    'New South Wales; Victoria, Queensland; South Australia; Western Australia;'
    'Queensland;'
    'Western Australia'
    'New South Wales; Victoria; South Australia;'
    'Queensland;'
    'Western Australia;'
    'New South Wales; Queensland; South Australia'
)

$RegionsArray = $RegionsArray -replace '[^;]$','$0;' -replace ',',';'
$RegionsArray

# Output

New South Wales; Victoria; Queensland; South Australia; Western Australia;
Queensland;
Western Australia;
New South Wales; Victoria; South Australia;
Queensland;
Western Australia;
New South Wales; Queensland; South Australia;

Edit: something else you might want to consider is maintaining a list of valid regions (maybe in a text file or config. file so you only have to update them in one place). The list can be imported at the start of your script and you can check against the list when processing the region. If it’s invalid for any reason you can handle that gracefully rather than generating unwanted files:

$ValidRegionList = Get-Content .\allRegions.txt

foreach ($Region in $UniqueRegions) {
    if ($Region -notin $ValidRegionList) {
        Write-Warning 'Unknown region; region will be skipped.'
    }
    else {
        # do some stuff for the region.
    }
}
1 Like
16

That worked beautifully. Thank you so much for your help. Forgive my initial frustration please.

17

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.