Best way to import a huge .csv file into Excel?

  • Thread starter Thread starter pete
  • Start date Start date
P

pete

Hello everyone --

Ignorance alert: I'm not a user of Excel or of any other spreadsheet s/
w.

Each morning, I generate a file of all the stock options that can be
traded that day. This file must be as easy as possible -- a snap --
to import into Excel.

Each record in this file has around 90 fields that describe one single
pair of put and call options: yesterday's closing price and volume,
the open interest in each option, its theoretical value, its
volatility, and many more.

This file has many records: around 160,000; and it's huge: maybe 70MB.

Ordinarily, only a few hundred of the records in this file are
interesting to any user but each user has his own idea of what
"interesting" is. That is, I hope that each user can apply his own
filters such that he'll end up importing only the interesting few
hundred.

Finally, my question:

What is the "best" way for me to arrange and write this file so that
it's super-easy for anybody -- any non-geek inexpert user -- to import
it into Excel?

A 38-line example of the file I'm talking about is at:

http://sellmycalls.com/option-list.txt

It shows a very few of yesterday's options for Apple, Amazon, Bank of
America, and Best Buy.

Thanks a lot, everybody! Happy New Year to all.

-- pete
 
Hi Pete!

I assume, that you have no influence depending the
row names for the download. It would be very helpfull if
you could configure automatically column-names in the first line before
the download. Try it. - And all your users need only the steps 6) and 7)
for filtering.

1) save the file as .xls or rename it to .xls before you open it with excel.
2) insert a new empty row on the top of the table (A:A)
3) in cell A1 insert the word "col1"
5) use the filling function to fill all columns automatically from A to
the last column: select A1 and navigate with the mouse to the lower
right corner of this cell and drag the mouse to the last cell of the
first record (e.g. AZ1). This fills the first record with column1
column2 column3 .... Now you have column names for filtering.
6) select the cells in line 1 from A1 to the last column of the record
7) Use "DATA"-Filter from the menu.
8) Now you can filter or sort all records via all columns as you want.
 
Hi Pete!

I assume, that you have no influence depending the
row names for the download. It would be very helpfull if
you could configure automatically column-names in the first line before
the download. Try it. - And all your users need only the steps 6) and 7)
for filtering.

1) save the file as .xls or rename it to .xls before you open it with excel.
2) insert a new empty row on the top of the table (A:A)
3) in cell A1 insert the word "col1"
5) use the filling function to fill all columns automatically from A to
the last column: select A1 and navigate with the mouse to the lower
right corner of this cell and drag the mouse to the last cell of the
first record (e.g. AZ1). This fills the first record with column1
column2 column3 .... Now you have column names for filtering.
  6) select the cells in line 1 from A1 to the last column of the record
  7) Use "DATA"-Filter from the menu.
8) Now you can filter or sort all records via all columns as you want.

pete schrieb:
















- Show quoted text -
Click to expand...

create a file with column headers option-list-header.txt and add it to
the data and save as file type csv

open command line c:\copy option-list-header.txt+option-list.txt
option-list.csv

Note: option-list-header.txt must have a carriage-return at the end of
the list of columns
 
Back
Top