Data Import Wizard

Iron Speed Designer’s Application Wizard places a “Data Import Wizard” button in selected Table Report panels, such as in the Table Report page.  The wizard currently supports importing from these file types:

Using the Data Import Wizard

Step 1:  Enable pop-ups on your web browser in order to display the Data Import Wizard.

Step 2:  Select the file you wish to import.

The file selection step of the Data Import Wizard.

Step 3:  Map the fields in the data file to the database fields into which the data is to be imported.  The Data Import Wizard displays the first few rows of data in your file to help you map the columns in your file to the database fields.

The data mapping step of the Data Import Wizard.

If the Data Import Wizard finds column names in your file that match the database field names, then those columns are automatically mapped to those database fields.  Unmatched data columns remain unselected, and you should manually map any column you wish to import.

Be sure to select the ‘Import’ check box for the columns you wish to import and unselect the check box for columns you do not want to import.

The Update related table option means that if the application user is importing any value that is a foreign key in another table and that value is not present in that table then that value will be added. However if the value is not already present and Update related table is not checked, then the record will not be imported. Consider an example:

Products table has category field which is a foreign key to the Category table, using the CategoryID value as the foreign key. The application user can import either the CategoryName or CategoryID in the products table.

Case 1: Update Related table is checked

1. The application user is importing values for the category field where the display as property of the field is CategoryName, in this scenario if CategoryName is already present in the Category table, then value will be imported, however if the CategoryName is not present in the Category table, then it will be added first and then updated in the products table

2. User is importing for the category field where there is no display as value, then record will be imported, however CategoryID value will be treated as CategoryName and added in the Categories table. Now primary key of this newly added record will be used in the Products table.

Therefore if you are importing record with ID fields from parent key table instead of display as value, always uncheck this checkbox

Case 2: Update related table is not checked

1. User is importing values with CategoryName, in this scenario we do not resolve display as value therefore it will be skipped irrespective of whether it is present or not in the parent table

2. User is importing values with CategoryID, if CategoryID is present then record will be imported, however if it does not exist in the Categories table, then it will be skipped

In addition to this, there is a threshold value set for writing skip messages to a text file. If the number of skip messages exceeds this threshold value, it will be exported in text file; otherwise they will be shown in the dialog. This threshold value (SKIPTHRESHOLD) can be set in the code file; ImportData.aspx.cs or ImportData.aspx.vb.

Step 4:  Click ‘Import’ to import your data.

Importing data from Microsoft Excel files

Currently the Data Import Wizard supports *.xls (Office 97-2003) and *.xlsx (Office 2007) file formats.  The data file should be in Microsoft Excel Workbook format.

It is possible to have multiple worksheets in one Excel workbook. To access specific worksheets, enter the worksheet name in Step 1 of the Data Import Wizard.  The Data Import Wizard uses “Sheet1” by default.

Importing data from ASCII CSV files

An ASCII CSV data file.

The Data Import Wizard supports ASCII comma separated value (CSV) files.  ANSI CSV files are not supported.  Data fields must be separated by the culture-specific separator character, one data record per file row.  Most cultures use a comma (,) separator character but some use semi-colons (;).  The culture is selected via the application’s Upper Tool Bar, and each culture has its own associated separator character.  If data elements are separated by a space character, then the Data Import Wizard will consider them as one single data field rather than multiple data fields.

Data elements can be text strings without any quotes:

Fred Jones, The Big Corporation

Data elements can be enclosed in single quotes:

‘Fred Jones’, ‘The Big Corporation’

Data elements can be enclosed in double quotes:

“Fred Jones”, “The Big Corporation”

Data elements can contain commas if enclosed within single quotes:

‘$12,587’, ‘Big Riding Lawnmower’

Data elements can include commas if enclosed within double quotes:

“$12,587”, “Big Riding Lawnmower”

Data rows can end with a comma or other culture-specific separation character:

“Fred Jones”, “The Big Corporation”,

Data rows can be separated with multiple new lines:

“Fred Jones”, “The Big Corporation”

 

“Michael Bigglesworth”, “MB and Associates”

Special characters such as the registered trademark symbol ® can be imported if the file is in UTF-8 format.

Importing data from Microsoft Access files

Currently the Data Import Wizard supports *.mdb (Office 2000-2003) and *.accdb (Office 2007) formats.

