Formatting CSV files in Microsoft Excel for Bulk Download/Copy/Delete

November 11th, 2022

We recommend importing the CSV file to an Excel worksheet, instead of simply opening the CSV file, to preserve data integrity. Opening a CSV file using Excel may result in data corruption due to unexpected formatting being added if your settings are not properly configured.

Please follow the steps below to use the more reliable Excel Import Wizard process:

MAC

  1. Open a New Excel document or a Blank Workbook.
  2. Go to File > Import to open the Import Wizard.
    Note: Windows users may not see the Import button. Instead, go to File > Open, select Text Files from the file type drop-down list in the Open dialog box and choose a CSV file to open. Skip to Step 5.
  3. Choose CSV file and click on the Import button.
  4. Navigate to the folder with the CSV file you want to open and choose the CSV file.
  5. The Text Import Wizard will open. Make sure you choose the Delimited radio button/checkbox, Start import at row: 1 and File origin: Unicode (UTF-8).
  6. Click Next.
  7. Select the Comma checkbox under Delimiters in the open dialog box. For Text qualifier choose the double quotes (“) from the drop-down list.
  8. Click Next.
  9. In the Data Preview pane the first column of data is highlighted. Drag the horizontal window control all the way to the right. While holding down the Shift key click on the final column. This should select and highlight all columns in the Data Preview pane.
  10. Choose Text as your Column Data Format. Every column should be labeled Text in the Data Preview pane.
  11. Review the Data Preview window to ensure your data appears correctly.
  12. Click Finish.
    • If prompted to select a location for your data (e.g. “Where do you want to put the data?”) choose Existing sheet: =$A$1.
    • Click OK.

WINDOWS

  1. Open a New Excel document or a Blank Workbook.
  2. Place your cursor in Cell A1
  3. Click on the Data tab
  4. Click on Get Data > From File > Text/CSV
  5. Navigate to the folder with the CSV file you want to open and choose the CSV file.
  6. Click Import
  7. The data preview pane will appear
  8. From the File Origin drop down menu, Choose Unicode UTF-8
  9. From the Delimiter drop down menu, choose Comma
  10. Click the Load button

To properly save the modified CSV file, the comma must be used as a delimiter. The default Excel delimiter may be different from comma in some regions, in which case it is necessary to follow the steps below to switch to comma:

MAC

  1. Open the Excel Preferences window.
  2. Choose the Edit icon.
  3. In the Edit window, un-check the Use system separators checkbox.
  4. Under Use system separators set Decimal separator to period (.) and set Thousands separator to space ( ) or blank ().
  5. Close the window.

Note: If you want to change a CSV separator only for a specific file, check the Use system separators checkbox again after saving the modified recordings report as CSV UTF-8 (Comma-delimited) (.CSV) file format.

WINDOWS

  1. Open desired spreadsheet.
  2. Click on File > Options > Advanced.
  3. un-check the Use system separators.
  4. Under Use system separators, set Decimal separator to period (.) and set Thousands separator to space ( ) or blank ().
  5. Close the window.

Note: As you must open a spreadsheet before the Use System Separators options will appear, you will need to do this for every CSV file you open on Windows.  If you prefer, you can go into your Windows Region Settings to globally change System Separator behavior as follows:

  1. Open the Windows Control Panel.
  2. Go to Clock and Region Settings.
  3. Under Region, select Change date, time or number formats.
  4. The Region dialogue box will open.
  5. Click Additional Settings at the bottom of this window.
  6. Change Decimal Symbol to a period (.).
  7. Change List Separator to a Comma (,).

Note: Older versions of Excel may fail to properly format your CSV file. This will result in a failure notice when you try to upload the CSV file into the Scheduler for purposes of bulk recording copy or delete.  If this happens, you can try using Google Sheets instead:

  1. Login to docs.google.com/spreadsheets in your web browser.
  2. Open a new blank spreadsheet.
  3. Place cursor in Cell A1.
  4. Click File > Import > Upload > Browse.
  5. Search for and select the desired CSV file on your hard drive.
  6. Click Open.
  7. Import File box will appear.
  8. From Import Location, Choose Replace Spreadsheet.
  9. From Separator Type, Choose Comma.
  10. Leave Convert text to number, dates and formulas BLANK.
  11. Click Import Data.

To Save a copy of the CSV file to your hard drive, click File > Download and choose CSV from File Type list.