This article gives you a few tips, tricks and things to be aware of when dealing with CSV files.
CSV stands for comma-separated values, an open file format (.csv) for spreadsheets which can be opened, modified and saved in all operating systems such as Windows, Mac OS or Linux. You use a CSV (comma-separated values) file to perform stock takes, import and export orders, contacts, reports (export only) and bulk data updates in Stock&Buy. CSV files might seem complicated at first, but after you understand their format and how they work, you might decide to use them for a variety of tasks.
When updating a CSV file export, make sure the software program you uses the UTF-8 encoding to read the CSV content. This is to avoid generating unwanted special characters in your file. If you save your file after forgetting to confirm its UTF-8 encoding, then your data will be corrupted when you upload it back to Stock&Buy. In most cases, Stock&Buy won’t be able to recover corrupted files for you.
In a CSV file, each entry represents a cell in a spreadsheet, each line represents a new row, and each comma indicates where one entry ends and another one begins.
For example, the following shows a stock take CSV file export:
Excel, Numbers and Google Sheets
When you export a CSV file from Stock&Buy, it opens as a .csv file in your computer’s native text editor. You can also use an alternative text editor, but it’s recommended that you use a spreadsheet program to view and edit your CSV files instead.
There are many tools out there you can use to open, modify and save CSV files. The most prominent desktop software programs are Excel for Windows and Numbers for Mac users. While Excel and Numbers are excellent tools to deal with spreadsheets and tabular data, they can give you headaches when faced with CSV files.
Excel sometimes converts CSV integer values into scientific notation and/or truncates decimal points. This behavior can easily corrupt certain fields of the exported file. For example, Excel might automatically convert integer barcodes such as “12345678901” to “1.23E+10“. In other situations, Excel might truncate text and display a set of #### characters instead. Furthermore, depending on your regional and language settings on your operating system, Excel can apply special formatting to CSV data when presented to you. For example, a decimal value 1234,34 with two decimal digits will show as 1,234.34 if you are located in the US.
To avoid all these issues, we highly recommend using Google Sheets instead.
Import CSV files into Google Sheets
To open a CSV file in Google Sheets:
- Login to your Google account
- On the Google Sheets page, click Blank in the Start a new spreadsheet section
- From the Untitled Spreadsheet page, go to File > Import…:
- Locate and select the CSV file that you want to edit
- In the Import file dialog, select your preferred options under the Import action, and Convert text to numbers and dates sections
Your CSV will look something like this after you’ve uploaded it:
From here on you can edit your CSV and change your data. When you are done, export the data from Google Sheets as a .csv file by clicking on File > Download as > Comma-separated values (.csv, current sheet)
Your CSV file will appear in your downloads folder.