Importing Data From SQL Server Into SkuBrain

If you’re using SkuBrain with an ERP or inventory management system that we don’t yet have an integration plug-in for, you’ll need to be able to export the data from your system to CSV (comma separated value) format before it can be imported into SkuBrain.

In this article I’m going to explain how to export data from a Microsoft SQL Server database, tidy this up a bit in Excel and finally to save it to a CSV file that can be imported into SkuBrain. Obviously I don’t want to use the data from a real company, but I do want the data to be realistic… so I’m going to use Microsoft’s Adventure Works 2012 database. This is quite a good database to use since it has around 120,000 sales spanning around 5 years (a good amount of data for forecasting).

Selecting the data

If you’re extracting data directly from SQL server, there’s no avoiding it – you must either be a nerd or know someone who is – since you’re going to have to write some SQL code to get at your data… I won’t waste time on the details of this script since it is specific to the Adventure Works 2012 database, but for completeness (and for the geeks amongst you) here’s the script I ran in order to select the Adventure Works sales data:

/*
This query allows us to extract, from the AdventureWorks database, some sample
data that can be imported into SkuBrain. In addition to the core data we've included
custom columns for Category, Subcategory and ModelName
*/  
SELECT
    -- Core fields
    head.OrderDate AS OrderDate
    ,pro.ProductNumber AS Sku
    ,det.[OrderQty] AS Quantity
    ,det.[UnitPrice] - det.[UnitPriceDiscount] AS UnitPrice
    ,pro.StandardCost AS UnitCost
    ,det.[UnitPriceDiscount] AS DiscountAmount
    ,head.CustomerID AS CustomerReference
    ,det.[SalesOrderID] AS OrderReference
    ,det.[SalesOrderDetailID] AS LineReference
    -- The rest are just demographics
    ,cat.Name AS Category
    ,sub.Name AS SubCategory
    ,mod.Name AS ModelName
  FROM [AdventureWorks].[Sales].[SalesOrderDetail] det
  JOIN [AdventureWorks].[Sales].[SalesOrderHeader] head ON det.SalesOrderID = head.SalesOrderID
  JOIN [AdventureWorks].[Production].[Product] pro ON det.ProductID = pro.ProductID  
  LEFT JOIN AdventureWorks.Production.ProductModel mod ON pro.ProductModelID = mod.ProductModelID
  LEFT JOIN AdventureWorks.Production.ProductSubcategory sub on pro.ProductSubcategoryID = sub.ProductSubcategoryID
  LEFT JOIN AdventureWorks.Production.ProductCategory cat on sub.ProductCategoryID = cat.ProductCategoryID

Running that script in SSMS (SQL Server Management Studio) results in the following:

SQL Results

Copying to Excel

Technically, SSMS will let you export results directly to CSV format. However I’ve never found it particularly easy to control this process from SSMS and Excel does a much better job of it IMO. As such, at this point I’m simply going to copy/paste the results table from SSMS into Excel.

Step 1. Select all the results

Select All

Step 2. Copy with Headers

Copy with Headers

Step 3. Paste into Excel

When pasting the data into Excel, I want to make sure that columns are preserved… so I’m going to select Use Text Import Wizard... from the Paste drop down.

Text Import Wizard

This shows step 1 of the text import wizard.

Text Import Wizard - Step 1

The defaults are fine, so I just hit Next.

Text Import Wizard - Step 2

On step 2 of the wizard, I make sure to select the correct delimiter (Tab), and verify that everything looks as it should in the Data preview pane (i.e. that column headers are sitting correctly above the appropriate data).

When I’m happy, I click Next.

Text Import Wizard - Step 3

In step 3, again, the defaults are fine so I just click Finish.

Fixing the date format

So now I have all of the Adventure Works sales data in Excel and it looks like this:

Data in Excel

This is almost ready. The only remaining problem is that Excel has done something a bit weird with the OrderData column… which is a formatting issue. This can be corrected by forcing it to treat that column as a Date and format it in yyyy-mm-dd format (the date format that SkuBrain is expecting).

To do so, select the column by clicking on the A column header and then select Format cells from the Format menu.

Format Cells

In the format dialog select Custom and enter yyyy-mm-dd in the Type editor, then click OK.

Custom Format

Exporting to CSV

Now we have our data in our spreadsheet application (Excel), we can export this to CSV format. Exporting to CSV format from Excel is pretty straight forward – it’s simply one of the file formats in the Save As... dialogue. Select Save As from the File menu and choose CSV as the file format:

Save as CSV

When you click Save Excel will warn you that CSV files don’t support multiple worksheets and that the file may contain features that are not compatible with CSV. Neither of these is a problem so just click Yes to both warnings.

Finally, we end up with a CSV file, which I have named “adventure works.csv”, which we can open in any text editor (such as Notepad) to check

Split the CSV into multiple files

The last thing to watch out for is that the CSV files that you upload to SkuBrain cannot be larger than 5MB… the file that I just created is 10.9 MB so I’m going to have to split this into 3 separate files. The reason I waited until now to point this out is that, before having saved the CSV file, I didn’t know how big it was going to be. So I didn’t know if I was going to have to split it into separate files or how many files I was going to need.

Actually splitting the CSV into separate files is pretty straight forward. In my case, I simply want to put around one third of the rows from my adventure works.csv file into three separate files: adventure works 1.csv, adventure works 2.csv and adventure works 3.csv. Note however that each file must contain the header row.

Import the CSV data into SkuBrain

Finally, we’re ready to import the CSV files into our SkuBrain forecasting app :–) This part is pretty straight forward.

From the sales Dashboard click on the Import Orders button:

Import Orders Button

On the Sales Import screen, choose the CSV file you want to import and then click Import:

Import Orders Button

Rinse and repeat for however many CSV files you have (3 in my case) and then wait for the import jobs to complete on the Jobs dashboard.

Finally, and most importantly, grab a beer!

Comments