Import Custom Fields with SQL

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