Avatar of zimmer9
zimmer9
Flag 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
Microsoft AccessMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Randy Downs

8/22/2022 - Mon
Randy Downs

Sounds like it's not finding ImportedFile
ImportedFile = "U:\TestFile.XLS"
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.
Randy Downs

Try it using the literal file name. The variable doesn't seem to work

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", "U:\TestFile.XLS", True
Your help has saved me hundreds of hours of internet surfing.
fblack61
zimmer9

ASKER
Good suggestion. I tried the 1 statement and I get the same error as I do when I use the variable.
Randy Downs

Try it with a local file (e.g., copy file to C: drive)

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

ASKER
They took away our access rights to the C: drive at work.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Randy Downs

OK whatever drive you have access should work. Is that the U: drive?
zimmer9

ASKER
Yes, I have put the XLS file on the U: drive to which I have access.
Randy Downs

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Randy Downs

See if you can run something like this from command line

excel.exe "U:\TestFile.XLS"
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
Randy Downs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
zimmer9

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

This works.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
zimmer9

ASKER
And this works too:

DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttributes", "U:\GetDoc.XLS", True, "A1:K65535"
Randy Downs

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
Randy Downs

You can try this but you probably need to be user dbo

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes", ImportedFile, True
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Randy Downs

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...)