Import devices into Spiceworks from a CSV file

tabrez · 2009-01-27T12:15:51.000Z

** 7.5 has brought an updated method to accomplish this. For more information, go to this help doc: http://community.spiceworks.com/help/Import_Inv **

We’ve heard from users who have an inventory of their old systems in an Excel file or even another system. With this script you’ll now be able to import devices into Spiceworks from a csv file.

Be sure to backup your database before you start!!

You’ll need a CSV file with the data you’d like to import. For the first run start with 2-3 entries (so you can test it!). The first line of the file needs to be the name of the columns. An example CSV file is:

Name,Manufacturer,Device Type,Model,IP Address,Serial Number,Location,Operating System
copier1,Apple,Copier,“”,192.168.168.115,“”,Austin,“”
fax1,Acme,Fax,Latitude D830,“”,45322221,Round Rock,Fax Plus Bios
fax2,Acme,Fax,Latitude D830,“”,45322222,Cedar Park,Fax Plus Bios
fax3,Acme,Fax,Latitude D830,“”,45322223,Round Rock,Fax Plus Bios

Step 1: Backup Your Spiceworks Database

Navigate to Settings → Backup Configuration and click “Back up Now”.

Step 2: Copy The Import Script

Using Windows Explorer navigate to C:\Program Files (x86)\Spiceworks\pkg\gems (replace “C:\Program Files (x86)” with the location where Spiceworks is installed)

Browse into the directory spiceworks-x.x.xxxx (choose the latest version you see listed. For eg: at the time I’m writing this how-to the latest version is spiceworks-7.4.00059)

You’ll see a file bulk_import.rb in this directory. Copy this file to C:\Program Files\Spiceworks\bin (again replace Program Files with the location of your install)

Step 3: Prepare your CSV file

Your CSV file is pretty straightforward - the first row should be the column headers (like Name,Manufacturer,Device Type,Model…), and then each subsequent row in the CSV represents a single device.

Check the end of the import script to see which headers are accepted “out of the box” (by default). You can read the import script using any text editor.

If you need more than the defaults you can also add your own custom headers. For any non-default header in your CSV, login to Spiceworks and go to Settings > Advanced & International Options. Under Custom Attributes, add a new attribute for each non-default header. Then, in the CSV add a “C” to the beginning of the header.

Example:
Name,Manufacturer,Device Type,Model,C Closet Name,C Last Service Date,C Leased

Step 4: Open Command Prompt

Open a command prompt window and navigate to the C:\Program Files (x86)\Spiceworks\bin directory (replacing Program Files (x86) with the right location)

Step 5: Run The Bulk Import Script

The usage of the script is as follows:
C:\Program Files\Spiceworks\bin>ruby bulk_import.rb -h

Usage: bulk_import.rb [options] - creates assets in Spiceworks from a CSV file

Options:
-e, --email [EMAIL] email address to log into Spiceworks

-p, --password [PASS]            password for email address

-s, --server [SERVER]            Spiceworks server to import assets to - default 'localhost'

-P, --port [PORT]                the Spiceworks server port  - default '80'

-m, --[no-]manual                manually created attribute flag - default 'true'

-?, --help                       show this message

-V, --[no-]verbose               flag to enable/disable verbose output

-v, --version                    show version

-u, --import over a secure https connection; defaults to port 443

For eg: If you have Spiceworks running on port 9675 and the computer spiceserver with login jsmith@acme.com and your csv file is assetlist.csv your command line will look like:

EXAMPLE
ruby bulk_import.rb -e jsmith@acme.com -p password -s spiceserver -P 9675 assetlist.csv

Change the parameters as appropriate. You’ll see a message for each import like this:

Logging into Spiceworks…
Importing assets…

imported device copier1
imported device fax1
imported device fax2
imported device fax3
goodbye!

Step 6: Verify That Your Data Was Imported

Log into Spiceworks and ensure you see your data in the Inventory.

Once you’re comfortable with the results you can import all your devices using this method. Please test this first with only a few devices. Also remember to backup your database before you start!!

A note on updating devices:
If you’d like to update existing devices then you can add the “–no-manual” flag which tells the script to not create new manual devices. You can update existing attributes like purchase price by using the column name c_purchase_price. See the end of the script for a list of know attributes.

