While you've almost certainly heard of relational and NoSQL databases, there is a better than even chance that you're completely unfamiliar with flat file databases. Flat file databases are indeed a real thing, but they don't get much love these days. As we'll learn in today's blog, there is a better way to work with flat file databases than in years gone by. In fact, if you use any of Navicat's database development and admin clients, you're in the ideal position to do so!
History and Limitations
Flat file databases have been around ever since the very first computers. They are a type of database that stores data in a plain text file, whereby each line of the file holds one record, and fields are separated by delimiters - typically commas or tabs. As such, flat file databases share more commonality with a spreadsheet than a relational database. Due to their simple structure, the "tables" represented within a flat file database support limited functionality, such as record and column sorting.
Flat file databases flourished as a back-end to applications. Their simple structure takes up less space than structured database files and work well for configuration data. If you have some programming savvy, you can find ODBC drivers for most languages for interfacing with flat file databases. Unfortunately, most relational database clients cannot connect directly to a flat file database. However, relational databases provide commands to import flat file databases and use them in a larger relational database.
Importing a Flat file
If the structure of a flat file database sounds familiar to you, it's because it is very similar to a CSV (Comma Separated Values), TSV (Tab Separated Values), or any DSV (Delimiter Separated Values) file.
Every relational database provides its own command(s) for importing data from a flat file. For example, MySQL provides the LOAD DATA INFILE statement. You have to create the database and table(s) first, but you only need to do that once for each data set. Once you've done that, LOAD DATA INFILE is very fast! Here's an example statement for importing a CSV (Comma Separated Values) file:
LOAD DATA INFILE 'c:/path/to/file.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Using Navicat Import
Navicat's powerful Import utility is a wizard-driven process that helps you import a wide variety of formats from DSV, JSON, XML, and more.
It lets you choose your record delimiter, field delimiter, and text qualifier:
You get a full progress report as the import proceeds, including the number of tables and rows processed, along with errors encountered and time taken:
Conclusion
In today's blog, we learned about flat file databases and how to import them into your relational database using native database commands as well as Navicat.
For a more in-depth look at Navicat's Import utility, I wrote the Importing XML, CSV, Text, and MS Excel Files into MySQL article a couple of years ago showing how to import data in a variety of formats using Navicat for MySQL.
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial organizations. In his spare time, Rob has become an accomplished guitar player and has released several CDs. You can hire Rob by emailing him at This email address is being protected from spambots. You need JavaScript enabled to view it. .
