Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to resolve an error importing an Excel file into a SQL Server 2005 table: " is not a valid name. Make sure that it does not include invalid characters or punctuation?

I am creating an Access applicaton with an APD type file using Access as the front end and SQL Server 2005 as the back end database.

I use the following commands to import an Excel file into a SQL Server table.

ImportedFile = "U:\TestFile.XLS"
   
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", ImportedFile, True

I get the following error:

Run time error '3125'
" is not a valid name. Make sure that it does not include invalid characters or punctuation
and that it is not too long.

Do you know how I can resolve this error?
Is the problem the input data?

I provided 2 input records in the attached Excel file.

the SQL Server table fields are defined as follows:
 
Field Name                         Data Type
--------------                        -----------------
MailID                                nvarchar(255)
PartyID                              nvarchar(255)
Account                            nvarchar(255)
DocumentDate                 datetime
ScanDate                          datetime
DocumentType                 nvarchar(255)
Mailroom_Address1           nvarchar(255)
Mailroom_Address2           nvarchar(255)
Mailroom_Address3           nvarchar(255)
Mailroom_Address4           nvarchar(255)
Mailroom_Address5           nvarchar(255)
TestFile.xls
Avatar of Randy Downs
Randy Downs
Flag of United States of America image

Sounds like it's not finding ImportedFile
ImportedFile = "U:\TestFile.XLS"
Avatar of zimmer9

ASKER

I renamed the file to generate a file not found error.
Then I get:

Runtime error 3011
The Microsoft Jet database engine could not find the object 'U:\TestFile.XLS'.
Make sure the object exists and that you spell its name and the path name correctly.
Try it using the literal file name. The variable doesn't seem to work

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", "U:\TestFile.XLS", True
Avatar of zimmer9

ASKER

Good suggestion. I tried the 1 statement and I get the same error as I do when I use the variable.
Try it with a local file (e.g., copy file to C: drive)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", "TestFile.XLS", True
Avatar of zimmer9

ASKER

They took away our access rights to the C: drive at work.
OK whatever drive you have access should work. Is that the U: drive?
Avatar of zimmer9

ASKER

Yes, I have put the XLS file on the U: drive to which I have access.
Apparently your code doesn't have access to the U: drive that's why I was looking for another. You could also try making everything lower case but it shouldn't matter if you are in a Windows environment.
See if you can run something like this from command line

excel.exe "U:\TestFile.XLS"
Avatar of zimmer9

ASKER

Yes that works.
The program generates a run time error of 3125 when the file is there.
When the file is not there the program generates a runtime error of 3011.
ASKER CERTIFIED SOLUTION
Avatar of Randy Downs
Randy Downs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zimmer9

ASKER

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True

This works.
Avatar of zimmer9

ASKER

And this works too:

DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttributes", "U:\GetDoc.XLS", True, "A1:K65535"
Yeah and your variable can probably go back in as well. Just need to get access to your table.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", ImportedFile , True
You can try this but you probably need to be user dbo

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes", ImportedFile, True
Try using  dbo_tblGetDocumentAttributes rather than dbo.tblGetDocumentAttributes


https://www.experts-exchange.com/questions/27926580/How-to-avoid-error-when-importing-Excel-2003-file-into-Access-2003-database-is-not-a-valid-name-Make-sure-that-it-does-not-include-invalid-characters-or-punctuation.html



DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo_tblClientLink", ImportedFile, True
(The "." may not be allowed)

Also check for trailing or leading space bar characters in the Excel field names.
(Some users add in these spaces to simulate indents...)