Importing Tables

Import tables into Manifold using the File - Import - Table dialog. In addition, tables stored within databases using spatial DBMS technology can be imported by connecting to the database using Database Console, clicking on the table to highlight it and then clicking the Import button in the Database Console toolbar.

When using the File - Import - Table dialog the Files of type choice acts as a filter for what is displayed in the browse pane in the Import dialog. Simple file types are imported directly from the Import dialog by choosing the desired type in the Files of type box. More complex imports from data sources such as databases are imported by choosing Data Sources () in the Files of type box and then using the Data Source dialog.

Tables may be imported into a Manifold project or linked into a project. Importing the table brings the data into Manifold so that it may be stored in the Manifold .map file and managed by Manifold System. Linking the table leaves the data stored in an external file. See the Linking Tables topic for information on linking tables into a Manifold project.

Use File - Import - Table to import tables from the following data sources:

In general, except when connecting to Oracle data sources, ADO.NET is a much faster connection technology than OLE.DB or ODBC.

Importing Tables from Text Formats

The CSV importer is used to import tables from most text formats, even those that do not use commas to separate the fields. The CSV importer may be set to use different characters as a delimiter. See the Import Table - CSV topic.

Windows Locale Settings and Table Import

Manifold's table import routines will recognize Windows locale settings for .mdb, .xls, some .dbf files (depending on drivers used) and some other formats and will automatically convert text fields to Unicode when the database file locale is incompatible with current regional options. For example, an .mdb database created with Spanish locale settings will have text fields automatically converted to Unicode if it is imported on a Windows machine employing French settings.

Importing .dbf

The .dbf or DBF format in MS-DOS and Windows arises from the dBase database management system. Due to the popularity of dBase in the 1980's this format has been adopted by many applications, including those in UNIX and other operating systems. Unfortunately, implementers of programs that read and write ".dbf" have at times "extended" the .dbf standard in ways that are not used by any version of dBase and which cannot be read and written by programs that are strictly compatible with the .dbf format as supported by Microsoft.

Some products will create and read .dbf with unusual names, even those with spaces in them, depending on the operating system or product involved. However, in Windows it is a violation of .dbf spec to use file names with spaces in them for .dbf files.

Manifold includes a special .dbf importer that attempts to auto-adapt to different varieties of .dbf files that may be encountered. This is especially important because ESRI "shapefiles," one of the most popular legacy GIS formats, use .dbf files to store data attributes. Because ESRI shapefiles created in UNIX will frequently violate Microsoft standards for .dbf, the Manifold .dbf driver will accept a wide range of bizarre violations of Microsoft spec that occur in shapefiles created with ESRI products.

For example, .dbf records that begin with an invalid leading character but which are otherwise readable are treated as valid, and Manifold's .dbf driver will read .dbf files with non-standard file and field names, even those with spaces in them.

One problem that does arise is that on occasion MEMO fields written by Microsoft's FoxPro application will not load correctly. A workaround is to use ODBC or OLE DB to connect to such files instead of the built-in .dbf importer. That is, instead of specifying a .dbf file in the Files of type box when importing a .dbf file or linking to it, we would specify an ODBC or OLE DB connection in the Files of type box and then use the ODBC or OLE DB dialogs to connect to that .dbf file.

To connect to a FoxPro .dbf file we must first install an ODBC driver for FoxPro which is part of MDAC 2.5, available at:

http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

To read the file via OLE DB we must install an OLE DB provider for FoxPro, available from Microsoft by searching their downloads page at:

http://www.microsoft.com/downloads

Importing from Excel

In general, the most efficient way of saving and working with database information, such as tables, is to use a database, namely Microsoft's Access DBMS or similar product. However, many people use Excel as a "quick and dirty" program in which tables are stored. This is rarely as good a strategy as keeping DBMS information in a real DBMS, but it is usually acceptable so long as we keep in mind the limitations of Excel as compared to a real DBMS.

Tables may be imported from Excel spreadsheets provided that the spreadsheet does, in fact, contain something that can be identified as a table. When importing from Excel, Manifold uses Microsoft's own "Jet" database and file access engine using Microsoft's Jet XLS driver to read Excel .xls file format. If the spreadsheet contains something Microsoft recognizes as a table, Manifold will be able to read that table as well.

A "table" in an Excel spreadsheet is a group of columns with the name of the column at the top and values within each column.

