Wrong format while importing

[ABEone]

Hi,

i experience the following problem:

When I import a xls sheet into an existing table in Access, which has all columns formatted as text, sometimes it goes wrong.

When in one of the xls sheets' columns there is no text for a number of rows, but just zeros (as text) then Access believes it is a number format and will not import any text in later rows. So Access checks according to the first few rows wether or not it is an interger or text. How can I make sure it only sees it as text?

Thanks in advance.

Abe

 

[Pat Hartman]

Unfortunately, we cannot control this as we can for the importing of text files. Access insists on "assuming" the proper data type. Two alternatives:
1. add a dummy row to the top of the spreadsheet that contains data in the format you want Access to assume.
2. Instead of creating a new table with the import, append to an existing table. This allows you to precisely control the data formats. Of course, this method will produce import errors if the Excel sheet contains invalid data from Access' viewpoint.

 

[ABEone]

2. Instead of creating a new table with the import, append to an existing table. This allows you to precisely control the data formats. Of course, this method will produce import errors if the Excel sheet contains invalid data from Access' viewpoint.

This doesn't work either. I have an existing table, and that is when it goes wrong. The target column is formatted as text. However when the first so-many entries of my source are "0", and then some other text, I get a import failure, because it says it expects numbers! And even while the format is text. Furthermore, it leaves out the text from the source file.

 

[RV]

This doesn't work either

Define an import specification.
This enables you to control the data formats, as Pat mentioned before.

RV

 

[Pat Hartman]

You can't define datatypes for columns for a spreadsheet in the input spec. That's the problem.

The target column is formatted as text. However when the first so-many entries of my source are "0", and then some other text, I get a import failure, because it says it expects numbers! And even while the format is text

post a db with the table you have defined and a spreadsheet with enough rows to cause the problem and we'll look at it.

 

[IgorB]

Built another field as a last field in your table with text data type.
Import all data manually or using VBA, and then just add update statement something like this

DoCmd.RunSql "Update YourExistingTableName Set NewFieldWithTextDataType=ExistingFieldWithNumberDataType"


Use your new field for all your purposes.

Igor.

 

[Pat Hartman]

IgorB, did you read the original problem? ABEone cannot import the table due to data type issues so how is he to follow your directions?