1

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

Be sure to backup your database before you start!

You’ll need to export your CDW purchases from their extranet site into a CSV file. For the first import, start with a small file of 2-3 lines so you can test your CSV.

Step 1: Backup Your Spiceworks Database

Picture_5.png

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

Step 2: 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 3: Download your CDW purchases

5-cdw_red.png

You can export your CDW purchases using the CDW extranet:

  1. Login to the CDW extranet
  2. Go to your Order Histrory
  3. Run a report (filter as needed - start with 2 or 3 items the first time).
  4. Download as CSV

Step 4: 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 5: Run The Bulk Import Script using the “-d” option

To import CDW data, be sure to specify the “-d” option.

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 cdw_purchaselist.csv your command line will look like:

ruby bulk_purchase_import.rb -e jsmith@acme.com -p password -d -s spiceserver -P 9675 cdw_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 6: 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 in your CSV file.

Remember to use the -d option when importing.

Also remember to backup your database before you start!!

12 Spice ups
2

Nice, I’ll have to try this one

3

Thanks. Was justing getting to this need for setup, so will use and comment back!

4

This is really cool. Now it would be cooler to automate this by just entering your CDW credentials into spiceworks.

5

Works perfect, I was able to create my own CSV and import from multiple vendors. Thanks for the tip.

6

Are CDW reports still produced in the format this is expecting? Running Spiceworks 6.2 and the CDW Export / Import columns aren’t lining up.

7

I had to rewrite the bulk_purchase_import.rb files to match the new CDW format before mine would work

8

Jake1081, can you post your revised script? The stock script in 6.2 is giving an “Invalid column(s) in csv” error on a CDW exported csv.

9

Love it!
Import complete
792 purchase entries processed: 792 imported, 0 duplicates ignored, 0 errors
goodbye!
Thanks Eric!