|
Spreadsheet Import Wizard Error
By The Gunny
Don't be left in the dark when the Spreadsheet Import Wizard fails to import your spreadsheet.
(Thousand Oaks, CA -- Dec. 9, 2005) -- Microsoft Access has plenty of built-in functionality for using, importing, and exporting data quickly and easily. Most of this functionality is handled by easy-to-use Wizards. One such Wizard is the Spreadsheet Import Wizard. While it's easy to use, it can be downright frustrating when you select the "Finish" button to import the data into your table, and you are greeted with:
"An error occurred trying to import file 'C:\Data\MySpreadsheet.XLS'. The file was not imported."
So then you wonder what you did wrong and how to fix it. And all you can do is guess, because the error message was completely silent about whether or not the spreadsheet column structure matched the table structure, or whether any of the spreadsheet's column names where invalid, or whether you were blindly trying to fit a square peg into a round hole.
What you did wrong was to use the wrong tool to import the file. While the Spreadsheet Import Wizard is simple to use, it prevents the user from being alerted to what problems have occurred whenever it fails, it doesn't allow the user to save the import settings, and it's very slow to import the data because the Wizard must parse the data from within the formatted spreadsheet file. Ever notice how much larger an XLS file is than the equivalent CSV (comma-separated values) file? The CSV file is the data, while the XLS file is the data plus the formatting information to display that data.
A better tool -- and the easiest method for beginners to import data with -- is the Import Text Wizard. The easiest method for intermediate users is VBA code for importing data, and for experienced users, it's an append query or make table query. (These other topics for more advanced users will be covered later in this article.)
Whenever the Import Text Wizard tries to import the data file and fails, it will give a slightly more descriptive error message and place information about the records that couldn't be imported into a TableName_ImportErrors table. (The "TableName" portion of the error table name is the name of the table that the data file is being imported into.) Each row that failed to import will be listed, along with the error that occurred and the field name, if only one field failed to import, not the entire record. When the field and row have been identified as not complying with the table structure (and the type of problem this noncompliance presents, such as field truncation when the field contains more than 255 characters and will be imported into a Text field instead of a Memo field), then it's much easier to find a way to fix the data in the column on this row so that it can be imported successfully next time.
Of course, the Text Import Wizard can't import a spreadsheet. It can only import a text file. This text file can be in text (ASCII) format, or tab-delimited format, or even a CSV file. Excel is capable of converting an XLS format file into a text file, so the first step to importing a spreadsheet is to convert the file format to one compatible with the Text Import Wizard.
After converting the spreadsheet into a text file, open the database in Access and select the File -> Get External Data -> Import... menu to open the Import dialog window. Change the "Files of Type" combo box item from the default "Microsoft Access (*.mdb;*.adp;*.mda;*.mde;*.ade)" selection to "Text files (*.txt;*.csv;*.tab;*.asc)" and then navigate to the directory where the text file resides and select the text file to open the Import Text Wizard.
Each page of the Import Text Wizard allows the user to make choices about the data to be imported. These choices are "specifications" that can be saved for the next time this data file is imported, which makes the next import faster, since the choices have already been preselected. To save the choices for next time, make all of the desired selections, then select the "Advanced..." button at the bottom left corner of the Wizard to open the <FileName> Import Specification dialog window. All of the selections made thus far will be displayed in the dialog window. When satisfied with these selections, save them as import specification settings by selecting the "Save As..." button and giving the specification a name.
Whenever the Import Text Wizard fails to import records, check the TableName_ImportErrors table. The three most common problems won't prevent the file from being imported, but will prevent certain rows or certain fields from being imported correctly. Since the row numbers will tell you exactly which rows in the text file are failing, alter the data in the named column on these particular rows in the text file as necessary to get the data to import into your table successfully next time.
|
Problem:
|
Usual Solution:
|
|
Field Truncation
|
Change destination Text field to Memo field.
|
|
Type Conversion Failure
|
Change data type of either source or destination field to match the other.
|
|
Unparsable Record
|
Look for misplaced comma, apostrophe or quotation marks in source record.
|
When you've successfully altered the data in the columns in the indicated rows so that the source data file and destination table match in structure, you'll see a message similar to the following one, indicating a successful import of the data file:
Finished importing file 'C:\Data\MySpreadsheet.txt' to table 'tblImportData'.
Intermediate Users
Intermediate users may use VBA procedures to import data from files. If the data is contained in an Excel spreadsheet, then the TransferSpreadsheet method could be used:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblImportData", "C:\Data\MySpreadsheet.xls", True
As mentioned previously, importing spreadsheets is slower than importing text files, since the data needs to be parsed from the formatting of the file. The TransferText method is recommended for importing a data file after converting the spreadsheet format to text file format:
DoCmd.TransferText acImportDelim, "MySpecName", "tblImportData", _
"C:\Data\MySpreadsheet.txt", True
. . . where MySpecName is the name of the import specification created specifically for this text file.
Expert Users
Expert users can use a query to import data from a file into a database. While an Excel spreadsheet can be imported from a query that uses the Excel driver, it's not the best data format. For the fastest imports, the spreadsheet (XLS file) should first be converted to a text file, so that the Text ISAM (Indexed Sequential Access Method) driver will be used to import the file. The text file can be in text (ASCII) format, or tab-delimited format, or even a CSV file, any of which Excel can convert into from the XLS file format.
(Note: Each of the examples in this article uses a data file with headers, i.e., named columns. If you use a data file without headers, then change the example syntax from HDR=YES; to HDR=NO; so that the data in the first row of the data file won't be assigned as column names for the import.)
Append query
If the data file is to be imported into a table that already exists, then an append query will append new records from the data file into the table. For example, the following query will append all records in the C:\Data\MySpreadsheet.txt file into the tblImportData table of the current database:
INSERT INTO tblImportData
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MySpreadsheet.txt;
Each column of the spreadsheet must match a column in the table in both name and data type, or else that column may not import successfully. If only certain columns are to be imported instead of all columns in the data file, then each column name that needs to be imported should be identified in the SELECT clause of the query. For example, the following query could be used to restrict the imported data to the FirstName, LastName, Address, City and State columns and ignore the EmpID and Zip columns in the text file:
INSERT INTO tblImportData
SELECT FirstName, LastName, Address, City, State
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MySpreadsheet.txt;
If only certain records are to be imported from the data file, then this criteria should be included in the WHERE clause or HAVING clause. For example, to restrict the records being imported to only those which have "Green" as the last name:
INSERT INTO tblImportData
SELECT FirstName, LastName, Address, City, State
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MySpreadsheet.txt
WHERE (LastName = "Green");
It should be noted that the methods mentioned previously for beginners and intermediate users for importing records from data files do not allow any criteria to restrict the records being imported, so they aren't as flexible as a query is to import data. The Import Text Wizard and Import Spreadsheet Wizard do, however, allow one to skip (or ignore) indicated columns when importing records from the data file into a table.
Make table query
A query may create a new table while importing the records from a data file. For example:
SELECT * INTO tblImportData
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MySpreadsheet.txt;
Jet will assign the data type for each field in the new table. Each new field's data type will correspond with the Jet's best guess of the data type of each field in the data file. Always check the data after the import, because sometimes the failure is silent, as when a text column is imported into a numeric data type column because Jet guessed incorrectly on the data type based upon the first few records in the data file. If you don't want Jet to guess on the data type, then use a Schema.ini file to automatically format the imported data. For instructions on how to create and use a Schema.ini file with a query, please see the following Web pages:
http://groups.google.com/group/microsoft.public.access.externaldata/browse_thr ead/thread/c224e0c161d618ca/e1335a172ce29d27?hl=en#e1335a172ce29d27
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcje tschema_ini_file.asp
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/w orkingwithtextfiles.asp
Each of the text fields will be assigned the maximum of 255 characters, regardless of the maximum number of characters that are actually used. No indexes or primary key will be assigned when the new table is created, so another query needs to alter the new table to create them. For example, to assign an AutoNumber primary key to the table created by the query above, the following query could be executed:
ALTER TABLE tblImportData
ADD COLUMN ID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY;
A new column named "ID" will first be added to the table. It will be an autoincrement (Long) data type, which will begin at 1 and increment by 1 for each new entry. The "Primary Key" constraint will be added to the table for the ID column, and this constraint will be named "PrimaryKey."
Update query
A query may update existing data and append new records to a table, as long as both source and destination are tables (or linked tables) within the database, and not queries or external files. Both tables must have primary keys or unique indexes, too. This update/append is often called "merging data" from two tables. It's easy to merge data from an imported text file into the existing table after the import (from the make table query). For example, the make table query above could be executed first, then the following query will update existing records in the tblEmployees table and add any new records that were contained in the text file by assigning a value to every matching column in the table (except an AutoNumber column):
UPDATE tblEmployees AS Tbl RIGHT JOIN tblImportData AS NewData ON Tbl.EmpID = NewData.EmpID
SET Tbl.FirstName = NewData.FirstName,
Tbl.LastName = NewData.LastName,
Tbl.Address = NewData.Address,
Tbl.City = NewData.City,
Tbl.State = NewData.State,
Tbl.Zip = NewData.Zip
WHERE (ISNULL(NewData.EmpID) = FALSE);
The EmpID column is the unique index in both tables that this update query relies on to sync the data in the matching records.
Both the make table and update queries listed above can be executed together in a VBA procedure so that both will be executed sequentially to import the new data and then update the tblEmployees table. For example, if the make table query were named qryImportEmployees and the update query were named qryUpdateEmployees, then the following VBA statements would execute both queries and then delete the temporary table created by the first query:
CurrentDb().Execute qryImportEmployees, dbFailOnError
CurrentDb().Execute qryUpdateEmployees, dbFailOnError
CurrentDb().Execute "DROP TABLE tblImportData;", dbFailOnError
Disadvantages
Executing a query for the import has a few disadvantages. Since SQL scripts aren't supported by Jet, sequential queries (such as the make table query and update query examples listed above) need to be executed by a custom VBA procedure, as provided in the example above. And whenever an import query fails, the error message may not be as descriptive as those for the TableName_ImportErrors table for the Import Text Wizard. However, expert users are familiar with the many ways a query can fail and can easily determine what is causing the problem.
Summary
Avoid the most common Import Spreadsheet Wizard error by using a better, more informative method to import the data file. The advantages of the Import Text Wizard are that it is the easiest, most reliable method for beginners, and it allows one to create a set of specifications to import the file to speed up importing the data file on later occasions, whereas the Spreadsheet Import Wizard does not. It's much faster to import the data file with the Import Text Wizard and whenever the import fails, the user can find out why by looking in the TableName_ImportErrors table. The disadvantages are that it requires manual input and only allows one data file to be imported at a time.
Intermediate users can use VBA code to import data files. While the TransferSpreadsheet method can transfer spreadsheets directly into the database, the TransferText method is the preferred method of importing data files after the spreadsheet format is converted to text file format, since the TransferText method is faster and can utilize an import specification.
Expert users can use a query to either append data into an existing table or create a new table. If a new table is created from the query, then additional queries need to be executed to modify the table so that a primary key or other indexes are assigned and default column properties can be modified as necessary. A Schema.ini file may also be used to coerce the data types or the column sizes for the data imported into the newly created table. The advantages of using queries for importing text data files are that they are faster than importing spreadsheets (by any available method) and they are much more flexible in which records and which columns are added to the table. The disadvantage that only one query can run at a time is alleviated by the usage of custom VBA procedures to automatically execute queries sequentially. The other disadvantage that the occasional less descriptive error messages presented whenever the import fails can be mitigated by a more experienced user who knows what causes each type of error and can easily track any of them down.
You can read about your favorite expert contributors.
Copyright © 2005 - 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 9 Dec. '05: 
|
