Automating CSV Data for Reliable API Testing

Recently, I tackled a practical challenge in my API automation work and wanted to share both the problem and my solution — in case it helps someone out there in a similar boat!

🚧 The Problem
In my current project, one of our APIs is responsible for onboarding users through the upload of CSV files. This API checks the uploaded data against existing backend records and rejects the entire upload if it detects any duplicates

Now, here comes the tricky part: My test needs to run repeatedly (automated), but each time it runs with the same CSV file, the API fails because duplicate entries already exist in the system.

Two workarounds were initially proposed:

1. Manually upload a new CSV file every time (not scalable)
2. Reset the database daily (risky and not practical for CI/CD)

I knew we could do better.

💡 My Thought Process
Instead of changing the whole database or managing tons of files manually… Why not generate unique data programmatically for every test run?

That way:

• The API sees fresh data every time
• The test runs on schedule (hands-free!)
• No need to reset the DB or manage uploads manually

🛠️ The Solution I Built
I created an R&D project to build an automated solution that:

1. Create a dummy API locally that mimics the real endpoint behavior (CSV upload, duplicate checks). Later, I deployed it on a dummy server to test the flow in a CI/CD environment.
2. Used faker.js to dynamically generate unique user data (ID, Name, etc..)
3. Write that data into a temp.csv file using Node.js
4. Use Postman to send a form-data request with the CSV
5. Executed the API request via Newman to upload the CSV file.
6. Runs in GitHub Actions every 30 minutes or is triggered manually as needed.
7. Sends me the test result report in Telegram, with a full HTML report with pass/fail status

⚙️ Technologies Used:

• Node.js for scripting
• faker.js for generating fake (but unique) data
• Newman to run Postman tests in CI
• GitHub Actions for automation
• Telegram Bot API for real-time notifications

The Result

• ✅ Fully automated test
• 📂 Always fresh and valid data
• 🧪 No duplicates = no failed tests due to data
• 📲 Real-time result delivery
• 💥 100% hands-off CI/CD experience

My Key Learnings

• Automating dynamic test data prevents flaky API test failures due to duplicates.
• Manual work, like resetting databases or uploading new files daily, isn't scalable
• Real-time Telegram alerts provide quick visibility into test outcomes [you can use Gmail, Slack, or any other platform to get the real-time update]
• A simple R&D initiative can lead to valuable production-ready solutions.

What’s Next?
This R&D setup is now complete and validated. I’ll be integrating it into our production test automation pipeline soon.

LIVE API + Repository Link
☑️API Endpoint: https://playing-with-api.onrender.com/docs/

📂Github Repo: https://github.com/Sisadia/playing-with-api

Why I'm Sharing
This might seem like a small challenge, but it's a real-world pain point in test automation — and solving it with code gave me the flexibility to focus on testing logic rather than data cleanup.

Happy testing! ✨