Versions Compared

Old Version 1

changes.mady.by.user Oleg Barshay

Saved on

compared with

New Version Current

changes.mady.by.user Noah Keats

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

Whenever possible we allow exporting data from PropReports in the Microsoft Excel file format .  However, due to limitations of Excel (for (.xls). While Excel is a useful and powerful tool, its file format has certain limitations. For example, a worksheet is limited to 65to only 65,536 rows by 256 columns) we sometimes generate files as . 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).  Subsequently, loading those files in Excel can cause various issues.  Below are some of the most common problems and their workarounds:

Text Gets Formatted as a Date

 

 .

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

No Format
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:

Anchor
textformatting
textformatting

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:

Image Added

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. 

Image Added


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.

Image AddedImage AddedImage Added

Anchor
filetoolarge
filetoolarge

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.