A few months ago, a discussion about how to handle pipe-delimited text files in FoxPro 2.6 appeared on the NICAR-L listserv. Unlike most database managers, FoxPro versions below 6.0 can't handle pipe delimiters when appending (importing) text files into an existing structure. The good news is the tips outlined here can be used to deal with other delimiter problems. Technically, any character can be a delimiter. Paul Walmsley of IRE and NICAR has even seen carriage returns used as delimiters and page returns used to mark the end of records. The goal is to find a search-and-replace tool that meets your needs.
The important thing to realize is there are many ways to work around strange or incompatible delimiters.
you'll need to be aware of ways around the problem if you have a pipe-delimited file and plan to use FoxPro 2.6 as your database manager.
The first tip comes courtesy of Tom Boyer, research editor for The Seattle Times. Tom suggests using the text editor in FoxPro 2.6 when dealing with pipe-delimited files. Just open the text file in Fox, go to "Edit" on the "Menu Bar," and choose "Find." In the window that appears, use a pipe to fill in the "Look For" box. Then type It in the "Replace With" box. The It is FoxPro's way of representing a tab with characters. When you're done, hit the "Replace All" button on the right side of the box. Then go grab a cup of coffee because it could take a while if you have a large file.
This technique can also be used in word processing applications, but you have to know how your application represents tabs with characters. For instance, Microsoft Word uses ^t to represent tabs.
Another option when trying to import a pipe-delimited file into FoxPro 2.6 is to use one of the many search-and-replace applications available on Web sites like www.download.com. Web developers use many of these applications to ggtrings of HTML code, but these applications work just as well on simple search-and-replace actions.
NICAR uses a DOS search-and-replace program called Xlate to create tab-delimited files from pipe-delimited Service Difficulty Reports downloaded from the FAA's FTP site. The nice thing about these DOS applications is you can make batch files in Notepad that allow you to search and replace on many files at once. For instance, NICAR uses the following batch command on the FAA data:
c:\util\xlate "|" \x09 SDRC9932.TXT SDRC9932.tab c:\util\xlate "I" \x09 SDRC9933.TXT SDRC9933.tab c:\util\xlate "I" \x09 SDRC9934.TXT SDRC9934.tab c:\util\xlate "]" \x09 SDRC9935.TXT SDRC9935.tab c:\util\xlate "I" \x09 SDRC9936.TXT SDRC9936.tab c:\util\xlate "I" \x09 SDRC9937.TXT SDRC9937.tab
The first command ("c:\util\xlate") tells DOS we want to use XLATE in the util directory on the C drive. The "I" is the character we want to replace, and the "Ix09" is the hexadecimal value for a tab. SDRC993X.TXT are the original files, and SDRC993X. are the files we create after searching and replacing.
Of course, you also could import the data into Access and then export it into a .DBF if you really want to use Fox. The problem with that tactic is the field lengths will default to 255 characters.
The important thing to realize is there are many ways to work around strange or incompatible delimiters. The problem isn't restricted to pipe-delimiters and FoxPro. But luckily the solutions listed here can handle just about any delimiter riff that throws you off.
Jason Grotto can be reached by e-mail at jason@nicar.org