Importing/Exporting CSV, JSON, XML

Imagine our OnlineStore needs to send its entire product catalog to a new partner vendor. This catalog is extensive, and manually copying it would be a nightmare! Or, perhaps the marketing team ran a campaign and has a list of hundreds of new potential customers in a spreadsheet. How do we get this data into our database efficiently and accurately? This is where importing and exporting data in common formats like CSV, JSON, and XML becomes incredibly useful. These skills are essential for any Database Administrator to manage data flow between the database and the outside world.

By the end of this lesson, we’ll be able to:

• Understand why importing and exporting data is a crucial DBA skill.

• Recognize and understand the structure of CSV, JSON, and XML data formats.

• Import data from CSV, JSON, and XML files into our MySQL tables.

• Export data from our MySQL tables into CSV, JSON, and XML files.

• Identify common challenges such as file permissions, character encodings, and data type mismatches, and learn how to handle them.

Let’s get started and learn how to move data like a pro!

Why import and export data?

In our journey as Database Administrators, we’ll often find ourselves needing to move data in and out of our MySQL databases. This isn’t just a niche task; it’s a fundamental part of managing and integrating data.

Key reasons include:

• Data migration: When moving from an old database system to a new one, or upgrading MySQL versions, we’ll need to export data from the old system and import it into the new one.

• Interacting with other systems: Our database rarely lives in isolation. We might need to share sales data with an accounting system, import product lists from suppliers, or send customer information to a marketing platform. These external systems often use standard formats like CSV, JSON, or XML.

• Backups and archiving: While MySQL has its own binary backup methods (which we’ve discussed in Backup & Recovery), sometimes exporting data to human-readable formats can be useful for specific archiving needs or for smaller datasets.

• Reporting and analysis: Analysts or other departments might need data extracts in specific formats for their reporting tools or analytical processes.

• Bulk data updates: Sometimes it’s easier to export data, modify it in bulk using a spreadsheet or script, and then import it back into the database. For example, updating prices for a whole category of products.

• Populating development/testing environments: We might export a subset of production data to populate development or testing databases, ensuring developers work with realistic data.

Effectively importing and exporting data ensures data integrity, saves significant time, and enables seamless integration with other applications and services.

Understanding common data formats

Before we get into the details, let’s first examine the file formats we’ll be using. Each one has its own setup and is used for different purposes.

Comma-separated values (CSV)

CSV is one of the simplest and most common formats for exchanging tabular data. It’s a plain text file where each line represents a data record (a row), and each record consists of one or more fields (columns) separated by a comma. Sometimes, a different delimiter (like a semicolon or tab) might be used, and text values containing commas are often enclosed in double quotes. The first line can optionally serve as a header row, naming the columns.

Its simplicity makes it widely supported by almost all spreadsheet programs (like Excel and Google Sheets), databases, and programming languages. It’s human-readable for small datasets and very efficient for bulk loading simple tabular data.

Example: Our OnlineStore receives a list of new Suppliers from a partner in a CSV file. Each row contains the supplier’s name, email, phone, and address. We need to import this into our Suppliers table.

JavaScript Object Notation (JSON)

JSON is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. It’s built on two structures:

• A collection of name/value pairs (often realized as an object, dictionary, or map).

• An ordered list of values (often realized as an array or list). Data is represented in key-value pairs, similar to how objects are defined in JavaScript.

JSON has become the de facto standard for data exchange on the web, especially for APIs. It’s more structured than CSV and can easily represent nested data and arrays, making it flexible for a wide range of data types.

Example: A mobile application for our OnlineStore sends new customer registration details to our backend server as a JSON object. We then need to parse this JSON and insert the data into the Customers table.

Or, a list of new products might come as a JSON array: