Whenever possible we allow exporting data from PropReports in the Microsoft Excel file format . However, due to limitations of Excel (for (.xls). While a useful and powerful tool, Excel's file format has certain limitations. For example, a worksheet is limited to 65,536 rows by 256 columns) we sometimes generate files as . Also, because they are binary, the files are difficult to import into other systems. Because of this, we sometimes export 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
.csv).
For example:
| Code Block | ||
|---|---|---|
| ||
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. This can often be the incorrect. formatting of text field values may be incorrect. For example, the value "1/2" will be interpreted as January 2nd:
If this is causing problems, the workaround is to open the file from the Data menu as follows:
- On the Data menu, point to Import External Data and then click Import Data to open the Select Data Source dialog box.
- In the Select Data Source dialog box, in the Files of type list, select Text Files.
- Then, 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.
- Choose Comma as the Delimiter and click Next.
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
- You can load the file into Microsoft Access or another database of your choice.
- You can use a text file editor or a tool like CSVEd to search, filter and edit the file.
- You can 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.