alter excel data before import?

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Hi,

My question is how do I alter the data in an excel spreadsheet before
importing into access. Specifically, how do I delete criteria meeting rows
from an excel spreadsheet?

There is an application that my department and another use to generate
some data. This data is exported automatically into an excel file. I use
the following code to import the data into Access:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"tblUploadRecordCount", "U:\Batching Files\BatchControl.xls", True

The problem is that the data for my department uses a unique numeric
identifier for records, where the other department uses random alpha numeric
system. So, what I want is to keep the rows where the data in column A is
like 000000 (a number exactly six digits in length), and strip off anything
that has alpha characters or is not exactly 6 digits in length.

Thanks,
Sean
 
Import the EXCEL file's data into a temporary table, then use an Append
Query to copy the desired records from the temporary table into your
permanent table. You could use something like this as the "Criteria:"
expression for the "ColumnA" field:

Like "######"

This will append only the records that have six-character values that are
all numbers.

Then you can run a delete query to empty the temporary table.
 
Hi Sean,

It's simpler to import all the data and then dump what you don't want.
With luck you'll be able to link to the spreadsheet rather than import
it (acLink); otherwise import it to a temporary table. Then use an
Append query to append only records where the first field meets
thecriterion
Like "######"
to your "real" table.
 
Ah, yes! thanks guys. I started to do it that way but I kept running into
errors because of the other departments data. Sometimes they have duplicate
identifiers, sometimes they have none, they have strange characters that
don't seem to import nicely, etc. I'll see if I can work around it though.

thanks
Sean
 
Since you don't want the other department's data, you can just ignore
the import errors it raises.

If the identifier column in the Excel sheet contains a mix of number and
alphanumeric values, there can be trouble importing or linking it.

Basically, if you're importing from Excel, there must be at least one
cell in the top few rows that cannot be interpreted as a number. That
will cause Access to import the column as a text field. (So sometimes
it's a good idea to add a dummy record at the top just to ensure that
this happens).

But if you're linking, the rules are different: if there is any cell in
the top few rows that contains a number, the entire column will be
linked as a numeric field regardless of the non-numeric values it
contains.

You can force Excel (and therefore Access) to interpret numbers as text
by either putting an apostrophe before them
999 - number; '999 - text
or by using VBA to append an empty string, along these lines:
Dim C As Excel.Range
For Each C in Selection.Cells
C.Formula = C.Formula & ""
Next
 
Thank you.
Shawn

John Nurick said:
Since you don't want the other department's data, you can just ignore
the import errors it raises.

If the identifier column in the Excel sheet contains a mix of number and
alphanumeric values, there can be trouble importing or linking it.

Basically, if you're importing from Excel, there must be at least one
cell in the top few rows that cannot be interpreted as a number. That
will cause Access to import the column as a text field. (So sometimes
it's a good idea to add a dummy record at the top just to ensure that
this happens).

But if you're linking, the rules are different: if there is any cell in
the top few rows that contains a number, the entire column will be
linked as a numeric field regardless of the non-numeric values it
contains.

You can force Excel (and therefore Access) to interpret numbers as text
by either putting an apostrophe before them
999 - number; '999 - text
or by using VBA to append an empty string, along these lines:
Dim C As Excel.Range
For Each C in Selection.Cells
C.Formula = C.Formula & ""
Next
Click to expand...
 
Back
Top