Split Large CSV excel file into two files or worsheet

[tarone]

I have a large file approx 107000 rows with 12 columns. It is csv file
coloum separated with comma.

I have very little knowledge of access and macros.

I have excel 2000 and access 2000. All i want is to work on this data
in excel. Is there any way to make two files or worksheet from it.

I can open it in ACCESS but i don;t know how to make subsets or split
it.

Any advice will be highly appreciated.

regards

 

[Guest]

One of the features of Access is it's capacity to hold more data than excel.
If you have imported all the data into a table, you use Queries to analyze
and manipulate the data to get the information you need. Routinely I import
mainframe reports in excess of 200,000 lines with many columns, the typical
size of weekly charge reports in my business. Access handles it all nicely

 

[Guest]

You can also export your tables and queries to Excel up to the limits of
Excel of course

 

[tarone]

I can open the file in Access. it has 107000 rows. But i have following
problems.

1. I don;t know how to split in subsets. ( it is not based on days or
months)
1. One of the coloumn have html tag, i want to remove these tags and
just save text.
2. Access auto adjust the width of row (i guess it has limit on width),
i want to keep the data without adjusting the width.

Any solution, will be highly appreaciated

regards

 

[John Vinson]

I can open the file in Access. it has 107000 rows. But i have following
problems.

1. I don;t know how to split in subsets. ( it is not based on days or
months)

What IS it based on? Is there anything within the contents of the file
which would let you logically subset it; or do you just want to
extract 65535 rows, and then the rest of the rows?

1. One of the coloumn have html tag, i want to remove these tags and
just save text.

That would involve an UPDATE query, probably using the Replace()
function: e.g. a query with a criterion

LIKE "*<BR>*"

and an Update To clause like

Replace([fieldname], "<BR>", "")

will eliminate all the <BR> terms. You'll need some (not terribly
difficult but maybe a bit tedious) VBA code to seek out and remove all
of the possible tags.

2. Access auto adjust the width of row (i guess it has limit on width),
i want to keep the data without adjusting the width.

Access doesn't deal with "width" in tables, and the width of a display
on the screen is completely irrelevant to the data that's stored!
Could you explain what you're seeing that you don't want?

Don't confuse data STORAGE with data DISPLAY. They are two separate
operations. Data is stored in Tables; it's best displayed on forms for
onscreen viewing, and/or in reports for printing. Either will allow
the data to be arranged however you please.

John W. Vinson[MVP]

 

[tarone]

I just want to extract 65535 and then the rest of the rows.

Here is the example of one cell of the colom (this colomn has all
rows like this)
When i open the file in access and select " as text qualifier, it
adjust the width of following and limit it to 255 characters only. if i
select non as text qualifier, access distribute data in more than one
cell.

Actual example of one cell of the colomn

<table border="1" cellpadding="0" cellspacing="0"
style="border-collapse: collapse" bordercolor="#C0C0C0" width="100%"
height="54"> <tr><td width="100%" height="20"
bgcolor="#D2E4FF"><b>Notes: </b>4-4OUNC-2</td></tr><tr><td
width="100%'" height="21" bgcolor="#EAEAEA"><p
align="left"><b>Hewlett-Packard Part Numbers F1440-60976 compatible
with the following Models:</b></p></td></tr> <tr><td width="100%"
height="60"><p align="left"><font face='Times New Roman' size=2><a
href=http://www.name.com/hewlett-packard-omnibook-4100-parts.html
title='OmniBook 4100'>OmniBook 4100</a></font>, <font face='Times New
Roman' size=2><a
href=http://www.name.com/hewlett-packard-omnibook-4110-parts.html
title='OmniBook 4110'>OmniBook 4110</a></font>, <font face='Times New
Roman' size=2><a
href=http://www.name.com/hewlett-packard-omnibook-4150--13-3--parts.html
title='OmniBook 4150 (13.3)'>OmniBook 4150 (13.3)</a></font>, <font
face='Times New Roman' size=2><a
href=http://www.name.com/hewlett-packard-omnibook-4150--14-1--parts.html
title='OmniBook 4150 (14.1)'>OmniBook 4150 (14.1)</a></font>, <font
face='Times New Roman' size=2><a
href=http://www.name.com/hewlett-packard-omnibook-7100-parts.html
title='OmniBook 7100'>OmniBook 7100</a></font>, <font face='Times New
Roman' size=2><a
href=http://name.com/hewlett-packard-omnibook-7150-parts.html
title='OmniBook 7150'>OmniBook 7150</a></font></p> </td></tr></table>


resulted cell after i open with access
<table border="1" cellpadding="0" cellspacing="0"
style="border-collapse: collapse" bordercolor="#C0C0C0" width="100%"
height="54"> <tr><td width="100%" height="20"
bgcolor="#D2E4FF"><b>Notes: </b>4-4OUNC-2</td></tr><tr><td
width="100%'" height="21" bgcolor

Above is example of only one cell.
So problem remains the same
a. Access auto adjust the width of row (i guess it has limit on width),
i want to keep the data without adjusting the width.
b. I want to remove all the html tags. ( any macro or command which can
delete the html tags)


regards

I can open the file in Access. it has 107000 rows. But i have following
problems.

1. I don;t know how to split in subsets. ( it is not based on days or
months)

What IS it based on? Is there anything within the contents of the file
which would let you logically subset it; or do you just want to
extract 65535 rows, and then the rest of the rows?

1. One of the coloumn have html tag, i want to remove these tags and
just save text.

That would involve an UPDATE query, probably using the Replace()
function: e.g. a query with a criterion

LIKE "*<BR>*"

and an Update To clause like

Replace([fieldname], "<BR>", "")

will eliminate all the <BR> terms. You'll need some (not terribly
difficult but maybe a bit tedious) VBA code to seek out and remove all
of the possible tags.

2. Access auto adjust the width of row (i guess it has limit on width),
i want to keep the data without adjusting the width.

Access doesn't deal with "width" in tables, and the width of a display
on the screen is completely irrelevant to the data that's stored!
Could you explain what you're seeing that you don't want?

Don't confuse data STORAGE with data DISPLAY. They are two separate
operations. Data is stored in Tables; it's best displayed on forms for
onscreen viewing, and/or in reports for printing. Either will allow
the data to be arranged however you please.

John W. Vinson[MVP]

 

[John Vinson]

Above is example of only one cell.
So problem remains the same
a. Access auto adjust the width of row (i guess it has limit on width),
i want to keep the data without adjusting the width.
b. I want to remove all the html tags. ( any macro or command which can
delete the html tags)

Access Text fields are limited to 255 bytes. Memo fields can be
64KBytes (actually bigger if you populate them progrmmatically). I
haven't used linked or imported Text files so I'm not certain if you
can define a field in a text file as Memo, but I rather doubt it. Does
your file contain only the one field?

To remove all the HTML tags will either take some fairly complex
(beyond the scope of this newsgroup, at least for me) or some
prewritten code designed to do so - there is very likely some program
out there which can process a text file in this way. YOu should then
be able to import the text file directly.

Aren't Excel cells also limited in the amount of text they can
contain? Are you sure you'll be able to store this information in
Excel at all??

John W. Vinson[MVP]

 

[SmartbizAustralia]

Strange business request....

Either create one shet per 10,000 rows....but why? How can business
users edit/update 100,000 records......

or display the data in a datasheet view.

Even better, only use Access and add a subform as a data sheet and some
pull downs to filter this...

The less data to be manipulated the better!