Number formats not numbers

[JoHickey]

I have a spreadsheet that needs to be imported every week. It has several
number fields formatted for General Number. When I import this spreadsheet
into Access, some of the fields come in as number, but most of them come in
as text fields. (They are all formatted identically in Excel) I need the
import to be as seamless as possible, so changing the fields in the table
every week is not an option. Any ideas?

 

[Jeff Boyce]

Turn it into a two-step process.

First, import the Excel spreadsheets into a "temporary" table.

Second, use queries to "parse" the import data into your permanent tables,
changing the data type when needed, using functions like CInt() to convert
to Integer...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JoHickey]

Thanks Jeff!

I'll give it a whirl!

Turn it into a two-step process.

First, import the Excel spreadsheets into a "temporary" table.

Second, use queries to "parse" the import data into your permanent tables,
changing the data type when needed, using functions like CInt() to convert
to Integer...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I have a spreadsheet that needs to be imported every week. It has several
number fields formatted for General Number. When I import this spreadsheet
into Access, some of the fields come in as number, but most of them come in
as text fields. (They are all formatted identically in Excel) I need the
import to be as seamless as possible, so changing the fields in the table
every week is not an option. Any ideas?

As a possibly simpler alternative to Jeff's two-step approach, you may
be able to get by with a slightly different two-step approach: Link to
the spreadsheets (using File... Get External Data... Link) rather than
importing, and then run an Append query from the linked data.

John W. Vinson[MVP]