Working with ServiceTrade CSV Exports in Excel

IMPORTANT UPDATE: Microsoft has recently issued an update to their software that will fix the big integer issue. Click there blog post here to read more https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac

If the above link and information does not fix your issue, we have developed a workaround below. Please review Microsoft's update above, before proceeding.

This article walks users through the process of working with ServiceTrade CSV Exports in Microsoft Excel. ServiceTrade has built a custom tool to assist with the conversion of .CSV files into .XLSX format while preserving the integrity of IDs and other numerical data.

ServiceTrade, like most modern software platforms, now uses 64-bit integers in our backend system in order to scale and accommodate the growth of our customers. This means that our record IDs are now longer. You can read more about our update to 64-bit integers for Record IDs here.

Microsoft Excel has a long-standing limitation and does not support proper formatting of 16-digit integers when importing from CSVs. You can read more about this Excel limitation here via Microsoft Support. Many other spreadsheet solutions do not have this limitation. Recently our record IDs went from 15 digits to 16 digits. Customers using Microsoft products to convert data will have to follow a new process to overcome this Microsoft limitation. We’ve outlined our own solution to this problem in the article below.

Downloading and Installing the CSV2XLSX Tool

ServiceTrade has developed a custom software tool that enables you to easily convert your ServiceTrade-generated CSVs into properly formatted Microsoft Excel Spreadsheet (.XLSX) file with a single click.

Downloading the CSV2XLSX Tool

Use the links below to download the tool for your corresponding system of choice:

Installing the CSV2XLSX Tool

Once you have downloaded the appropriate installer for your system, double-click on the downloaded installer file to launch it.
On Windows, a standard installer wizard program will run to install the tool on your system.
1. On Mac, a window will open allowing you to drag the application file from the installer image into your Applications folder (see below.)
2. IMPORTANT: On Macs with Apple Silicon, you will need to run the following command in a terminal window in order for the application to run:
```
xattr -cr /Applications/csv2xlsx.app
```
  - Steps on how to run a terminal command on Mac can be found here.

Using the CSV2XLSX Tool to Convert a ServiceTrade CSV into XLSX

Open the CSV2XLSX Application
- On Windows, click on the start menu and select CSV2XLSX from your applications list.
- On Mac, navigate to your Applications folder and double-click the CSV2XLSX application.
Once launched, you will see a simple "drop-zone" window that appears that says "Drag & Drop CSV File Here."
Drag the .CSV file you wish to convert to .XLSX on this window.
- Alternatively, you can click on the drop-zone area and a system dialogue will open, allowing you to navigate to and select your CSV file of choice.
The CSV file will then be converted into a properly formatted Excel Spreadsheet file (.XLSX)
- Note: The newly converted file will be saved into the same directory where the CSV originated.

How to Manually Import CSVs into Excel for Windows

The process below will ensure that you are properly importing the ServiceTrade CSV export file into Excel with all 16-digit numbers intact and formatted properly.

Open Excel.
Create a new Blank Excel Workbook.
Navigate to the Data tab.
Select the From Text/CSV button.
Select the CSV file to import.
In the formatting pop-up window, under the Data Type Detection dropdown select "Do not detect data types."
Click Load to import the CSV into the Excel Workbook with proper formatting.

Example of properly imported ServiceTrade data in Excel for Windows:

How to Manually Import CSVs into Excel for Mac

The process below will help you properly import the ServiceTrade CSV export file into Excel with all 16-digit numbers intact and formatted.

Open Excel.
Create a new Blank Excel Workbook.
Select File > Import.
On the import dialogue select CSV File as the file type to import.
Select the CSV file that you exported from ServiceTrade.
On the Text Import Wizard Step 1 window, select Delimited and click Next.
On the Text Import Wizard Step 2 window select the Comma checkbox, indicating that the data should be separated by Commas, and click Next to proceed.
On the Text Import Wizard Step 3 window, select Text as the Column data format and click Finish to close the wizard.
Click Import and the data will come into Excel formatted correctly and will not improperly format the 16-digit numbers.