I have an MS Access database on the web. In order to upload the database, I export the tables to Excel, then save each table to CSV format, and then upload. Total of four tables. Pretty straighforward nobrainer.
This has worked without a hitch for several month now. Until recently.
ONE of the four tables is now causing me problems.
I can save it to Excel - no problem, no lost data.
But when I save the Excel form to CSV format, suddenly there is major data loss. I am only able to save about 1/5th of the entire Excel table.
There is no problem on the other three tables, all of which by the way are far larger in size.
Why is this suddenly happening and what can I do to fix it?
Yes. But for other reasons, I always have to do that. Due to the different formatting on the website, I have to delete the top row of titles for each table, and on some tables I have to delete a few columns. Never had a problem.
On this particular table, I delete two columns, and the top row (titles) before changing to CSV format.
Again - there has never been a problem until just last week.
What I mean is, is the data that goes missing from columns, or is it from rows? Does it stop at row 98, when there should be 150 rows? Or, does it stop at Column F, when there should be data up to Column K?
Try checking for characters in a cell that are being interpreted as separators (typically punctuation marks).
Excel also has this bad habit of changing data formats for reasons that remain mysterious to mere mortals - are you using alpha or numeric data ? You might want to try manually converting the excel table to text, and visually scanning the trouble area for anomalies.
It is stopping at row 75 out of 686 rows. So do you think there is some punctuation or something in that row that is causing the problem?
And regarding punctuation…there are several memo fields that hold large documents written in Word. But again, this is the same data that is in the other three tables that still have no problem in being converted to the CSV format.
If you can’t readily see something in that row that might be throwing it off, you might try deleting the first missing row and try again. If it works properly, then you’ll definitely know it’s the culprit.
This table is a list of Plaintiffs for a large, class action suit. In one section, a memo is attached, sometimes several pages long. So, for one Plaintiff, the memo was the reason. Would only save up to that point. I removed that Plaintiff from the equation and suddenly all is back to normal.
So now my next question - why? I mean, there are larger memos for other Plaintiffs in this table, so I don’t think it is a matter of size. And I went through this memo very carefully and did not find any odd punctuation. No “” next to each other or , next to each other. Any idea what kind of punctuation would cause this snafu?
And thanks for your, and everyone else’s help. At least I have isolated the problem. Now I just need to fix the problem memo, and be aware of what to look out for in the future!