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:
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
Step 2. 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.
This shows step 1 of the text import wizard.
The defaults are fine, so I just hit
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
In step 3, again, the defaults are fine so I just click
Fixing the date format
So now I have all of the Adventure Works sales data in Excel and it looks like this:
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.
In the format dialog select
Custom and enter
yyyy-mm-dd in the Type editor, then click
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:
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:
On the Sales Import screen, choose the CSV file you want to import and then click
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!