Issues with CSV Files in Excel

Owned by Oleg Barshay
Last updated: Aug 29, 2012 by Noah KeatsVersion comment
3 min read

Whenever possible we allow exporting data from PropReports in the Microsoft Excel file format (.xls). While Excel is a useful and powerful tool, its file format has certain limitations. For example, a worksheet is limited to only 65,536 rows by 256 columns. While this may sound like an incredibly large amount of space, our clients frequently run into issues with Excel when attempting to download all historical trade data or large reports. What further complicates using .xls files is the fact that they are binary, making it difficult to import the files into other systems. As a solution, our software sometimes exports data as text files with comma-separated values (.csv).

Below is an example of a text file with comma-separated values:

Date/Time,Account,B/S,Qty,Symbol,Price,Exchg,Contra,Liq,Comm,Ecn,SEC,TAF,NSCC,Clr,Misc,Order Id,Fill Id,Status,Multiplier,Currency,Listing Exchange,Tape
01/13/2012 09:42:07,ACCOUNT1,T,100,ABB,19.82,ZRFC,"1/2",2,0.043,-0.17,0.04,0.01,0,0,0,901307,,,1,USD,NYQ,A
01/13/2012 09:42:17,ACCOUNT1,B,100,ABB,19.84,ZRFC,,2,0.043,-0.17,0,0,0,0,0,901309,,,1,USD,NYQ,A

While you can open csv files in Excel, they can cause other problems discussed below:

Text Gets Formatted as a Date or Number

When opening a csv file from the File menu or by double-clicking on it from Windows Explorer, Excel automatically decides how to format each field, and often does so incorrectly. In the example below, the value "1/2" was interpreted as January 2nd:

If you are encountering issues such as the one above, open the file from the Data menu as follows:

  1. On the Data menu, point to Import External Data and then click Import Data to open the Select Data Source dialog box.
  2. In the Select Data Source dialog box, in the Files of type list, select Text Files.
  3. In the Look in list, navigate to the folder containing the file you want and select the text file you want to import. 


Choose Delimited as the Original Data Type and click Next. The Text Import Wizard opens and guides you through a three-step process that ensures that the text is correctly split into separate worksheet columns by selecting delimiters in the files.

  1. Choose Delimiter and click Next.
  2. Check Comma. 

  3. Make formatting changes as necessary by selecting a column and then selecting an option under Column data format. For example, if there's a column of part numbers that you want to define as text instead of numbers, select the Text option.  Click Finish when done.

Opening a .CSV file too large for Excel

If you are attempting to open a Microsoft Excel file larger than 65,535 rows, you will see the error “File Not Loaded Completely”. To open the file, you can:

  1. Load the file into Microsoft Access or another database of your choice.
  2. Use a text file editor or a tool like CSVEd to search, filter and edit the file.  
  3. Use a file splitter (e.g. GSplit) to break up the file into pieces less than 65,535 lines each. You can then open each piece separately in Excel.