For example, the Excel spreadsheet shown above contains a table. Three columns are labeled at the top with values proceeding down the columns.

In contrast, the spreadsheet above does not contain a "table" as far as Microsoft software or drivers are concerned. There are many ways of intermixing data in what to the human eye is a tabular format that nonetheless as far as Microsoft software is concerned is not a table.

The rule of thumb for success is to keep it simple: save tables in Excel with only one table per .xls file, with each table organized as shown in the first Excel illustration above. Do not add anything else, such as explanatory comments, extra titles or text, subtotal calculations and so on. If you are having difficulty importing an Excel table into Manifold, try opening Access and importing the Excel table into Access. If Access can't find a table in the spreadsheet then Manifold also will not be able to find a table in that spreadsheet.

One limitation of Excel when used instead of a DBMS is that Excel does not maintain strong data types as would a DBMS. When the Microsoft Jet XLS driver reads in a table from Excel, it must infer the desired data types by examining the first few values it encounters.

For example, if an Excel column contains both text and numeric entries but the first dozen entries in the column are all numeric, the Jet XLS driver may decide that the column should be treated as a numeric column and might therefore import all values as if they were representations, say, of Floating Point Double numbers instead of a mixture of text and numbers.

The classic situation in which trouble occurs is the case of Excel tables with columns that contain ZIP codes. ZIP codes are postal codes in the United States that, despite appearing as numbers, must be treated as text since leading zeros are significant. For example, there are ZIP codes in the US such as "02138" (in Cambridge, Massachusetts) in which the leading zero is significant. Much software that utilizes ZIP codes will reject the numeric equivalent of "2138" as a malformed ZIP code because it does not contain five characters.

If we store ZIP codes in a column in an Excel table, upon import into Microsoft Access or any other program (such as Manifold) that uses Microsoft's Jet XLS driver to read the .xls file the column containing ZIP codes will be imported as a numeric column and thus leading zeros will be truncated.

This situation may be remedied by either storing such DBMS information in a true DBMS, like Access, or if we must use Excel tables by manipulating it after import into Manifold. For example, such a ZIP code column that was imported as a numeric column could be changed into a text column, all values with only four digits identified and then a leading "0" added to them under the assumption that the reason they are four digits is because a leading "0" was truncated.

Another method is to add ten or so "dummy" values at the beginning of the table where each of the ZIP code values is alphabetic text, thus forcing the Jet XLS driver to recognize that column as text and so preserve any leading zero characters. After the table is imported into Manifold, we can delete the dummy values at the beginning of the table.

Regional Settings

When importing data from text file formats like .csv or from .dbf or shapefiles the Regional Options in the system have to match settings within the file that is being imported. This is because such simple formats are too stupid to understand that in different countries people use different symbols to denote decimal points (that is, either a dot character or a comma). To change Regional Options, do the following:

§       Go to the Control Panel and open the Regional Options applet.

§      Go to the Numbers page,

§      Ensure that the Decimal Symbol is set to '.' (dot),

§      Ensure that the List Separator Symbol is set to ',' (comma),

§      Press Apply to apply changes if there were any,

§      Import the .csv file or shapefile desired.

§      After import, restore the original settings of Decimal Symbol and List Separator Symbol if you like.

Another option is to invoke the Data Sources applet (hidden within Administrative Tools folder of the Control Panel if you're running Win2K) and create a file DSN for the .csv file you want to import. After creating the DSN configure it to use the comma character as a data separator.

Important Note when Using 64-bit Manifold Editions

Due to a lack of required Microsoft facilities in 64-bit Windows systems, Manifold in 64-bit mode cannot export, import, export or link to DB, HTML, MDB, XLS or WKx format files. This includes no access to the MDB parts of Manifold MFD and MapInfo TAB imports. The workaround for importing or exporting such files is to launch Manifold in 32-bit mode by using the Manifold System (32-bit) shortcut, perform the export from or import into a .map project file and then re-launch Manifold in 64-bit mode using the Manifold System (64-bit) shortcut. Linking is more complex: the data must be in some format usable in 64-bit mode within 64-bit Windows systems, such as a SQL Server database. Alternatively, the data can be kept within a Manifold .map project file and linked using the Manifold ODBC driver.

See Also

Importing and Linking Tables

Import Table - CSV