CODEWITHSUNDEEP
powershell
Empty Coder
Empty Coder

Reputation: 589

create excel sheetname based on servicname

Below are my 4 csv files which i am merging using Export-Excel" cmdlet.

C:\Script\sum_prod-ild-ne-as1-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__eKommerceExtensions__eKommerceExtensions.csv
C:\Script\sum_prod-ild-ne-as1-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__T1T2_Reweighting__T1T2_ReweightingExtensions.csv
C:\Script\sum_prod-ild-ne-as2-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__eKommerceExtensions__eKommerceExtensions.csv
C:\Script\sum_prod-ild-ne-as2-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__T1T2_Reweighting__T1T2_ReweightingExtensions.csv

if you notice here, there are 2 files for same server (prod-ild-ne-as1,prod-ild-ne-as2) because the next part of the filename is servicename (e.g. Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__eKommerceExtensions__eKommerceExtensions).

The sheet name should be based on servicename so that all the server data for one service will be at one sheet and so on. So before i written a code which was working fine when the service name was small (e.g. check_cpu, check_eth0)

foreach ($csv in $csvs)
{
    $Content = @()

    $name = $csv

    $pos = $name.IndexOf("-")
    $leftPart = $name.Substring(0, $pos)
    $rightPart = $name.Substring($pos+1)

    $pos = $rightPart.IndexOf(".")
    $leftPart = $rightPart.Substring(0, $pos)

    $leftPart


    $Content = Import-Csv -Path $csv
    $TimeNow = Get-Date    
    $Current_Time = $TimeNow.ToUniversalTime().ToString("yyyyMMdd-HHmm")

    $Content | Export-Excel -Path "$scriptPath\Combind_Data_$Current_Time.xlsx" -WorksheetName $leftPart -Append

But in this can as the servicename is very long, excel is not allowing to create sheets with service name as the limit is 31 char.

Please tell me in this case how to create sheets with servicename keeping it in limit.

Upvotes: 0

Views: 46

Answers (1)

Theo
Theo

Reputation: 61068

An Excel worksheetname is not only limited by its length, but also there are characters you cannot use.

  • The name must be unique within a single workbook.

  • A worksheet name cannot exceed 31 characters.

  • You can use all alphanumeric characters but not the following special characters: \ / * ? : [ ]

  • You can use spaces, underscores (_) and periods (.) in the name as word separators.

Taking that into account, this is a job for regex:

$csvFiles = 'C:\Script\sum_prod-ild-ne-as1-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__eKommerceExtensions__eKommerceExtensions.csv',
            'C:\Script\sum_prod-ild-ne-as1-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__T1T2_Reweighting__T1T2_ReweightingExtensions.csv',
            'C:\Script\sum_prod-ild-ne-as2-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__eKommerceExtensions__eKommerceExtensions.csv',
            'C:\Script\sum_prod-ild-ne-as2-Proc_mem-check_extender-__ldmodels__Demo_TakeHome_HH_P__T1T2_Reweighting__T1T2_ReweightingExtensions.csv'

$today = (Get-Date).ToUniversalTime()
foreach ($csvPath in $csvFiles) {
    # combine the distinctice part of the server name (a1 or a2) with the distinctive
    # part of the service name (i.e. the stuff between the last underscore and the .csv extension)
    # take out all invalid characters and remove doubled underscores
    $sheetName = $csvPath -replace '^.+-(as[12])-.+_(\w+)\.csv', '$1_$2' -replace '[\\/*?:[\]]+', '_'
    # make sure the sheetname does not exceed the 31 character limit
    if ($sheetName.Length -gt 31) { $sheetName = $sheetName.Substring(0, 31) }

    $target = Join-Path -Path $PSScriptRoot -ChildPath ('Combind_Data_{0:yyyyMMdd-HHmm}.xlsx' -f $today)

    Import-Csv -Path $csvPath | Export-Excel -Path $target -WorksheetName $sheetName -Append
}

Using the above filename examples, the sheets will be named:

as1_eKommerceExtensions
as1_ReweightingExtensions
as2_eKommerceExtensions
as2_ReweightingExtensions

Regex details:

           
^              Assert position at the beginning of the string
.              Match any single character that is not a line break character
   +           Between one and unlimited times, as many times as possible, giving back as needed (greedy)
-              Match the character “-” literally
(              Match the regular expression below and capture its match into backreference number 1
   as          Match the characters “as” literally
   [12]        Match a single character present in the list “12”
)             
-              Match the character “-” literally
.              Match any single character that is not a line break character
   +           Between one and unlimited times, as many times as possible, giving back as needed (greedy)
_              Match the character “_” literally
(              Match the regular expression below and capture its match into backreference number 2
   \w          Match a single character that is a “word character” (letters, digits, etc.)
      +        Between one and unlimited times, as many times as possible, giving back as needed (greedy)
)             
\.             Match the character “.” literally
csv            Match the characters “csv” literally

Upvotes: 0

Related Questions