Importing data from excel to access, with specific format!

[candela32]

Hello,
i'm trying to import an excel sheet in an access table.
My problem is how to import 2 text column that access always try to import as numeric, and so raise me errore when find out a non numeric value.
What i've tryed is:
1) format the excel sheet columns as text.
2) building a table in access with correct format column and import excel sheet into it.

None of them works... always same errors, in the same columns, at the same rows!

Any idea!
thenks!

 

[MazeWorX]

Access creates the data type based on the data in the first row of the spreadsheet. You can precede the data in the first row with an apostrophe('9999) which will force access to import as text, note excel doesn't display the apostrophe only the data. Do a search and you ll find macros already written to perform this task. Alternatively you can import the data as a text file where you can then define the data types in the specification file

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[candela32]

the problem is that the 2 columns that have import error, are empty at the first line, so if i put ' inside them the table i import has an empty value, and all other field with no value are NULL! :<

probably a macro or sub is the way!

 

[SkipVought]

the problem is that the 2 columns that have import error, are empty at the first line

DELETE that row!!!

Convert ALL the data in the column to TEXT, via the leading APOSTROPHY.

The import manager analyses the first 8 rows to determine the data type.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[candela32]

sorry... i mean, that the first row have the 2 column value empty, not the row is empty.

the fact is that these 2 columns have mainly numeric value, but not only so the non numeric value is about 25 row position... or later..

 

[SkipVought]

these 2 columns have mainly numeric value, but not only so the non numeric value is about 25 row position

You cannot have it both ways!!!

Your data must be all text!!!

You must CONVERT all your NUMBERS to TEXT as MazeWorX suggested.

You will have NO NULL values!!! The EMPTY cells MUST be a zero length STRING. APOSTROPHY does just fine.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[candela32]

sorry for my english.

of course i have and i want these 2 columns to be text columns.
and i have both set in access and in excel the type of these columns to text.
But when i import in access it give me errors in importing, i think this is caused because access try to import them as a number cause there are just few values that are not numeric (refferring to Value not Type that is set as text).

If i put ' in first line of these 2 columns, it import the sheet correctly, reffering to TYPE, i mean the two columns are imported as text ones... but the first row have different values from the other ones that have empty values:
-the first have empty string!
-the other just NULL value!

 

[SkipVought]

-the first have empty string!
-the other just [highlight red][white]NULL[/white][/highlight] value!

[highlight red][white]That[/white][/highlight] may be a problem. Every value in these two columns, EVERY VALUE, must be TEXT.

By the way, simply formatting the columns in Excel as TEXT does absolutely NOTHING!. The DATA MUST CHANGE from NUMERIC to TEXT. Hence the APOSTROPHY.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

How about...

Create the Access table first.
Import to the existing table.

Now, regardless of the data in the spreadsheet, you will
be importing to a text field in your table.


Randy