Solved

Problem importing from Excel to memo field in Access

Posted on 2013-12-18
6
576 Views
Last Modified: 2013-12-19
I am trying to import an Excel spreadsheet into Access.  There are > 255 characters in a few of the Excel cells - and so I have set up the Access field as Memo.

However, when I use the following VB code to import the spreadsheet, the data is getting truncated.  What am I missing?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_MASTER_temp", GetFile2, True

Thanks for your help,
je
0
Comment
Question by:aeolianje
  • 3
  • 2
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 350 total points
ID: 39728819
Try a couple of things:

- Check your table's design.  Do you have any formatting on your memo field in your table's design?  If so, remove it.

- Also try placing a row of 'junk' data at the top of your spreadsheet with more than 255 characters in the memo field (so that the first row in that column contains more than 255 characters).
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39728860
... and try importing the data into a table that does not exist in your database, so that the table structure is created by the import itself:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Newtbl_MASTER_temp", GetFile2, True
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39729061
double check your import specification
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Closing Comment

by:aeolianje
ID: 39729158
Perfect!   The original import was setting the format of the fields to "@".  Once I removed that, it worked!

Thank you!
je
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39729261
@aeolianje

Your closing comment suggests that the problem existed in the import specification.  However, the accepted solution comment does not seem to refer to the import specification.  Did you mean to accept that comment?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39729450
aeolianje,

Glad to help.



akimark,

The TransferSpreadsheet method does not use import/export specifications:
http://msdn.microsoft.com/en-us/library/office/bb214134(v=office.12).aspx
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

770 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