10 Tips to Help You Write a Flat File Database

Originally developed by IBM, flat file databases have been around since the 1970s. Because these files store data in plain text format, most people use MS Excel to create them. It’s an easy-to-use system that allows for the quick sorting of results. This is because each line of plain text has just one record. Tabs, commas, or other delimiters separate multiple records. In this article, you’ll learn some tips for optimizing your flat file.

Flat files may be a simpler method of storing data, but they are also rather time-consuming—until you know what you’re doing. Whether you’re new to flat files or have been using them for some time, you can speed up the process if you use the following tips:

Tip 1: Color Code for Easier Viewing

Depending on what you’re using to view your flat files, you may be able to change the colors of the columns and rows. This makes it even easier to pick out the exact data you need at a glance. For example, if you have a list of employees, you might color code them so you can tell immediately which ones have seniority or who lives closest to the office.

Tip 2: Use Text Wrapping to Print

It can be tricky to print a flat file. Often, the columns aren’t wide enough to accommodate all the information, so the printout will look like it cut off each section. To avoid this and ensure that your file prints cleanly, you can widen the column. However, this may widen the entire page too much. A better way is to just add text wrapping to the sections. In Microsoft Works, you'll find this under Report Design View, but most options for viewing your files will include something similar.

Tip 3: Refresh Your Metadata

Are you getting frustrating “data conversion failed” errors? These may affect almost every aspect of data processing, so you definitely want to avoid them. Since the most common reason for this is outdated metadata, refreshing the column should solve the issue. However, it’s easier to just make sure you refresh after every change.

Tip 4: Use a ForEach Loop Container

Moving your files around can cause a specified path error because the platform may not recognize them. You might prevent this issue if you use a ForEach Loop Container. This will scan the flat files, and once it finds the required file, execute the dataflow task. This only works if it actually locates the file; otherwise, it will cancel the task.

Tip 5: Skip Auto-Field on Large Files

For small files, there’s nothing wrong with having an Auto-Field option set up. However, once you get into larger files, this can drastically slow down the entire process. The solution is to set each field type to something other than Auto-Field.

Tip 6: Resize Your Columns

The automatic size for the column length in a flat file is 50 characters, which is usually more than you need but may be less. Either way, you could end up with truncation or errors when you transfer the files or open them in a new destination. It’s fairly easy to resize columns. Consider adjusting them to be compatible with the destination.

Tip 7: Turn Off Unicode

You can use Unicode when saving your flat file. But if you do, you cannot specify a code page. Turn this option off if you prefer to select a code page for your text.

Tip 8: Know Your Formats

Choosing the right format is easier if you know what they do. Are you aware of the main formats used in flat file databases? If not, here they are:

Delimited: Delimiters create and separate the columns.

Fixed width: The columns have a fixed width, which you may adjust.

Ragged right: These columns have a fixed width, except for the last one, which is delimited by the row delimiter.

Tip 9: Double Check Your Changes

Not sure if your delimiter change is the right one? You can see what the effects are by simply clicking on Refresh. However, keep in mind that the Refresh button will only show up once you have adjusted other connection options. Do this and you can refresh to see the changes made to the delimiters.

Tip 10: CSV vs. Delimited Files

Do you know when to use CSV vs. a delimited file? If you’re planning to move data from one database to another, you’ll probably find that CSV is the best choice. However, some people prefer delimited files because they’re more comfortable with them.

CSV files use a delimiter AND an optional enclosing character. A delimited file only uses a delimiter.

Conclusion

Creating flat files doesn’t need to be an enormous task, but it can certainly go more smoothly if you use the above tips. The more you use them, the more used to the different delimiters you’ll become. Eventually, you’ll find that you can do a lot with a very basic text file.

Use Integrate.io to Move Your Flat Files

Now that you know how to better write your flat file database, it's time to move it into database storage. Let us show you how simple that can be with our free two-week trial.