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
725 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
17 Comments
 
LVL 30

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 30

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 30

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 30

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
 
LVL 30

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 30

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 30

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 30

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 30

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 30

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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