Q: I have a data importing puzzle. I'm the sales coordinator for my company's five field sales reps. The first week of every month, each rep sends me his prior month's call log as a text file (rodney2008feb.txt, for example) so I can import it into our archive database. It's easy enough to manually import these text files, but some months they just won't import correctly. I'll wait a couple days, then the files will import just fine. Even stranger, is that it's all or nothing -- if one file doesn't work, all five don't work. The problem when this happens is that all the data for each record will go into only the first field, not the six different fields we track. The other imports I do that week all work fine. There's no rhyme or reason I can see, but is it something I'm doing?
Figure 1: An example of a tab-delimited text file (.tab or .txt) -- The tab and carriage return are usually invisible characters but are shown here in Microsoft Word. FileMaker Pro can import this file; each line becomes a record with the data between tabs going (hopefully) to different fields.
Figure 2: An example of a comma-separated values text file (.csv or .txt) -- Confusing this with a tab-delimited text file will likely result in data being incorrectly imported.
A: You gave me all the clues I need to guess the issue: You're experiencing a classic data import problem, involving the two main formats of text files. What's interesting is why your import works some times and not others. Before I describe what I think is going on and how to fix it, here's how to distinguish the two text file formats … along with a little history.
The text file has been with us since the beginning of computing time. It's the most basic of all files, invented long before we started applying styles and fonts to our text such as Italic or 12-point Arial. As such, it carries a legacy inherited from the typewriter! On a typewriter (for those who are too young to remember!), to get characters to align in neat columns, you'd press the Tab key to move to the next place to continue typing. At the end of the line you'd have to move the cylinder holding the paper, called the carriage, to the start of the next line -- hence, the terms Carriage Return and Line Feed. The Tab key got its name because that's how you'd construct tables; the content of a table is also referred to as tabular data.
When computing was getting off the ground, programmers also wanted to represent tabular data -- so a character code was created that would separate, or "delimit," columns of text. That invisible character was naturally enough called the tab character. Thus, a file that uses the tab character to indicate columns is called a "tab-delimited" text file. FileMaker Pro can import tab-delimited text files, placing the data between tabs into different fields. Figure 1 shows a tab-delimited text file opened in Microsoft Word, with tab and carriage return characters visible.
As number-crunching programs for business emerged, a different delimiter character gained favor. The comma became the preferred separating character, and the format was known as a "comma-separated values" text file. Early spreadsheets and other financial software used this file format predominantly. FileMaker Pro can import comma-separated text files, placing the data between commas into different fields. Should the data itself contain a comma, the data for that column is contained by double-quotes. Figure 2 shows a comma-separated text file opened in Microsoft Word.
My reason for the trip through history is that both file formats are legitimately considered to be text files. Computer operating systems use extensions at the end of file names to convey the type of file, and the three-letter ".txt" suffix is used to describe text files of either format. Over time, the convention evolved where comma-separated values text files are usually named with the extension ".csv". Tab-delimited files may use the extension ".tab", although this convention is followed much less frequently. Microsoft Excel, for example, still saves tab-delimited text files with the extension ".txt".