How to prepare your sales report for processing

The first thing to do before sending a sales report to be processed is to ensure that it is a CSV file. But what is a CSV file? According to Microsoft, it is…

A CSV (Comma Separated Values) file is a special type of file that you can create or edit in Excel. Instead of storing information in columns, CSV files store information separated by commas.

This comma that separates the information in a CSV file can be other characters as well, such as the tab (t), the semicolon (;), and the comma itself (,). Those are the most common. Ideally, it should be a character that isn’t used as the content of the columns, so we’ve got a few tips for saving the CSV and making sure it doesn’t mess up your file.

Tips

1) Fields separated by semicolons (;)

The first one is: save your CSV separated by semicolons (;). This character does not participate in most of the content that may be within the columns of your sales report, so it is the most suitable.

2) Fields with the values enclosed in quotation marks (“)

By saving the contents of cells in quotation marks (“), you help ensure that column separation, along with the semicolon column separator, works best and that its contents are rendered correctly.

3) Save the contents as formulas and not with their result

It can happen that the identifier of a video on YouTube starts with the equal character (=), causing Excel or LibreOffice to interpret the content as a formula. Therefore, when you save the file, the content will be saved with an error, which will not be identified by the sales processor. Sales files never have formulas inside their content cells. Be careful!

4) Save content in UTF-8

This is to help with standardization. Some sales partners ship in different formats, which first need to be discovered in order to be used explicitly when processing. This is a complex problem, as you have to test each and every type of charset and look at it (a human person) to check if it is legible! In other words, almost impossible mission for the machine!

5) Numbers using decimal separator with dot (international standard)

Our processor is able to identify most cases of displaying numbers, but to ensure that your numbers are interpreted correctly, save them with the decimal place separator with the period (.), as is done in the foreign system. In Brazil, one thousand Reais and thirty-four cents are represented as R$ 1000.34 and R$ 1,000.34. Ideally, it should look like this: 1000.34. This will ensure, especially if this spreadsheet goes through many people and their Excel is configured differently, that the values are lost.

In Libre Office, you can change the default to be international or open the file indicating the columns with the international standard.

6) Not saving content as it is being displayed

Another common problem that can happen is that your CSV editor has some content preview option turned on and displays the content in a different way, as it happens with very large numbers that are often displayed in scientific notation.

What happens is that the program displays the number in scientific notation according to the number of numbers you can see in the Excel column, for example. Because of this, the UPC 759108797670 can become 7.5911E+11 or 7.6E+11 and the reverse conversion yields different results from the initial value, such as 7.5911E+11 becomes 7591100000000 and 7.6E+11 becomes 7600000000000. You can have fun with this online calculator.

In Excel, one workaround is to add a single apostrophe (‘) before a number will force Excel to treat a number as text (including a default left alignment). And if you have errors flagged, it will be displayed as a number stored as a text error in the cell. Another option is for you to format the column in the form of numbers. The problem with this option is that you’ll have to do it every time you open a sales spreadsheet that contains UPCs, mostly.

7) Columns with English name

Some distributors offer the possibility for you to download your sales reports in several languages. What happens is that we may not have implemented your sales partner’s processor in the language you have chosen, or even that the column names are not all translated. Therefore, it is ideal that you export your report in English. If you don’t know English, it won’t make a difference, as the report will be imported and processed successfully and in our system it will be in your language!

8) Remove any information before the column headings

Other distributors or sales partners have a habit of including extra information about receipts, customer name, and so on in the first few lines of a sales report that should only contain sales lines. This is a hindrance to readers of CSV files, as the practice is for the first row to contain the names indicative of the contents of each column. This helps both the person viewing the file and the machine reading the file. In our case, it’s the machine, and it needs a pattern to be able to work.

How it’s done

How to do it in Libre Office

First you have to open the file, then click on the main menu under “File->Save As…” which can also be accessed via the keyboard shortcut “Control+Shift+S“. In the window that opens, select the “Edit filter settings” box so that you can select the options mentioned above, as in the image below.

Select the name of the file and after clicking on the “Save” button, the window below will appear so that you can select the options. Select exactly as it’s marked to save as explained in this tutorial.

By saving in this format, through Libre Office, you solve the standardization tips from 1 to 4 mentioned above.

How it’s done in Excel

The steps are as follows:

1.  Open the CSV file with a Microsoft Excel spreadsheet.
2.  Navigate to the “File” menu option and click “Save As.” The window appears as shown below:

Save As option in Microsoft Excel

1. Click “Browse” to select a location to save the file.
2. The Save As window appears as shown below:

1. Then enter the name of the file.
2. Select the Save as type as CSV (comma-delimited) (*.csv) option.
3. Click the Tools drop-down box, and then click Web Options. A new window for web options appears as shown below:

1. On the Encoding tab, select the “Unicode (UTF-8)” option from the “Save this document as” drop-down list
2. Finally, click Ok and save the file.

Conclusion

In an ideal world, all sales partners, i.e. physical and digital stores, physical and digital distributors, any partner who has to report sales would use this standardization. This would reduce processing problems, time spent searching for new reports, time of the sales partner’s support team having to attend to their customer to review the spreadsheets sent, in short, it would avoid an unnecessary chain of work and would improve the perception of the result of the work of all of us!

Got any doubts, any suggestions? Contact us!

[page_tags]