This article will provide you with a step-by-step process to format your data and upload it to all versions of the DotActiv software.
Applicable Products: DotActiv Free, DotActiv Lite,
DotActiv Pro, DotActiv Enterprise
Formatting, also referred to as ‘cleaning’, makes your data usable in the planogramming process. It will also ensure the accuracy of planograms created by the user.
Formatting Your Data
Before formatting your data, it is important to know why formatting the data and checking for duplicates is so important. Since the DotActiv application has a very structured approach to data formats, any duplicates or inconsistencies in the data would affect the accuracy of the results you were looking for.
Duplicate data results in inaccurate projections for your business, hence why it’s important to make sure you have no duplicate data at all before importing it into your DotActiv Database. Your key-value such as Product Code or Barcode can not contain any duplicates as the software will not import the data if a duplicate key value is detected.
1. Open raw data in Excel (or any relevant spreadsheet program).
2. Select the ‘Barcode’ column and click on the ‘Data’ tab.
3. Click on the ‘Text to Column’ tab that appears in the Data ribbon.
4. A ‘Convert Text to Columns’ pop-up bar will appear on the screen.
5. Click ‘Next’ until you have reached ‘Step 3 of 3’.
6. Select the ‘Text’ option seen under the ‘Column data format’ heading, as seen in the image below.
7. Click ‘Finish’.
8. The Barcodes will now appear as text.
9. Repeat the ‘Text to Column’ process for the Product Codes if they are present in your data.
10. Insert ‘Period From and Period To’ dates, as highlighted below.
11. Insert a column named ‘SKU Count’ and insert a 1 in all rows, as displayed in the image below.
12. Select all data on your sheet.
13. In the ‘Data’ tab, click on ‘Remove Duplicates’.
14. On the pop-up bar that appears, ensure that all boxes are ticked and click on ‘Remove Duplicates’, see image below.
15. Once again, select all the data.
16. By clicking Ctrl+ F, ‘Find and Replace’ all commas ( , ) and replace them all with a space ( ).
17. Ensure that all columns have the relevant headings as needed.
18. Under the ‘File’ tab, choose to ‘Save As’ your document.
19. In the ‘File Type’ bar, choose the CSV (Comma delimited)(*.csv) option.
20. Save the file in your folder of choice.
Checking For Duplicate Data
Step 1: Open your data file and insert a new column, you can remove this once your checks are done, and put it preferably next to your key-value like a Barcode or Product ID.
Step 2: We at DotActiv would recommend using the following formula to check for your duplicates in .csv files with Libre Office or Microsoft Office: =IF(COUNTIF($A$2:$A$21,A2)>1,”Duplicate”,”Unique”)
Step 3: As per the above, you can see in the snippet, the formula takes your key-value, barcode in this example, and comparing them to all the other barcodes in the tab. If there is a duplicate it will leave a “Duplicate” in your column and if it’s unique, it will leave a “Unique” in your column.
Step 4: This way you can see why, how, and what data is duplicate. You can then remove and amend as needed.
Uploading Data to the DotActiv Application
1. Open the DotActiv software of your choice
Please note that you have to open an existing shelf plan OR create a new gondola in order to import the data.
For the purpose of this article, we will be opening an existing shelf plan:
2. After opening your existing shelf plan, click on the ‘Data’ tab.
3. Click on ‘Select’.
4. Choose ‘CSV to Shelf Plan’.
5. On the pop-up screen, choose ‘Open File’ to import CSV.
6. Choose relevant CSV from the directory and click ‘OK’.
7. On the pop-up bar that appears on the screen, make sure that the data is separated by a comma and click ‘OK’.
8. Once your CSV has been opened, you can start with ‘Mapping’.
9. Click on the ‘Markets’ tab in the ‘Mapping’ column.
10. Click on ‘AutoMap’ in the ‘Mapping’ column.
11. Choose the relevant key market field e.g. ‘ Store Code’ or ‘Store Name’.
12. Click on the ‘Product’ tab in the ‘Mapping’ column.
13. Click on ‘AutoMap’ in the ‘Mapping’ column.
14. Choose the relevant key product field e.g. ‘Product Code’, ‘Barcode’, ‘Brand’ and ‘Description’ (‘Key Product’ should already be ticked if using ‘AutoMap’ – if not, please tick).
15. Click on the ’Facts’ tab in the ‘Mapping’ column.
16. Click on ‘AutoMap’ in the ‘Mapping’ column.
17. Choose the relevant key facts fields e.g. ‘Sales’ and ‘Units’(or anything related to that – this will depend on the type of data included in your CSV).
18. Click on the ‘Periods’ tab in the ‘Mapping’ column.
19. Click on ‘Period From’ and ‘Period To’.
20. Click on the ‘Import’ tab in the ‘Import’ column.
21. On the pop-up bar that appears, you can either choose to merely ‘Update Only’ the planogram currently open.
22. Or you can ‘Insert and Update’ the planogram.
23. All new products can also be added to a floating shelf to make the planogramming process easier.
If any new data has become available and there is a need to update the planogram, the new data can be added to the CSV and the entire process followed again.
Please note that to add an Excel file to the DotActiv software, you must have access to the Excel application.
Make sure to select the ‘Add new products to floating shelf’ (as seen above) to start updating the planogram. It is NOT necessary to be linked to a database for you to complete this process. Now you can begin planogramming.