Solutions - About Data Files
The information here is designed to help you understand your data files and how Pervasive DataTools can help you convert virtually any data file. Here we will address types of data files, sources of data files, and how one or more of the DataTools products works with each.
Data File Types | Data File Sources
What Type of Data Files Do You Have?
There are many ways to classify data files: storage format, readability, structure, etc. In the tables below you will find brief explanations of some of the types of data files, and which of our DataTools products work with each.
Data Storage Formats
At the highest level of classification, data file storage formats can be grouped into two categories of formats. Each is described below.
Open vs. Proprietary Formats
- Open Formats
- Open file formats are common formats that can usually be exported from or imported into databases and applications.
- While these files are somewhat "transportable", the quality of the data and schema differences between the data file and the database or application into which you need to import the data add complications. The data often must be cleaned and transformed prior to loading the data into your target.
- Three of the most common open file formats are: CSV text, or tab delimited ASCII, and xBASE (.DBF) files.
- While these files are somewhat "transportable", the quality of the data and schema differences between the data file and the database or application into which you need to import the data add complications. The data often must be cleaned and transformed prior to loading the data into your target.
- Proprietary Formats
- Most databases and applications store the data in their own proprietary format.
- These data files are usually not compatible with any other database or application; therefore, requiring either custom code or the use of a data tool that can read, translate, and transform the data from its source and into another application or database.
- Some examples of proprietary file formats are: DataEase, Clarion, Microsoft Access, Oracle, IBM DB2, and many others.
- These data files are usually not compatible with any other database or application; therefore, requiring either custom code or the use of a data tool that can read, translate, and transform the data from its source and into another application or database.
Structured vs. Semi-structured vs. Un-structured Formats
- Structured Data
- Structured data files are those that contain sufficient metadata within the data file that a data tool can automatically parse the file into sequential records and fields.
- Most desktop databases store their data in a structured format so the Pervasive DataTools can auto-parse the data. This makes the task of mapping the source data to the target database or application relatively simple with a drag-and-drop mapping interface.
- Some examples of structured data are: Microsoft Excel, Microsoft Access, SQL Server, Oracle Database, Sybase, and Informix.
- Most desktop databases store their data in a structured format so the Pervasive DataTools can auto-parse the data. This makes the task of mapping the source data to the target database or application relatively simple with a drag-and-drop mapping interface.
- Semi-structured Data
- Semi-structured data files contain a limited amount of metadata and cannot be automatically read or parsed by any application except the one for which the format was originally designed.
- These data storage methods are often referred to as a "record managers". The Data Parsers enable you to either manually parse or to "overlay" the data file with a dictionary file that contains the metadata or parsing rules.
- Some examples of record manager file formats are: Btrieve, C-TREE, Micro Focus COBOL, and Fixed Text (with record separators).
- These data storage methods are often referred to as a "record managers". The Data Parsers enable you to either manually parse or to "overlay" the data file with a dictionary file that contains the metadata or parsing rules.
- Un-structured Data
- Un-structured data files contain no metadata - only data.
- These are commonly known as "flat files" and usually cannot be read by any application other than the originating application. They cannot be auto-parsed, but the DataTools Data Parsers enable you to either manually parse or apply a dictionary file that contains the metadata or parsing rules.
- Some examples of un-structured flat files are: Binary, COBOL, C-ISAM, and Fixed Text (without record separators).
- These are commonly known as "flat files" and usually cannot be read by any application other than the originating application. They cannot be auto-parsed, but the DataTools Data Parsers enable you to either manually parse or apply a dictionary file that contains the metadata or parsing rules.
Readability
- Readable
- The data in "readable" data files is all stored as character data that can be opened in most text editors and can be read by a human.
- CSV Text and Fixed Text data files are the most common readable data file formats. Pervasive offers DataTools for both.
- Non-Readable
- Most data files are stored in such a way that they cannot be opened in a text editor and read by a human. They are designed to be read only by a computer.
- The data in non-readable files are stored in packed and/or binary data fields that must be unpacked and "interpreted" before you can read the data. And reading the data is important when you are trying to determine how to resolve the schema differences between your source and your target!
- There is probably a Data Parser that will work with your packed and binary data files.
- The data in non-readable files are stored in packed and/or binary data fields that must be unpacked and "interpreted" before you can read the data. And reading the data is important when you are trying to determine how to resolve the schema differences between your source and your target!
What is the Source of Your Data File?
Almost everyone is familiar with common data file name extensions such as .DBF, .XLS, .TXT, and .DOC. But how often do you encounter a data file with a .DAT file extension? Where did it originate? And how can you view or convert the data? Good questions!
In this section you will find information to help you determine the following:
- The original source of your data file
- The DataTools that work with your data file
The Source of Your Data File
Obviously, the most reliable way to know the source of a data file is to know the name of the application or database from which it originated. However, that is not always possible. So, here are some options...
How reliable are file extensions?
File extensions are only reliable when the originating application or database requires a specific file extension and disallows renaming of its data file(s). Here are some examples of relatively reliable file extensions:
| File Extension | Database(s) / Application(s) |
|---|---|
| .MDB | Microsoft Access |
| .XLS | Microsoft Excel |
| .DBF | dBase, Clipper |
| .FPT | FoxPro |
| .CSV | Comma Separated Text |
| .SDF | Standard Data Format |
| .BTR | Btrieve |
| .RTF | Rich Text Format |
| .HTM | HTML |
When a data file type is known, choose one or more of the DataTools that work specifically with its application, database or file format.
Conversely, the following table contains a list of file extensions that are common, but not a good indicator of the originating application or file format.
| File Extension | Database(s) / Application(s) |
|---|---|
| .DAT | Too many to name! |
| .BIN | Too many to name! |
| .TXT | Too many to name! |
So What To Do With Unknown Data Files?
Pervasive DataTools provides an answer. The Data Parser for Binary data can open virtually any data file to which you have access. You may parse the file into records and fields, and export the data to a CSV text file. Of course, the ability to do this may depend on your knowledge of packed and binary data types. But, you do have an option!
Your transaction secured by high-grade AES-256 encryption.