UKC

Help!

I have stored an csv file on a shared network drive. With 204,000 rows.

Every time a colleague opens it we get something different - attempt 1 - 175,000 rows (roughly), attempt 2 - 187K...

Every time it's different, and every time it truncates the file.

Any idea what's causing this? it's fine on some machines so I'm thinking limited by memory (it's only 67 MB file tho)

In reply to balmybaldwin:
which version of excel? some of the older version are limited to the number of rows they support
could the file be converted to a native excel workbook?

and 67Mb for an excel file is actually pretty large

In reply to balmybaldwin:

Excel is just naff these days. Why they couldn't leave it as it was I don't know .

In reply to rallymania:


65k I think. Would be consistent though in my experience.

IS there anything in the event logs. Sounds like it is partially opening before dropping the connection. Seems odd though and have never heard of anything similar.
If you copy the file to local does it open fully?

In reply to balmybaldwin:

If it's 67MB then the file is corrupted somehow. You have a worksheet that contains a lot of populated rows that will be blank. Copy and paste the contents to another one or you risk it becoming unusable.

In reply to balmybaldwin:

Hmm. Inconsistent, and doesn't match any of the row limits I'm aware of in Excel.

The first thing I suspect is network connection problems, very possibly related to your antivirus software.

KevinD's suggestion of copying the file to a location on your own computer, then opening that copy, seems like a good thing to try.

If you are able to temporarily disable the antivirus software on your PC and/or whatever machine the file is shared from, that might be worth trying too.

In reply to balmybaldwin:


Although, be very careful with this - especially as the file seems to be larger than it should be.

Is it something you could submit to https://www.virustotal.com/ for checking?

In reply to balmybaldwin:
67MB is pretty big for Excel, we have a similar issue from time to time, run this AddInn to clear the excess cell formatting and see if it improves things:

https://support.office.com/en-us/article/Clean-excess-cell-formatting-on-a-...

Direct download here:

https://support.microsoft.com/en-za/kb/244435

In reply to Dark-Cloud:

If it's a csv file as stated, it won't have cell formatting.

In reply to balmybaldwin:
Thanks for suggestions, will try to move it to a local drive and try again... it's just weird. I would tell you which version of Excel it is, except they've changed all the menus and I now can't find the help>about option. as far as I am aware it is the latest version without the row limits as they were

Edit the reason I categorise 69MB as small is compared to the RAM on the machine (4GB) its tiny

In reply to balmybaldwin:

Most obvious first step after copying to a local drive (if this doesn't solve the problem) is to try and open it in a bog standard text editor (notepad, notepad++, etc) - csv is simply plain text.

In reply to Kid Spatula:


Curious as to the reasoning here. Can certainly have csv files (or excel files even) as big as this. Also, it's a csv file not a worksheet - I'm thinking you missed that bit maybe.

In reply to balmybaldwin:


Click on "File", then "Account" (one of the options down the left hand side when you have clicked on "File"). On the right there should be a Product Information display, click on "About Excel" and it should pop up with the info.

32-bit and 64-bit Excel do have different limits on size, but I'm surprised the CSV gets truncated - I'd expect an error message when trying to open it. Perhaps the file is corrupted?

Also a 70MB CSV file would consume a lot more memory when opened in Excel as Excel itself (and any add-ins etc.) would be included. For example, I opened a 70MB CSV on my machine and Excel as a process (with only that one CSV open) was taking up about 250MB of memory. Still small compared to 4GB (but that depends on what else you have running). Again, I'd expect an error message rather than just a silent truncate as if nothing was wrong!

In reply to Willi Crater:

Another step would be to save it as xlsx and see if your issues persist. Excel is not always great with CSV files.

In reply to Willi Crater:

Erm, your title says Excel, that may have mislead several people, me included.....

In reply to Dark-Cloud:


It's an Excel problem happening when he opens a .csv file within excel.

I'd suggest opening it using notepad but it sounds like it's a huge file and maybe one of the characters in the file is wrong. Finding the corruption that way won't be easy.

In reply to balmybaldwin:

What are you actually trying to do with this CSV file? Excel, IME, isn't great with very large data files. I'd open it with something like Notepad++, and do simple processing using a custom script (AWK, Perl, Python, etc).

In reply to DancingOnRock:
Notepad may not do big files. Perhaps WordPad or Notepad++.

In reply to Dark-Cloud:

My title says excel because the problem is in excel.

Excel can read a plethora of data files, supposedly without any hard limit.

In this case I have installations of the same version of Excel on the same network on 2 different pcs, and one can read the CSV file fine, and does so on every occasion even when running other resource hungry applications, and another PC that never reads the correct number of rows. Both PCs can read the whole file without problem in a basic text editor.

I'm off network now so can't test further, but will have a go tomorrow

In reply to elsewhere:


gVim for the win

In reply to captain paranoia:

What we are doing is trying to find a reliable way to transfer the data to a third party in a reliable format. The transport mechanism is sorted, but relies on the file being moved around the network by automated processes. If we can't read the file properly there's a risk once automated we see the same issue but it wont be noticed immediately.

It's not a major stumbling block - as you say there are much more appropriate bits of software to read, edit, manipulate and compile the data with. It's just bugging me why it won't open it properly on 2 seemingly identical machines. Not to mention it's caused a great deal of confusion and lost testing time when trying to reconcile the data extract with system held data until we realised what was happening...

In reply to balmybaldwin:

Zip and unzip, if it's a file size problem; or use XLSX as a file type. (say what you want about microsoft, but xlsx and docx filetypes are far superior to xls and doc files)

In reply to balmybaldwin:


I imagine you are worried that the inability of Excel to open the file suggests your transport mechanism isn't 'sorted'...

I'd suggest using any form of checksum to validate original and moved copy. Zip will work well, as it includes checks, and will report corrupt files. It will also improve your transfer times... Or use something like an MD5 signature.

In reply to Dark-Cloud:


........"I demand a refund!!!!!!"

In reply to balmybaldwin:

I think that having hundreds of thousands of rows in Excel is a good way to bring it to its knees, using a database may be a better approach.

In reply to Dark-Cloud:


Erm, it's not my title....

In reply to balmybaldwin:

Try the 64bit version of Excel. I've found this handles large datasets better. If you're subscribed to some flavour of Office 365 you can choose to install that version instead. You'll need to uninstall the previous version first.

Cezza

In reply to elsewhere:


Possibly. But we don't know if it is a big file or whether Excel is corrupting it and making it huge.

In reply to DancingOnRock:
Good point, i've had huge excel files that went from crazy to sensible sizes when re-saved.

In reply to DancingOnRock:

Yes which is why i suggested clearing the excess formatting in case the CSV has got something strange going on because as soon as its in Excel its Excel format, but nevermind.....

In reply to Dark-Cloud:


But unless you save it as a .xls or .xlsx the formatting is discarded upon the closing of the application. .csv cannot save Excel's formatting options.