Direct answer
A migration can report every record loaded and still be wrong, because the counts match while the values changed. The usual culprits are dates read in the wrong order, times shifted by a time zone, accented or non-Latin characters garbled, numbers read with the wrong decimal separator, leading zeros dropped from codes and IDs, and blanks that turned into zeros. Check each of these on a small sample of real rows before the main load, and compare the values, not just the totals. This page lists the checks. The wider planning is in the legacy system data migration plan.
Dates and ambiguous day and month order
A date such as 03/04 means one thing in one country and another elsewhere. If the source and the target read the order differently, every date where both parts are twelve or under is silently wrong, and dates above that fail loudly, which hides the problem. Find a date in your data that can only be read one way, note how the source shows it, and use it to confirm how the load interprets the whole column. Record the agreed format in the mapping.
Time zones: stored timestamps versus displayed ones
Some systems store a moment in a fixed reference and show it converted to the viewer's local time. Others store the local time as it was typed, with no reference at all. Loading one kind into the other can move every timestamp by some hours, or move records across a date boundary so a day's entries appear on the day before or after. Find out how both sides store time, then compare a few known events, such as a call you remember, before and after.
Character encoding: names with accents or non-Latin scripts
If a file is saved in one encoding and read as another, accents turn into stray symbols and other scripts turn into question marks. It often happens when a file passes through a spreadsheet. Always test with the records that have the hardest names, meaning accents, umlauts and non-Latin scripts, and inspect them in the target. If your customers include such names, this test is not optional. See also the notes on spreadsheet handling in spreadsheet to database migration.
Numbers, currency and decimal separators
One country writes a decimal with a comma and another with a point, and thousands may be separated by a space, a point or a comma. A misread separator can turn a small amount into a huge one or drop cents. Check the amounts with decimals, the largest values and any column that mixes currencies. Keep the currency as a field of its own, rather than as text inside the number.
Leading zeros and IDs that spreadsheets turn into numbers
A spreadsheet treats a code such as one that begins with zero as a number and removes the zero, and may show a long numeric ID in scientific notation. Postal codes, phone numbers, product codes and account references are the usual victims. Where a value is an identifier and not a quantity, treat it as text throughout the process, and check it against the source. The importance of IDs is covered in keeping old IDs and links after migration.
Empty versus zero versus unknown
A blank, a zero and a value that was never known are three different facts. If the process turns blanks into zeros, reports show a quantity that was never recorded. Decide for each field how missing data is represented in the target, and check that the load follows the rule. Do not let an empty cell become a real-looking value by accident.
Sample rows to test
Build a small test set that deliberately contains the awkward cases, and load it before the real data:
- A date that can only be read one way
- A record near midnight or at a month boundary
- A name with accents and one in a non-Latin script
- An amount with decimals and a very large amount
- A code with a leading zero and a long numeric ID
- A record with an empty field and one with a real zero
Record the rules where the builder will use them
Write the agreed formats into the mapping and the integration specification, so the same rules apply to later loads and to any system connected afterwards. The system integration specification template has a place for this.
Where LATYNEX fits
If the target is a custom web application, portal, integration or internal tool, we can write the format rules into the migration plan and test them on sample rows before the main load. See Web Application Development.
When LATYNEX is not the right fit
It is not the right fit for a pure vendor-to-vendor move that needs no custom build, or for an IT file-server migration. This page does not describe how any specific product parses dates or text, and it does not claim that bad data can be detected automatically. The checks need a person who knows what the data should look like.
Questions
Why do the record counts match but the data is wrong?+
A count only shows how many records arrived. Dates, encoding, separators and leading zeros can change values without changing the count. Compare values on a sample.
How do I check date order?+
Find a date that can only be read one way, such as one with a day above twelve, and see how the load interprets it. Then apply that rule to the whole column.
Why do accented names turn into strange symbols?+
The file was saved in one encoding and read as another, often after passing through a spreadsheet. Test with the hardest names before the main load.
Why did my codes lose their leading zeros?+
A spreadsheet treated the code as a number. Treat identifiers as text throughout and compare them with the source.
What should a test sample include?+
The awkward cases: ambiguous dates, midnight records, accents and non-Latin scripts, decimals, leading zeros, and both empty and zero values.