Help in Importing from EXcel

[Tom]

I have several diferent sources of data incuding Excel spreadsheets and other
non access databases that I will be importing into an access 2007 database.
The issue is that part numbers are not uniform. Some have dashes in the part
numbers and others do. I am looking at the easiest way to import the data and
then once it is imported to make sure the required dashes are added to the
data.

Any suggestions? I thought of using an input mask on the field and then
importing or appending to that table but that didn't work. Can I use an
update query to apply the correct format to the imported data? How do you
apply an input mask to data store in a table?

Any sugestions aremuch appreciated

Thanks

 

[Douglas J. Steele]

An input mask only makes a difference when you're keying values in. It does
nothing to data already in the table.

Let's assume that your values are supposed to be telephone numbers, and some
are xxxxxxxxxx, some are xxx-xxx-xxxx and some are (xxx) xxx-xxxx and you
want them all to be (xxx) xxx-xxxx. You could use an update queries along
the lines of:

UPDATE MyTable
SET [MyField] = Format(Replace([MyField], "-", ""), "\(###\) ###\-####")
WHERE Left$([MyField], 1) <> "("

Hopefully that will point you in the correct direction.

 

[Tom]

Doug I am not sure what I am doing wrong
my table name is import and the field name is field1

UPDATE Convert:
SET [field1] = Format(Replace([field1], "-", ""), "\(###\) ###\-####")
WHERE Left$([field1], 1) <> "("

I tried it without the update Convert:
and I keep getting the same error...invalid syntax and it doesn't seem to
like the
WHERE Left$([field1], 1) <> "(" at least the where is what is highlighhted

i have field1 formatted as text
I made this an update query and in the update to field of the field1 column
I put in your code.

I appreciate the guidance




any suggestions?

 

[Douglas J. Steele]

The Update statement expects the name of the table you're updating:

UPDATE import
SET [field1] = Format(Replace([field1], "-", ""), "\(###\) ###\-####")
WHERE Left$([field1], 1) <> "("

That's the SQL for the query, not something you put into the Update field.

To create the query, you'd add table import to the query, and drag field1
into the grid.

You'd put Left$([field1], 1) as a computed field (i.e.: it would go into a
blank cell on the Field row), and you'd put <> "(" as the criteria under
that cell. (You might

You'd put Format(Replace([field1], "-", ""), "\(###\) ###\-####") in the
Update To row under field1.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug I am not sure what I am doing wrong
my table name is import and the field name is field1

UPDATE Convert:
SET [field1] = Format(Replace([field1], "-", ""), "\(###\) ###\-####")
WHERE Left$([field1], 1) <> "("

I tried it without the update Convert:
and I keep getting the same error...invalid syntax and it doesn't seem to
like the
WHERE Left$([field1], 1) <> "(" at least the where is what is
highlighhted

i have field1 formatted as text
I made this an update query and in the update to field of the field1
column
I put in your code.

I appreciate the guidance




any suggestions?

I have several diferent sources of data incuding Excel spreadsheets and
other
non access databases that I will be importing into an access 2007
database.
The issue is that part numbers are not uniform. Some have dashes in the
part
numbers and others do. I am looking at the easiest way to import the data
and
then once it is imported to make sure the required dashes are added to
the
data.

Any suggestions? I thought of using an input mask on the field and then
importing or appending to that table but that didn't work. Can I use an
update query to apply the correct format to the imported data? How do
you
apply an input mask to data store in a table?

Any sugestions aremuch appreciated

Thanks

 

[susan roads]

I have several diferent sources of data incuding Excel spreadsheets and
other
non access databases that I will be importing into an access 2007
database.
The issue is that part numbers are not uniform. Some have dashes in the
part
numbers and others do. I am looking at the easiest way to import the data
and
then once it is imported to make sure the required dashes are added to the
data.

Any suggestions? I thought of using an input mask on the field and then
importing or appending to that table but that didn't work. Can I use an
update query to apply the correct format to the imported data? How do you
apply an input mask to data store in a table?

Any sugestions aremuch appreciated

Thanks

TOM