Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Urgent Help with Large CSV Data Files...

Jan 15, 2011 11:00PM PST

I have a client data with full details in about 50 different csv files

e.g.
------------------------------------------------------------------------------------------------------------------
1 2 3 4 5 6 7 8
------------------------------------------------------------------------------------------------------------------
a1 b1 c1 d1 e1 f1 g1 h1
a2 b2 c2 d2 e2 f2 g2 h2
a3 b3 c3 d3 e3 f1 g3 h3
a4 b4 c4 d4 e4 f3 g4 h4
a5 b5 c5 d5 e5 f4 g5 h5

Column 6 contains the nature of business, so i want to EXTRACT ENTIRE Row according to the Colunm i.e EXTRACT all Rows that Contains f1 and copy to new book/sheet.

and then Extract all Rows that Contains f2 and copy to sperate sheet/workbook.

I have 50 CSV file and about 3000 different type of categories in Column 6 (in all 50 CSV)


So I need to sort that data in to separate sheet according to categories listed in Column 6.


Please suggest best possible and easiest way to perform this, its urgent.

I will be very thankful.

regards,

Discussion is locked

- Collapse -
Re: csv files
Jan 16, 2011 3:08AM PST

This is a typical case of misusing Excel. Your 3000 different categories would result in 3000 new workbooks. If I were you I would convert your application to a real database program like MS Access.

Kees

- Collapse -
Urgent Help with Large CSV Data Files..
Jan 16, 2011 10:15PM PST

Thanks Kees for your response.
I was thinking the same, even i tried but i do not know anything about using MS Access.
Can you please tell me a bit about it? any idea?

thank you

king

- Collapse -
Roughly how large (rows and columns) are the 50
Jan 16, 2011 11:36PM PST

worksheets and is each of them the same layout ? Are you using Excel 2003 or 2007 ?

I was wondering if you could put all the worksheets into a single worksheet and then create a pivot table. From the pivot table you could generate separate worksheets of the data in any of the columns. If you need to identify which WS the data came from .... you could add a reference column to each WS before combining them indicating the source i.e. Sheet1, Sheet2,etc.,.

VAPCMD

- Collapse -
Roughly how large (rows and columns) are the 50
Jan 17, 2011 4:32AM PST

Yes VAPCMD, the format is same in all csv.
The no. of columns varies.
The size of all csv is about 1.5 GB, no point of merging them into one.

- Collapse -
This fully confirms my idea ...
Jan 17, 2011 4:38AM PST

of this being a case of misusing Excel. Nothing that can be done about it apart from starting all over with a better suited tool and migrating 1.5 GB of data into it once it's working.

Seems like a job for IT-professionals to me. Any company with 1.5 GB of client data should be able to pay them for the work.

Kees

- Collapse -
Ok...I always like a challenge but without seeing
Jan 17, 2011 5:08AM PST

data and the layout...it's tough to provide meaningful help even if the effort has to be accomplished manually.

Excel is a great product but it has it's limits. With the data as is, it sounds like you need a better solution.

VAPCMD