Importing to access from excel

[KIM]

I understand that access uses the first 15 rows of an imported excel sheet to
determine whether the access field is numerical or text. I have a worksheet
with a date column, and columns that contain both numbers and text entries
(in the form of less than values e.g.<1). Therefore the date column cannot
be changed to text or number otherwise it looses the correct format. And
although the numbers can be changed to text in excel they are only recognised
as numbers in access. The only way I have found to get the all the
information across from excel into access without error values (e.g.#NUM!) is
to enter ' in front of all entries. I have done this manually (i.e. entering
each cell separately) but this is not practical for the large amount of data
I have. Is there any other way round the problem? If not is there any other
way of getting ' in front of every entry without having to do it manually?
If I do have ' in front of every entry can I still calculate average values
for the columns?

Thank you

[Myrna Larson]

If you put an apostrophe in front of all of your entries, they become text.
AVERAGE ignores TEXT, so no, you won't be able to get an average.

As for as your real problem, you DO have a mix of numbers and text. "<1" is
not a number. You would have to adopt some convention for a true number that
indicates "less than 1", perhaps -.0001 or something similar. Then all of your
Excel entries can be represented as numbers and imported correctly into
Access.

On Tue, 22 Feb 2005 08:15:13 -0800, KIM <[email protected]> wrote:

>I understand that access uses the first 15 rows of an imported excel sheet to
>determine whether the access field is numerical or text. I have a worksheet
>with a date column, and columns that contain both numbers and text entries
>(in the form of less than values e.g.<1). Therefore the date column cannot
>be changed to text or number otherwise it looses the correct format. And
>although the numbers can be changed to text in excel they are only recognised
>as numbers in access. The only way I have found to get the all the
>information across from excel into access without error values (e.g.#NUM!) is
>to enter ' in front of all entries. I have done this manually (i.e. entering
>each cell separately) but this is not practical for the large amount of data
>I have. Is there any other way round the problem? If not is there any other
>way of getting ' in front of every entry without having to do it manually?
>If I do have ' in front of every entry can I still calculate average values
>for the columns?
>
>Thank you

[Julia Osborne]

Confession up front - I'm no expert but I do spend quite some time dumping values from Excel into Access. There might be some useable ideas in some of these work arounds which I have used in the past.

You could format the cells in Excel with a custom format to automatically put an apostrophe in front of the value.

You could add a extra colums to the sheet to copy the values before the apostrophe and then still do the average on them. Another column could then affix an apostrophe to the values. That way you don't lose any of the data, you can import all the columns into Access and then use Access queries to modify them as appropriate.

You could get them all formatted as text and once they are imported into Access run a query to derive another field which you put the value into if it doesn't have a < sign in it or else leaves a blank (try an Fieldname:=IIF([field name]=(like"*"&"<"&"*"),"",[Field name]) it might work (can't quite remember if you can use the like clause in this way) or the solution will be very similar), you could then do another query including this one and do it as a make table query and then change the field type of the new field to numberic and do the sums in Excel or Access

If you want to concatenate values it can be done in Excel or Access, or else its always worth remembering that if you open the file in Notepad/wordpad, and resave it as text that you can open this file in Excel (file/open routine) and use the wizard to put the column lines where you want them.

[KIM]

I think you can average a TEXT entry in excel as long at it has a number in -
I asked a question about 'averaging columns with less than (<) text entries'
and I have had replies letting me know the correct formula which seem to work
on entries with ' infront of it too. Unfortunately its is not practicle
adopt some convention for a true number that indicates "less than 1" because
I need the exact data. Also I have a large amount of data so it would take a
long time to go through and change all the less than signs plus not all the
figures are less than 1 so it could get all very confusion trying to remember
what the convention was.

I am assuming that i would therefore require a macro or something to put the
' infront of every entry?

Thank you for your help though

Kind regards

Kim

"Myrna Larson" wrote:

> If you put an apostrophe in front of all of your entries, they become text.
> AVERAGE ignores TEXT, so no, you won't be able to get an average.
>
> As for as your real problem, you DO have a mix of numbers and text. "<1" is
> not a number. You would have to adopt some convention for a true number that
> indicates "less than 1", perhaps -.0001 or something similar. Then all of your
> Excel entries can be represented as numbers and imported correctly into
> Access.
>
> On Tue, 22 Feb 2005 08:15:13 -0800, KIM <[email protected]> wrote:
>
> >I understand that access uses the first 15 rows of an imported excel sheet to
> >determine whether the access field is numerical or text. I have a worksheet
> >with a date column, and columns that contain both numbers and text entries
> >(in the form of less than values e.g.<1). Therefore the date column cannot
> >be changed to text or number otherwise it looses the correct format. And
> >although the numbers can be changed to text in excel they are only recognised
> >as numbers in access. The only way I have found to get the all the
> >information across from excel into access without error values (e.g.#NUM!) is
> >to enter ' in front of all entries. I have done this manually (i.e. entering
> >each cell separately) but this is not practical for the large amount of data
> >I have. Is there any other way round the problem? If not is there any other
> >way of getting ' in front of every entry without having to do it manually?
> >If I do have ' in front of every entry can I still calculate average values
> >for the columns?
> >
> >Thank you
>
>

[Julia Osborne]

No you shouldn't need a macro

To format numbers as text Select Format cells select 'Number' tab format then select 'text' category. This will treat even numbers as text. You can do this on a whole column and you should still be able to a lot of sums on them