Many companies run their work from Excel sheets for years. Customer data, stock, and transaction records sit in dozens of files made by different people. When a new application is ready, an often underestimated question appears: how do you move all that data without bringing old problems into the new system? A clean application will still produce wrong reports if the data inside it is a mess.
Start by listing every source. Ask each department which files they use, who maintains them, and which version is the latest. A single customer list often has three copies with different contents. Choose one main source for each type of data, and write down why. This decision should be made by the people who understand the work, not only by the technical team.
Next, decide what really needs to come across. Not all old data has to enter the new application. Customers inactive for years, items no longer sold, or old transactions can stay in an archive. Master data such as customers, items, and employees usually must be moved, while old transactions can often be carried over as opening balances if your bookkeeping rules allow it. The less unnecessary data you bring, the lower the chance of mistakes.
After that, clean the data. This is the slowest stage, but it decides the result. Look for the same name written differently, such as PT Maju and PT. Maju Jaya. Standardize date formats, phone numbers, and item units. Split content mixed in one column, such as an address and a phone number. Check for empty columns, numbers stored as text, and cells holding personal notes that should not come along.
Simple Excel features already help a lot. Filters reveal blank or odd rows, while duplicate highlighting helps you find codes used twice. Still, do not delete something just because it looks the same. Two customers may have similar names and be different people. Let the data owner decide, and keep a copy of the original file before changing anything.
Then match the Excel columns to the fields in the new application. Make a simple table showing which old column goes into which new field, which fields are required, and what format is expected. The application often asks for a category code while Excel only holds the category name. Problems like this are easier to solve before the import than after the data is already in.
Do not move everything at once. First try a small part of the data, such as a few dozen rows of each type, in a test environment separate from real data. Check that names, numbers, and dates appear correctly. Open the data from the screens users work with every day, then try running a transaction with it. Many errors only show up when the data is actually used.
Once the small trial goes well, run a full import in the test environment and reconcile the results. Count the rows before and after. Compare total stock, total receivables, or balances on the same date. Ask the finance and warehouse teams to check random samples. If there is a difference, find the cause, and do not simply leave a note to look at it later.
Choose the right moment for the switch. Set one cutoff date, for example at month end or on a quieter day. After that date, the old Excel files are locked and can no longer be changed. Without this rule, people keep filling in two places, and the new data soon differs from the old. Announce the schedule well ahead so that each department can prepare.
Prepare a backup before the real data is moved. Keep a copy of the cleaned Excel files in a safe place, along with notes on the date and who approved them. If a serious problem appears after the switch, the team can restart from a clear point. Limit who can open files containing personal data as well, because copies like these spread easily without anyone noticing.
The work does not end once the data is in. During the first few weeks, collect user reports about wrong data and fix the source of the problem, not just the row. Assign someone responsible for each type of data and its entry rules, so that the order you have achieved does not fade after a few months.
EXE AJA works on application and information system development, as well as IT consulting. If your company wants to move from Excel to a better organized system, tell us what you need through our contact page.
Illustrative photo: RUT MIIT via Unsplash.