It is possible to have multiple tables in one Access database file. To access a specific table, provide the table name in Step 1 of the Data Import Wizard.  “Table1” is used by default.

Protected Microsoft Access database files

Currently the Data Import Wizard supports only password protected Microsoft Access files via the Microsoft.ACE.OLEDB.12.0 data provider. It does not support any other security type which involves a security descriptor file (*.mdw) such as User/Group level security.  If your system has only the Microsoft.Jet.OLEDB.4.0 data provider installed then the Data Import Wizard will not support password protected Access files because this data provider requires a security descriptor file (*.mdw).

Field formatting

When importing data, the Date, Currency, and Number formats in the data file should match the application’s format settings so that values are imported properly.

Data columns

Number of data columns in the data file must match the number of column headings in first row if the file has a column header row.  For example, if the column heading row has five columns then all other data rows in the file must have five columns. 

Note however, it is not necessary for the data file to have same number of columns as the database table.

De-duping imported data

The Data Import Wizard will not import duplicate records into your database if a matching record already exists.  Moreover, the Data Import Wizard will not update existing records with new field values.  The Data Import Wizard uses the primary key field in the imported data file to detect duplicate (matching) records.  If the data file contains a primary key field, that value is checked against the database when importing.  If a record already exists with that primary key value, then the record is skipped (not imported), and the import process will continue.

If the imported data file does not contain a primary key field, then the Data Import Wizard does not check for duplicate records and all rows in the imported file will be imported.

Database table and view support

Importing data is only supported for database tables.  Data cannot be imported into database views.

Primary key handling

If the table’s primary key is computed, automatically generated by the database, or automatically incremented by the database, then the Data Import Wizard cannot import the primary key column.  If your data file contains primary keys, they can be skipped by unselecting the primary key column in the Data Import Wizard.

If the primary key field is not computed, automatically generated or automatically incremented then the Data Import Wizard can import primary keys only if a record does not already exist in the database with same primary key value.

Foreign key handling

If the table has a foreign key field, then the Data Import Wizard first attempts to locate an existing record in the foreign key table with same foreign key value.  If the Data Import Wizard finds such a record, it will use the primary key value for that foreign key record and insert it into the newly imported record’s foreign key field.

If the Data Import Wizard cannot find a matching record in the associated foreign key table, then it first attempts to insert a new record in the foreign key table with value in the imported data record.  If the foreign key record is created successfully then the Data Import Wizard gets the primary key of the newly created record and inserts it into the imported record.

Read-only, computed and automatically generated column handling

The Data Import Wizard cannot import data into read-only, computed in the database, and automatically generated database fields.  These fields are effectively ‘read only’.

Culture specific data

Do not import data files exported from applications using a different culture than the application server into which the data is being imported.  The Data Import Wizard can import data if data does not violate database constraints, but some data may lose its meaning due to cultural conversion, especially for currency conversion.

Large import files

If you have large amount data then the Data Import Wizard may time out.  To avoid this, increase your application’s request time by adding or updating the following in your application’s Web.config file:

<system.web>

            <httpRuntime executionTimeout="{Your time-out value}"/>

</system.web>

Replace {Your time-out value} according to your need.

Supported data providers

The Data Import Wizard requires at least one these data providers to be installed on your system in order to access Microsoft Excel and Microsoft Access data files.

Custom medium trust support

If your hosting provider runs a custom medium trust environment, make sure that the user has “write” permission.  The Data Import Wizard cannot function if the custom medium trust setting does not have “write” permission.

Be sure to grant permission to the “ASPNET” user to write into your application’s “\Temp” folder.  If this does not work, try giving “write” permission to the “Everyone” user and so forth.  See your hosting provider’s user list for details; it is different for every hosting provider.

You must also set write permission for the Application folder’s FileIOPermission class in the machine’s Web.config file to enable temporary file saving.

The permission entry in your machine’s Web.config or separate trust configuration file should look like:

<IPermission class="FileIOPermission"
            version="1"
            Read="$AppDir$"
            Write="$AppDir$"
            Append="$AppDir$
            PathDiscovery="$AppDir$"
/>

Spaces are not trimmed

The Data Import Wizard does not pre-trim any field values, text or numbers, before importing them.  The Data Import Wizard reads the data from the selected data source and does not alter the value in the data source file.

See Also

Application Function Buttons

PDF Report

Microsoft Word Report

Export to Microsoft Excel

Export to ASCII CSV

Data Import Wizard