Formatting A .CSV file

[Ange]

Hi All,

This is more of a general question, probably not pertaining directly to
excel, but worth a try. Any help would be appreciated.
If I have a .csv file containing say 200,000 lines of data, which is the
best way, or suggessted way to format it (eg add columns, format columns
etc) in the same way that an excel macro formats an excel spreadsheet.
I know you can import the file into excel and split is into multiple files,
but that can be rather time consuming.

Cheers,

Ange

 

[Dave Peterson]

Unless you're using xl2007, you're going to be limited to 64k rows/records/lines
of data in that imported worksheet.

Maybe excel (below 2007) isn't the best tool to work on this much data.

 

[EeOr]

This may not be the best way however you could import it into MS Access,
save it as a table, do any of your formatting within the design view and
then export back to a csv.

Jon

 

[Pete_UK]

A .csv file is just plain text - there is no formatting included
within it. In order to insert new columns you would have to put the
file into an application such as Excel 2007 or Access, insert columns
as needed, then save the data back as a .csv file.

Given the limitations of 64k rows in Excel versions prior to 2007, it
would not be recommended to import 200k rows of data into multiple
sheets of these earlier versions.

Hope this helps.

Pete

 

[Beege]

Hi All,

This is more of a general question, probably not pertaining directly to
excel, but worth a try. Any help would be appreciated.
If I have a .csv file containing say 200,000 lines of data, which is the
best way, or suggessted way to format it (eg add columns, format columns
etc) in the same way that an excel macro formats an excel spreadsheet.
I know you can import the file into excel and split is into multiple
files, but that can be rather time consuming.

Cheers,

Ange

It might also depend on how many fields you have in the file. If only a
couple, then adding one more column would be an addition at the top
line, if you have field names there. But best bet would be using a
database program like Access, or OpenOffice's Base.

Beege

 

[Guest]

Hi Ange:

If you cannot use access to import the data then there are other options for
example:

1. Word. Although it gets difficult to handle.

2. Use a data analysis tool like prompt (pinnacle software - limited
freeware) or monarch (datawatch). Both of these tools process te file and
produce output.

3. Write a VBA macro to read the file line by line and process it as
required, (even making summary spreadsheets).