Can a macro examine data in a csv greater than 65536 rows

**endoskeleton** · 06-08-2011, 12:21 PM

Hi

I was going to start work on creating a macro to compare two sets of data for matches.

I downloaded the csv file from our CRM system (this is the file I would be comparing against)

When trying to bring it into Excel it states "File Not Loaded Completely" because the csv produces more that 65536 rows of data.

My question is is it possible to produce a macro that examines the csv file directly avoiding the 65536 problem in excel ?

If that is not possible the second question is :

Is it possible to write a macro that would compare data in several workbooks at the same time ?

Any guidance on this is appreciated.

Cheers

Jon

**Richard Buttrey** · 06-08-2011, 12:33 PM

Hi,

Where are the two sets of data? Are they in two columns in the same csv file. If so then it would be possible to check every cell for a value in all of the workbooks with standard =MATCH() functions across as many columns as there are workbooks, but this is likely to be quite slow. And much much slower if you were to attempt the same thing with a macro.

If there are less than 1 million rows you may be better borrowing a copy of Excel for Mac 2008, or better still 2011 which does support VBA whereas 2008 doesn't.

Regards

**endoskeleton** · 06-08-2011, 01:33 PM

Hi Richard

Thanks for getting back to me so quickly.

One set of Data is held over many columns in the large csv file. The other is in a separate csv file. I would need to match on a particular column and then create a report based on the results.

Do you think that is possible ?

Failing that I could segment the large CSV file into several smaller files (so the row count is less than 65536 ), could the macro look at all of the workbooks within a particular folder and find matches ?

Kind regards

Jon

**Richard Buttrey** · 06-08-2011, 02:56 PM

Hi,

I haven't got a picture yet of your data. Are you saying that the data you want to match is in a single csv file which will result in many Excel workbooks and many columns in each one, and you want to check each cell in each of the columns in each of the workbooks with data in the other file/files?

And is the other data to which you're matching also in a single workbook or or many workbooks. The distinction is important since you can probably see that the permutations could be extremely large?

I can't help thinking that this is not a job for Excel and that you'd be better using Access or some other database tool.

Regards

**endoskeleton** · 06-08-2011, 03:46 PM

Hi Richard

Thanks for your patience.

There are just 2 CSV files. THe large one containing all the data from our CRM system (currently over 75,000 rows of data ) and a much smaller one with less than 500 records in it.

I need the macro to check through a column of the small CSV (customer reference numbers) and match with all occurrences (of the same reference numbers) in the large file.

From there I need all matching data to produce an excel worksheet showing all matching instances and other information pulled from those records in the large file.

I know how to produce a macro to do this, I just don't know how to do it on a csv file. Would the macro read all of the 75,000 + rows or would it stop at 65536 like excel does ?

Cheers

jon

**MaczaQ** · 06-08-2011, 04:46 PM

hi endoskeleton,

If you can please send those two CSV files zipped in attachment.
I have a bit knowleage about working on files and I can give you following solution:
I can create macro which check each reference number (get from smaller file) in your main file and then all rows for current reference number will create another csv file with only its data.
This way you should be able to import new CSV files normaly into excel for other things what you need.

Best Regards

**snb** · 06-08-2011, 05:03 PM

Suppose
- the largefile large.csv
- the checkfile check.csv
- in path "E:\OF\"
- the reference number are the first 8 characters of each line in the check-file
- filtered data from the large file will be written into column P

Please Login or Register  to view this content.

**endoskeleton** · 06-08-2011, 08:47 PM

@ SNB

That code looks great and I think I can certainly adapt that to suit my needs.

Just want to make sure I understand correctly that it will exact matched records on the reference number. but does the reference number need to be in the same position (column) in each file. For example if the customer reference is in column AB in the large file and in column D is the check file.

Would it still work ?

Also it will be able to match records direct from the csv, so the 65536 row (records) problem does not apply, correct ?

Cheers

Jon

**snb** · 06-09-2011, 07:28 AM

The only prerequisite for the previous code is that the identification number consists of the first 8 characters in the checkfile. If the customer number has some special characteristics those can also be used in the loop.