Error Help:
“Error: NoMethodError: undefined method `purchase_date=’ for #<Unknown:0x50d302c” : the header/attribute is not defined as a Custom Attribute, or is not mapping to the column in the database. You can use this report to find the “name” for each of your existing Custom Attributes to help you update your CSV headers properly, so they map to the custom attribute name properly: http://community.spiceworks.com/appcenter/app/shared_report_3485

tonypoupa7612 · 2009-01-28T15:01:37.000Z

Excellent post, well done!
Thanks and keep 'em coming.

justin6761 · 2009-02-03T12:42:40.000Z

Any advice on importing tickets from a CSV file into spiceworks?

tabrez · 2009-02-03T15:18:55.000Z

Yes. Check out the relevant how-to: http://community.spiceworks.com/how_to/show/207

martinlillepuu5515 · 2009-02-17T11:26:52.000Z

cool, just what I was looking

now I can finally batch import my monitors and add network patch panel port numbers and owners to devices

johnv4 · 2009-02-19T22:52:11.000Z

can someone help describe how to batch import my monitors and add network patch panel port numbers and owners to devices…pls and thanks.
Also, I can not get the
-m, --[no-]manual manually created attribute flag - default ‘true’
to work for me. Anyone out there able to be a bit more descriptive please regarding the syntax?
Thank you once again.

johnv4 · 2009-02-19T23:23:40.000Z

oops … to quick on the send keys
how about similarly batching a delete processes?

Can this be done as well?

When using a c_warranty_end field, what is the dating format to be used?
Still not getting to add devices as … not manual…
thanks

johnv4 · 2009-02-20T06:50:30.000Z

last thought of the night.
by default, my OS values include a comma, which is typed in as follows.
Win 2000 Pro ,SP 4.0
I have tried enclosing the expression in " ". ()… no luck.
any thoughts?
The only work around I have found thus far is to painfully analyze the raw data before importing it, finding any commas that are not intended as a field separators and replacing it with something like “///” which I can then search for again and then manually change later on from within the spiceworks interface. Too many step.

martinlillepuu5515 · 2009-02-25T05:34:24.000Z

–no-manual does not seem to work, existing devices are not updated and are added as duplicates.

ruby bulk_import.rb -e email@example.com -p pass123 --no-manual imptest.txt

imptest.txt:

Name,C Department,IP Address,primary_owner_name,Asset Tag,Location,C Patch Panel Port,Description
tktln-lt003,HLD,10.1.1.96,Test User,10481,406,12-16,“”

martinlillepuu5515 · 2009-02-26T04:16:16.000Z

Here’s what I would like to do:

export device names from spiceworks
bulk add location, patch panel port and other fields (eq. in Excel)
import data back to spiceworks, updating each device properties by device name

after looking at bulk_import.rb code, it only adds devices and -m flag controls if the manually_added field is true or false on added device. So please Spiceworks, we still need a proper bulk data update script.

francis-spiceworks · 2009-02-26T10:02:33.000Z

Hi Martin,

You should be able to do what you want:

export device names from spiceworks
=> you do this by creating a new report in Spiceworks that has the device name, the device serial number, and the rest of the attributes you want to edit selected as columns. You then run the reports and export to CSV. If you have added new custom attributes that you want to track, these too can be added to the report.
bulk add location, patch panel port and other fields (eq. in Excel)
=> You can edit a CSV from Excel. Make sure you rename any of the columns that are custom attributes to start with c_. I hope to remove this limitation in the next version of the script.
import data back to spiceworks, updating each device properties by device name
=> You actually need the real serial number to line things up. Devices found by the spiceworks scanner rarely use the device name as the serial number.

Finally, make sure you add the --no-manual flag to the command line when you run this.

I think that I will add a -u flag in future versions of this script such that this script will only update existing devices, because this sounds more like what you want.

martinlillepuu5515 · 2009-02-28T04:16:25.000Z

Thanks for the reply, Francis. I already tried that. Script either created a new device or died with an error on duplicate serial number. So I wrote a little csv->sqldb3 import script in php which updates fields based on a device name: http://www.lillepuu.com/spiceworks/import_sw.phps

Definately not for the faint of heart but it works and I got my fields updated.

lejhorna32297067 · 2009-03-12T10:19:03.000Z

By me having additional columns, would it make it difference if i added it into the script?

victorcavendishges4169 · 2009-03-26T13:45:41.000Z

Hi Francis, this is exactly what i was looking for in order to update information in the Spiceworks inventory (such as Internal asset tags, purchase price, and purchase date).

Where can we get the full listing of the Spiceworks field names?
Also to be used for sql reports and etc…

robert-spiceworks · 2009-06-09T12:19:42.000Z

I have used this method many times for importing of data, and even in repairing a corrupt database and getting some information back into it. It is a great way to get your data into Spiceworks quickly so you can use it.

lisa13097225 · 2009-06-12T17:36:47.000Z

I keep getting an error and wonder if anyone has seen it and knows what I may be doing wrong.

Error: NoMethodError: undefined method ‘owner=’ for #Unknown:0x4150550

‘owner=’ is sometimes another field
0x4150550 changes everytime I try to run it.

Any ideas?

weston3340 · 2009-06-16T14:07:15.000Z

–no-manual didn’t work for me either, however I figured out that the records are tied to serial numbers instead of the name or asset tag as I had thought.

Did what I needed it to do. Thanks!

thommck · 2009-07-06T03:55:39.000Z

I want to do something similar but for License keys. I harvest the keys using the Magic Jellybean Keyfinder. This dumps information into a csv file for each PC on the network.
What would I need to do in this case. Is it even possible?

danjordan · 2009-07-07T11:40:15.000Z

Lisa1309, I got the same message. I had created a SW report on the SW server from which I wanted to export, then I exported it to CSV and then tried to import this file to my other SW server – then I got the error message you mentioned. My solution was to modify my report to only contain the fields mentioned in the 4th paragraph of the instructions (e.g. Name,Manufacturer,Device Type,Model,IP Address,Serial Number,Location,Operating System ). Then the import of that CSV worked just fine.

Dan J

jgolvano · 2009-07-20T19:04:25.000Z

I’m in the porcessing of evaluating Spiceorks for my organization and need to move accross all our assets (currently on an Excel spreadhseet).
I’ve tried using the bulk import script but it fails at the Purchase Date colum with the following error:
“Error: NoMethodError: undefined method `purchase_date=’ for #<Unknown:0x50d302c”
Purchase date in the csv file is in the dd-mm-yyyy format.
What format do I need to use to get it recognized bythe script?