I have a .csv file that contains 500+ fields. Obviously
too large for Excel. Is there a way to write a macro
that will import column 1-256 into a worksheet, and then
load column 1, and columns 257-510 in a second
worksheet? I will need to do this on a regular basis,
and doing by hand will take way too long.
Thanks
My first idea was "use database for this". Only trouble is: With MS
Access you can't have more than 255 fields in a table, just like Excel
(at least that's true for an externally connected text file, like a csv
file). Which leads to a somewhat strange advice in a MS newsgroup (I've
given it a few times already)... Use OpenOffice.
If installing another application is an option, read on...
The spreadsheet in OpenOffice can't handle more columns, but the
database interface can. With the database interface in OpenOffice I have
opened a CSV file with 500+ fields as a table, and created a few queries.
In the current stable version of OpenOffice (1.1.3 or 1.1.4, depending
on language) the database interface is available as a tool for
wordprocessor and spreadsheet. It seems a little sluggish, but once you
get used to it it's rather intuitive. The not-so-intuitive part is
configuring the database.
-Download the Office suite from
www.openoffice.org and install.
-start worprocessor or spreadsheet
-View data sources (F4)
-Right click in the left DB pane and select "manage data sources"
-Add a data source
-Give the database a name and select connection type="text"
-Select a folder for your database. This is where your csv-files will
go, and all csv files in this folder will appear as tables in the database.
-Select the "Text" tab
-Select the proper field/text delimiter (unless you know it's something
else, stick with the defaults of ; and ").
-Check/uncheck the "headers" box, as applicable (should be checked if
the first row of your csv file contains field names)
-Select extension "csv"
-For proper decoding of international characters you may need to change
the character set. Choice depends on where the csv file came from: Many
Windows applications use Ansi. Some use Unicode/UTF. Most DOS
applications will use an "extended ASCII" type of character set, in
which case you must find the correct "Western...(DOS/OS2-...)" entry.
You can then use queries to extract data. Be warned that indexing is not
available on text files. Queries on large tables without indices will
take some time. If you need to do extensive work, I advise copying to a
different database (to use a proper DB engine).
