/build/static/layout/Breadcrumb_cap_w.png

Creating a Machine Label from a Spreadsheet List of Computers

This guide will walk you through creating a Machine Label from a spreadsheet containing a list of machines.


Step 1:

Save the spreadsheet containing your list of computers as a CSV (Comma delimited) file.1

 

Step 2:

Method 1: Copy these commands to a .cmd file:

Commands:

@echo off > NEW%1.txt & setLocal enableDELAYedexpansion
<nul set /p =^^(>> NEW%1.txt
for /f "tokens=* delims= " %%a in (%1) do (
set str=%%a
<nul set /p =!str:,=^|!>> NEW%1.txt
)
<nul set /p =)$>> NEW%1.txt

Save this .cmd file to the same directory as your CSV file.  Run the cmd file along with the name of your CSV file from a command prompt. e.g. create a cmd file called "commaToREGEX.cmd" and run:

Command:

c:\commaToREGEX.cmd myCSVFile.csv

This will create a new file called NEW[your csv filename].txt.  Open this txt file - you will use it in Step 4.  Skip to Step 3.

Method 2: Open the file in any text editing software that allows you to search and replace text.  Search for commas and replace them with the pipe "|" symbol which is located on the backslash "\" key.  Add "^(" to the beginning of the list, and ")$" to the end.  The result should look like:

^(PC_1|PC_2|PC_3)$

Leave this file open - you will use it in Step 4.


Step 3:

Open the K1000 WebUI, navigate to Inventory > Computers and click "Create Smart Label." 

Create Smart Label

Change the criteria to: System Name > matches REGEX > PC_LIST
*PC_LIST is a place holder which we will overwrite.  Enter a name for the label and click Create Smart Label.  


Step 4:

Navigate to Home > Label > Smart Labels and select the label you just created.  The last line of SQL should show: 

                 where ((  MACHINE.NAME rlike 'PC_LIST')) 

Copy the text from Step 2.  On the K1000 WebUI, replace PC_LIST (not the single quotes surrounding it) on the last line of SQL with your list resulting in a line like this: 

                 where ((  MACHINE.NAME rlike '^(PC_1|PC_2|PC_3|PC_4|PC_5)$')) 

Click Save.  This label will contain all PCs in the list.  Machines are added to a smart label as they check in, so it will take some time for the label to populate.

 

1 Your spreadsheet must contain the list of machines in a single row, not in a column, to create a proper comma delimited file.  See this page for information on converting a column of data to a row: http://office.microsoft.com/en-us/excel-help/rotate-data-by-converting-columns-to-rows-or-vice-versa-HP005203138.aspx


Comments

  • In this example the resulting Smart Label will contain not only PC_1, but also PC_10, PC_11, PC_12, PC_1001, PC_21, ... etc. How do I format the SQL so only PC_1 is included in the label? - rcubed 11 years ago
    • Thanks for catching that, rcubed. You can surround the REGEX expression with "^()$" to address this issue. e.g. ^(PC_1|PC_2|PC_3)$. I've updated the post to show this information. - PHKace 11 years ago
  • BRILLIANT! Thank you! - awingren 10 years ago
  • Awesome, you saved me tons of work, I needed to change
    @echo off > NEW%1.txt & setLocal enableDELAYedexpansion
    if its separated it would give you an error, it has to be one line - josefino 9 years ago
  • Really useful article. Have used this a few times now and it has been a hit. Thanks. - DRodders 6 years ago
  • I use a powershell script to have the same RegEx output.

    $CSVsource =Import-Csv -Path 'C:\temp\Convertor\CSVData.csv'
    # "^($($CSVsource.Devices -join '|'))$"
    "^(" + $($CSVsource.Devices -join '|') + ")$" | Out-File C:\temp\Convertor\CSVDatatoREGEX.txt - Levio 1 year ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