Import Custom Fields with SQL

4/26/2018 3135 Contributors

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.

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