"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!)
BTW, I haven't tried the Linked Table option, because these are discrete files for discrete jobs stored in discrete locations - I may even violate an ISO audit in an area or three by exporting data to a common location...
And in business process terms, adding that extra export step is unnecessary complication / "opportunity for error"... exacerbated by my lack of experience with it.
I'd suspect you have one or more characters which are invalid for JET SQL.. bear in mind the duff character may be a non printing or invisible character which is outside the permitted values for columns names (AFAIK: a-z A-Z 0-9 plus spaces).
it could be a line feed or carriage return or anything else.
personally for an import I tend to 'just' import the data sans columns names.
if its a complex data import I prefer to take the performance hit and do the import as a VBA process rahter than a file import.. this especially applies if there are PK relationships or validations that need to be done. by doing it as part of a VB process its easier to control and handle errors, and sometimes make assumptions about missign or incorrect data, you can also import the whole file, but store records with problems in a separate holding tank/table which then allows the users to tidy up their own data.
http://i792.photobucket.com/albums/y.../miniDGR-0.jpg I've taken part in the Distinguished Gentlemans ride, and although I enjoyed the day the main focus was to raise awareness of Prostrate Cancer and raise some funds for research. Many thanks to the many sponsors who helped me raise some £360 towards this cause, its much appreciated and rather humbling.
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.
I;ve seen this before when importing or transforming from Excel. It usuall happens when a user saves some data in the extra cells, and is it is not deleted properly, thus, Access still considers it has values, but are Nulls.
I didn't encounter it on any other imports...
I'm thinking that as a precaution, I could select a slew of columns to the right of my last "real" column and hit "delete" to just wipe anything out - and maybe the same thing below my final row in my spreadsheet, just to be sure.
I don't think there's anything I can do programatically in the import...
The real version is a form using the DoCmd.TransferSpreadsheet command, but there's no "ignore nonprinting characters" or anything like that. And even then, a space wouldn't count if that was my troublemaker.