Error in data import? It's worked using this template several times prior!

[geolemon]

Import text wizard says:

"The first row contains some data that can't be used for valid Access field names. In these cases, the wizard will automatically assign valid field names"

I'm banging my head on this one, here's why:
I've been importing files using this process and data format, with success!

I created a temporary table in Access to facilitate my import process (It's all text columns, I perform transformations later).

And I created a template in Excel to correspond to the table - essentially it's just column headers:
In Excel, I've been opening files of work that we've done for a particular customer, and rearranging the data to conform to my template. Then, each job's data is ready for import!

So in other words - the data that I started with is common format, and I'm using my "template" to create files of common format that correspond to the temp table.

And
This worked for 3 or 4 of the files. No problem.

The puzzling thing is that all of a sudden, one of my files gave me an error last night:

"The first row contains invalid data"

Or something along those lines.

So after sleeping on it, this morning I decided to try working around the issue by exporting my Excel as a tab-delimited file.

Now, the wizard is throwing the exact error message above...
...And although THIS way at least I can get through the wizard to the end and click a "Finish" button - it throws a "Property not found" error, and then "An error occurred trying to import file [my file path]. The file was not imported."
I'm very puzzled!

Particularly - how could this work for three files having exactly the same header row, and suddenly fail now?
Exported to text, and still an issue?

(worse - I need to develop this into a repeatable process to pass on to an administrative user!)

[geolemon]

PS-
I haven't explored the "linked tables" option because it doesn't seem to be set up for the purpose of avoiding errors like these, and I'm not familiar with it. It also creates additional complexity (read: "opportunity for error").
I'm also concerned that taking this data (currently stored discretely per-job in appropriately distinct locations) and exporting it to a common location would potentially violate something in ISO processes..

[geolemon]

"Method 'Columns' of object 'IImexGrid' failed

is the error message I originally got directly importing this one particular Excel file, followed by

The first row contains some data that can't be used for valid Access field names. In these cases, the wizard will automatically assign valid field names.

The header row is as follows:
RefID
DNP
CustPN
BOMPN
PurchasedPN
PNSource
Manufacturer
Description

[geolemon]

Eureka!!!!

I decided to just let it fly - import it into a NEW table, and I found something:

It was trying to import two extra columns (to the right of the ones I listed), and 30 or 40 extra rows of data (all blank cells, I presume from below the rows that were actually populated - although when I opened the table they appeared at the top.

I deleted the extra rows and columns, and was able to insert without problem into my "real" temp table and finish the processing routine from there.

So, the real question is this:
How does Access know what rows/columns to import?
I assumed it was just based on what was filled in, if not explicitly specified.

I don't really have a means of explicitly specifying it in this process I'm trying to develop, so it would really help to understand the logic Access uses when it isn't explicitly given.

Thanks in advance for insight!

[Stewart Ross]

Hi geolemon. I'm glad this worked out for you in the end.

Access uses Excel's internal properties (such as the LastCell address) to determine the overall range imported, as it is the rectangular region starting at range A1 and ending at the last cell that is imported. From the last column address Access can work out what cells to read in the first row as field names.

It would appear that Access found values it could not accept beyond the columns you expected. A cell looking blank does not necessarily mean it is empty! It could contain any non-printable chracter, or spaces for instance, and you would not notice it or know it was there unless you selected the whole spreadsheet to see what the actual range was (using Crtl-End to go to the last cell).

Where the data imported has a header row it defines the field names created by Access. The ones you quote in your post are fine, and Access will normally change certain invalid characters that cannot be used in field names to ones it can accept (for instance, the period character, as in 'ABC.DEF', is I think changed to an underscore, as in 'ABC_DEF'.

Beyond that it is not really possible to speculate without seeing the sheet directly.

-Stewart

[geolemon]

So possibly there was a space or other non-visible character an those columns, somewhere?

Thinking "I need to make this a repeatable process", then you'd speculate with me that either of these precautions would work, right:

Either one:

• Highlight "a good number" of columns to the right of "Description" and press "Delete, and highlight a "good number" of rows below my last completed row and press "Delete" - to clear alll those cell's contents
• Highlight the range of cells I want to import, in the columns I want, "copy" and "paste" them into a new, virgin worksheet by clicking on cell A1 and doing a "paste" (or "paste values")

I've written a form for a user to browse to an Excel file, populates a text box with the path, and have an "upload" button to DoCmd.TransferSpreadsheet...
I wonder if I should use the "range" variable in that command to limit the spreadsheet only to those columns? The problem that I see with that is if I state an explicit range like that, if Access will then try to import all 65536 rows?

One other annoyance is that when Access does try to import "extra" rows, it throws a primary key "duplicate records" error. Such an error would scare an end user silly - and certainly even with me, it causes me to go back to the Excel worksheet, reorder my data and perform a search for duplicate keys to make sure I don't have any! THEN I know I can ignore it, but it breaks a process that an end user would be expected to handle.