1

This How-To shows how to use a script to import purchases into Spiceworks (version 5.0.60009 or later).

Be sure to backup your database before you start!

You’ll need a CSV file with the purchases you’d like to import. For the first import, start with a small file of 2-3 lines so you can test your CSV.

The first line of the CSV needs to be the names of the columns, for example:

name,quantity,price,created_at,part_number,purchase_order,charge_to
Toner,2,45.95,2010-11-01,123-45-6789,IT-639,IT
Laptop,1,1499.99,2010-10-31,987-654-321,E-639,Executive
Keyboard,1,25.95,2010-11-01,K12312,S-639,Sales

Note: The CSV file must include at least the “name” column in order to create purchases.

The following columns are available:
name
quantity
price
purchased - 0 or 1
purchased_at - Date
ticket_id
created_at - Date
received - 0 or 1
received_at - Date
approved - 0 or 1
approved_at - Date
charge_to
vendor - name of vendor (IT Service) that already exists in Spiceworks
order_number - Vendor’s Order Number
part_number
purchase_order - PO number
notes
shipping_code - Shipper tracking number
purchased_for_name - name of device, user, etc. that this item was purchased for (should already exist in Spiceworks)

Step 1: Backup Your Spiceworks Database

Picture_5.png

Go to Settings → Backup Configuration.
Then click the “Backup Now” button.

Step 2: Copy The Import Script

script_location.png

Using Windows Explorer navigate to C:\Program Files\Spiceworks\pkg\gems (replace Program Files 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-5.0.60009)

You’ll see a file bulk_purchase_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: Open Command Prompt

40cdf44595278d2b042e7430d6d06edd64cd2588dc7352f224bc9110e52d627e_Picture_8.png

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

Step 4: Run The Bulk Import Script

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

Usage: bulk_purchase_import.rb [options]

  • creates purchases 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 purchases to
    - default ‘localhost’
    -P, --port [PORT] the Spiceworks server port
    - default ‘80’
    -c, --encoding [ENCODING] Encoding of CSV file
    - default UTF-8 or UTF-8 (detected)
    -d, --cdw CSV file is a CDW export
    -r, --received Mark purchases as received on the same date they are marked as purchased
    -o, --override Import CDW entry even if duplicate entry is found on the same date
    -?, --help show this message
    -V, --[no-]verbose flag to enable/disable verbose output
    -v, --version show version

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

ruby bulk_purchase_import.rb -e jsmith@acme.com -p password -s spiceserver -P 9675 purchaselist.csv

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

Logging into Spiceworks…
Importing purchases…

  • imported purchase Toner
  • imported purchase Laptop
  • imported purchase Keyboard

Import complete
3 purchase entries processed: 3 imported, 0 duplicates ignored, 0 errors

goodbye!

Step 5: Verify That Your Data Was Imported

results.png
results.png728×192 20.1 KB

Log into Spiceworks and ensure you see your data in Spiceworks

Once you’re comfortable with the results you can import all your purchases using this method. Please test this first with only a few purchases to check the format of the CSV file.

Also remember to backup your database before you start!!

35 Spice ups
2

It will be implemented in to GUI in the next version? (Select the file, import)
Congratulations abou this feature :slight_smile:

3

Very nice how-to, thanks!

4

Nice, works like a charm. Now I can import all my purchases at the end of the month, instead of everyday. (Due to the inability to change the date) We only keep track of three or four purchase items on Spiceworks, so we’re inputting an average of 10-15 purchases a month.

If it becomes incorporated somehow in the GUI, it would be even more awesome.

5

Nice! Thanks Eric, I’ll have to incorporate this into my SpiceU classes.

6

Thanks for the how-to eric. This works best when combined with the inventory pool post you made using purchase lists. We buy large quantities of items… (for example, 40 desktop ups batteries) and were disappointed that you had to enter each individually to track still. Importing all 40 from a spreadsheet is a breeze!

7

Any plans to update this so you can include categories, sub-categories and custom attributes?

8

OK Can some tell me why I keep getting the
Error: bad user ‘email@email.com’ or password for server ‘localhost.’ on port 88

Error. I have tried 4 different users plus created a new user just for this to see if I had something else wrong and get the same error.

9

Is this still the best way to import purchases? This was written in 2010. I found this newer article but the link for importing is still referencing this thread.

https://community.spiceworks.com/help/Importing_Data