Solved

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?

Posted on 2013-01-03
17
701 Views
Last Modified: 2013-01-03
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
0
Comment
Question by:zimmer9
  • 10
  • 7
17 Comments
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742741
Sounds like it's not finding ImportedFile
ImportedFile = "U:\TestFile.XLS"
0
 

Author Comment

by:zimmer9
ID: 38742752
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.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742765
Try it using the literal file name. The variable doesn't seem to work

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

Author Comment

by:zimmer9
ID: 38742773
Good suggestion. I tried the 1 statement and I get the same error as I do when I use the variable.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742785
Try it with a local file (e.g., copy file to C: drive)

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

Author Comment

by:zimmer9
ID: 38742796
They took away our access rights to the C: drive at work.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742798
OK whatever drive you have access should work. Is that the U: drive?
0
 

Author Comment

by:zimmer9
ID: 38742800
Yes, I have put the XLS file on the U: drive to which I have access.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742806
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.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742810
See if you can run something like this from command line

excel.exe "U:\TestFile.XLS"
0
 

Author Comment

by:zimmer9
ID: 38742812
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.
0
 
LVL 29

Accepted Solution

by:
Randy Downs earned 500 total points
ID: 38742825
OK you get the same error with the variable ImportedFile so the issue is not access to the file but the query. Try another table

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

Author Comment

by:zimmer9
ID: 38742831
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True

This works.
0
 

Author Comment

by:zimmer9
ID: 38742832
And this works too:

DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttributes", "U:\GetDoc.XLS", True, "A1:K65535"
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742839
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
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742843
You can try this but you probably need to be user dbo

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes", ImportedFile, True
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742849
Try using  dbo_tblGetDocumentAttributes rather than dbo.tblGetDocumentAttributes


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27926580.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...)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now