Solved

Access 2003 TransferSpreadsheet Method errors while importing Excel 2003 document

Posted on 2011-09-19
19
416 Views
Last Modified: 2012-05-12
Hello-
I'm importing a number of excel worksheets using the code below.  I have one column that is defined as TEXT and is causing a numeric field overflow.

If I perform a manual Get External Data, the error log shows only records where the field value is text or a "/"

During the Get External Data, I also notice that the field value is Double - not TEXT which is defined for that field in the destination table.

Any thoughts are greatly appreciated.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportedRegistrations", Me.txtFileName, True, Range:="RegistrationsV2!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblEventHeader", Me.txtFileName, True, Range:="EventHeader!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblLocations", Me.txtFileName, True, Range:="Locations!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblUsers", Me.txtFileName, True, Range:="Users!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCategories", Me.txtFileName, True, Range:="Categories!"

Open in new window

0
Comment
Question by:OnsiteSupport
  • 9
  • 7
  • 3
19 Comments
 

Author Comment

by:OnsiteSupport
ID: 36562903
To add to this problem, I removed all Alpha data from the column and the import works with no errors.
So, I have a column defined as TEXT, but sees numerics in the column during the import and it overrides it.  Then when Text is imported, the import fails.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36563453
Access 2003 import from Excel always seems to have that grief. It guesses based on the first 50 rows or so.
Strategies:
1)Put a dummy row of data in row two to fool Access and then whack that record from your destination
2)Build good dummy files to use as link tables.  Once Access decides what a column is, it doesn't change it's mind.
Replace the dummy tables with the real ones.
0
 

Author Comment

by:OnsiteSupport
ID: 36563956
Would CSV be more reliable?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 167 total points
ID: 36563989
<Would CSV be more reliable?>

Yes, but you need to create an import specification.

'to create an import specification
'
1. File>get external data>import
2. select in the File of types box   Text files (*.txt etc..
3. select the file
4. in the import text wizard window select  delimited
5. Click advanced
6. in the import specification window
    type the name of the field in the Field Name column
    (here you can use the field names of the destination table, specify data type,
      check the box Skip if you do not want to import the column)


7 click save as, give the specification a name  <-- this is the specification name that you will use in the command line below

Now you can use the code below to import the CSV file via code

   DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True





0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 333 total points
ID: 36564031
That may work, and Access 2003 may not ignore your specified data assignments.
Access 2007 and 2010 do not ignore your specified data assignments.

How often do you do this?
Option 1 can be automated to open the Excel file, insert a dummy row, and then whack it out of the final table
Option 2 with the link tables is bulletproof but you have to set it up once, and then code to take the files named in your textbox and overcopy the dummy tables

capricorn1's option is good but you have to set it up once for each time the filename changes

No matter how you slice it, there's some hassle to getting what you want.
0
 

Author Comment

by:OnsiteSupport
ID: 36564052
Good thing is FN's will never change.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36564083
I use Option 2
I'll take the Excel file, make a copy, leave the headers and remove all but one line of data.
That data I make sure is the right type so Access gets it right and save it.
I then make it a link table in Acces.
I then overcopy that dummy file with the real thing.

Then I can just query the link table and do whatever with it.
Just make sure to never use the Linked Table Manager to change the link table file location and it works perfectly
0
 

Author Comment

by:OnsiteSupport
ID: 36566633
So, in theory, I could force all values in this column to be test and it should solve my problem? Say - Add a "T" in front of every record?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36566700
adding a dummy information to your data before importing ???

what are the values in the column that is giving problem ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36566960
if you have mixed values of alpha and numbers for that column, just select all cells and do a descending sort on that column before importing.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36567707
<So, in theory, I could force all values in this column to be test and it should solve my problem? Say - Add a "T" in front of every record? >

It doesn't have to be every record.
Access looks at about the first 50 rows or so.
If it doesn't see an alphanumeric in the first 50, regardless of what you have declared the column to be in Excel, it'll make it numeric.
If it sees an alphanumeric in the first 50, it is likely to choose text, although sometimes it gets the smart ass idea that you made a typo with that alphanumeric one, and that the column really still should be numbers :(
This PITA is one of the few good reasons to upgrade to Access 2010

If you, and not end users, are doing this, you could manually add some letters into the value of the first few rows of your problem columns, and then fix the data in Access afterward--that works, but is not necessarily elegant.
0
 

Author Comment

by:OnsiteSupport
ID: 36567855
Since it's an Alpha field on the web side, I'm concatening text to the front end of the field contents.  Only problem now is the clown who is giving me the data killed the website! Lol
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36567923
Now that you mention it, it would be easy enough to run an update query along the lines of

UPDATE Table1 SET Table1.Field1 = Right([field1],Len([field1])-1)
WHERE (((Table1.Field1) Like "t*"));

to get rid of the leading letter 't' if you concatenated 't' onto all the data prior to import.

Sometimes its not the answers that you get from the Experts, its the thought process you go through that gets you where you need to go!
Most of my own questions go that way :)
0
 

Author Comment

by:OnsiteSupport
ID: 36568097
ok...so now I'm having a competition for the points :)

Please let me know why you feel you deserve the points
0
 

Author Comment

by:OnsiteSupport
ID: 36568103
The contest entries will be reviewed and judged by Donald Trump for sarcasm content  LOL
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36568350
The points need to go on an answer that details how you actually solved the problem :)
So it isn't a question of deserved, it's a question of what worked
And while @capricorn1 has a bazillion points, and I have a whack, it's not really about that for me.
It's about seeing to it that working answers get posted--because when I started in MS Access, my googling lead to the site A LOT.

So,  what did you do in the end?
0
 

Author Comment

by:OnsiteSupport
ID: 36568807
I'm at a dead end.  I've modified the query on the web side (SQL2Excel joomla plug-in).
In PHPMyAdmin, the results look right.  But, since there are resource issues on the joomla website, I can't get an excel sheet that isn't corrupted.

I will be modifiying an older Excel sheet when the export was working to look like the expected results.
I may be beating up my counterpart on the web side if he doesn't fix it soon :)
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 333 total points
ID: 36568884
CSV is six-for-one half-dozen-for-another.
Excel opens them too.
Instead of TransferSpreadsheet, it's TransferText.

Alternatively, open the CSV in Excel and save it as an xls.
Extra bonus points alternative: Open the CSV using Excel automation from Access VBA, save it as xls and then move on.

Whatever it takes to get good data into Access where you can transmogrify it as needed :)
0
 

Author Closing Comment

by:OnsiteSupport
ID: 36573303
Good Morning-
Before the web export, I used concat("T",value).
Forcing the value to text seems to do the trick when I import to Access...that is until they add another curve ball :)

Thanks for the help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

744 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