If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Import problem with Excel spreadsheet
I am importing an Excel spreadsheet with item data from our mainframe. The
warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. |
#2
|
|||
|
|||
Import problem with Excel spreadsheet
Try importing the excel file to a new table, rather than setting up a table
beforehand. Once you have the data, you can rename the table. This would avoid any problems with data types. "JKnope" wrote: I am importing an Excel spreadsheet with item data from our mainframe. The warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. |
#3
|
|||
|
|||
Import problem with Excel spreadsheet
I did try that as well, but because the first several rows had a warehouse
value which was numeric, it assumed the field to be a "number" data type. As a result, none of the rows with a text warehouse value are showing. With no changes made - I let the wizard handle the import, and I receive over 6,000 errors. "mnature" wrote: Try importing the excel file to a new table, rather than setting up a table beforehand. Once you have the data, you can rename the table. This would avoid any problems with data types. "JKnope" wrote: I am importing an Excel spreadsheet with item data from our mainframe. The warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. |
#4
|
|||
|
|||
Import problem with Excel spreadsheet
Some possible approaches, in no particular order:
-Export the data from Excel to a CSV file, then import that. -Put a dummy row of data at the top of the Excel table, with values in the text fields that cannot be construed as numbers. -Prefix the values in Excel with apostophes, e.g. '0033 rather than 0033. This forces Excel and Access to treat them as text. The apostrophes aren't displayed in the Excel worksheet and are stripped off during the import process. -Tweak the registry settings as described in http://www.dicks-blog.com/archives/2...ed-data-types/ On Wed, 8 Feb 2006 09:50:28 -0800, JKnope wrote: I am importing an Excel spreadsheet with item data from our mainframe. The warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#5
|
|||
|
|||
Import problem with Excel spreadsheet
Set up a dummy first row which will "set" all the data types correctly. You
can always delete that record once everything imports. "JKnope" wrote: I did try that as well, but because the first several rows had a warehouse value which was numeric, it assumed the field to be a "number" data type. As a result, none of the rows with a text warehouse value are showing. With no changes made - I let the wizard handle the import, and I receive over 6,000 errors. "mnature" wrote: Try importing the excel file to a new table, rather than setting up a table beforehand. Once you have the data, you can rename the table. This would avoid any problems with data types. "JKnope" wrote: I am importing an Excel spreadsheet with item data from our mainframe. The warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. |
#6
|
|||
|
|||
Import problem with Excel spreadsheet
On Wed, 8 Feb 2006 09:50:28 -0800, JKnope
wrote: I am importing an Excel spreadsheet with item data from our mainframe. The warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. Since formats in Excel are advisory not enforced, Access must guess at the appropriate datatype based on the first few rows of the imported data. As you have seen, it's often going to guess wrong! Two suggestions: *link* to the Excel spreadsheet rather than importing, and run an Append query into your table. If that doesn't help, put a "dummy" row at the top of the spreadsheet with an unambiguous text value ( "TEXT" say) in the column which you wish to import into a text field. John W. Vinson[MVP] |
#7
|
|||
|
|||
Import problem with Excel spreadsheet
Insert a dummy record in Excel, somewhere within the 1st 8 records. Fill all
columns of this record with a text value (ex: "DeleteMe"). That should force the Access import wizard to treat every column as a text field and allow the import to proceed without interruption. Then delete that record after the import. (Not particularly elegant, I know.) Another possibility to consider would be to save the Excel file as comma delimited and then read that text file into Access. Importing text files allows much greater control over field types etc. than the assumptions that are built into spreadsheet imports. Importing text files also allows you to have sets of saved Import specifications. HTH, -- George Nicholson Remove 'Junk' from return address. "JKnope" wrote in message ... I did try that as well, but because the first several rows had a warehouse value which was numeric, it assumed the field to be a "number" data type. As a result, none of the rows with a text warehouse value are showing. With no changes made - I let the wizard handle the import, and I receive over 6,000 errors. "mnature" wrote: Try importing the excel file to a new table, rather than setting up a table beforehand. Once you have the data, you can rename the table. This would avoid any problems with data types. "JKnope" wrote: I am importing an Excel spreadsheet with item data from our mainframe. The warehouse field in this spreadsheet may contain numbers or alpha characters. As a result, I set the field data type in my table as "text". When I import, I continue to get 'type conversion failure' errors on any records that begin with numbers, then contain text. An example is "00LE" is a value within the spreadsheet. Rather than getting any data in my table, it doesn't import at all. Numeric warehouse values are imported properly, any that contain text are appearing in the errors table and do not import. I've formatted the entire column in the Excel spreadsheet to be text, imported, no change. Any advice would be greatly appreciated. I've worked with Access for 10+ years, and I have never seen this problem before. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I import an Excel Spreadsheet from a scanner? | greenbob | General Discussion | 2 | January 4th, 2006 06:06 PM |
import excel spreadsheet | Steve | General Discussion | 1 | January 14th, 2005 09:21 PM |
Can I import a Word table to an Excel spreadsheet? | Liz | Worksheet Functions | 1 | November 25th, 2004 04:41 PM |
Importing Excel spreadsheet into Access - data type problem | Andrew Good | General Discussion | 2 | August 27th, 2004 09:39 AM |
Import data into EXISTING Excel Spreadsheet | Wesley H | Links and Linking | 1 | April 9th, 2004 10:11 AM |