Purpose:
This article has been superseded with the introduction of the Custom Fields Import Wizard. For instructions on how to use the Custom Fields Import Wizard, please see the following: Import Custom Fields In PDQ Inventory Using The Custom Fields Import Wizard
You wish to import custom fields from a file, such as a CSV file of asset tag numbers.
Resolution:
There are a number of ways to accomplish this task, and this is just one example.
Step 1 - Create CSV file
Create a CSV file containing two columns, the name of the computer followed by the value to import into the custom field. For example:
COMPUTER1,Asset tag 5
COMPUTER2,Asset tag 7
When entering dates and times, use a format that is recognizable on your computer. To ensure compatibility between different locales, you can use a standard date format of "YYYY-MM-DD HH:MM:SS".
COMPUTER1,2015-01-05 04:14:32
COMPTUER2,2015-12-31 00:00:00
Step 2 - Find the ID of the custom field
Open the SQLite console using Preferences > Database > SQLite Console. Execute the following SQL (replace Asset Tag with the name of the custom field):
select CustomComputerItemId from CustomComputerItems where Name like 'Asset Tag';
The number value you see is the number you will use later. If you have only one custom field, the number will most likely be 1.
Step 3 - Import the CSV file from step 1
While still in the SQLite console, create a temporary table to hold the data with the following SQL:
create temp table CustomImport (name, value);
Import the CSV file with the following two commands:
.mode csv
.import C:/AssetTags.txt CustomImport
** Note that the SQLite console uses forward instead of back slashes in the path. **
Step 4 - Update the computers with the asset tags.
Execute the following SQL to attach the asset tags to the individual computers. Replace the 1 with the ID number from step 2.
insert into CustomComputerValues (CustomComputerItemId, ComputerId, Value)
select 1, ComputerId, Value from CustomImport, Computers
where CustomImport.Name like Computers.Name;
See Also:
Import Custom Fields In PDQ Inventory Using The Custom Fields Import Wizard
Comments
1 comment
I used this successfully, with no useful SQL knowledge. Only change I made was changing 'Asset Tag' to "Warranty Expiration Date" to match my custom field name.
Thanks for the instructions!
Article is closed for comments.