Solved

Importing an Excel file into a table with one field being a memo field

Posted on 2014-09-30
23
256 Views
Last Modified: 2014-10-05
I believe this was discussed a few days ago but I am really stuck.  I have an Excel file which contains a cell that has more than 255 characters.  I need to be able to import the entire contents of that cell into a table field.  But no matter what I try it only imports the first 255 characters even through the field in the table it is being imported into is a memo field.

Can someone help?

--Steve
0
Comment
Question by:SteveL13
[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
  • 12
  • 5
  • 4
  • +1
23 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40353574
use concatenate and the restriction will be lifted.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40353580
Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&).
0
 
LVL 12

Expert Comment

by:danishani
ID: 40353583
I think to go around this quirk is to import into a temp table first and then append your data to the actual target table.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SteveL13
ID: 40353588
I did try importing to a temp table first. But when I looked at the contents of the field in the temp table only 255 characters were there.
0
 

Author Comment

by:SteveL13
ID: 40353591
I don't understand the CONCATENATE suggestion
0
 
LVL 12

Expert Comment

by:danishani
ID: 40353616
I am not sure why it did not work for you using a Temp Table.
Are you sure you marked the Field Type Memo importing that field?

Couple of notes why this happens:

Source: Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or when Linking to an EXCEL File

The truncated text string that you see is because Jet (ACCESS) sees only "short text" (text strings no longer than 255 characters) values in the first 8 - 25 rows of data in the EXCEL sheet, even though you have longer text farther down the rows. What ACCESS and Jet are doing is assuming that the "text" data actually are Text data type, not Memo data type. One of these suggestions should fix the problem:


1) Insert a dummy row of data as the first row, where the dummy row contains a text string longer than 255 characters in the cell in that column -- that should let Jet (ACCESS) treat that column's values as memo and not text.

2) Create a blank table into which you will import the spreadsheet's data. For the field that will receive the "memo" data, make its data type "Memo". Jet (ACCESS) then will "honor" the field's datatype when it does the import.
0
 

Author Comment

by:SteveL13
ID: 40353633
That did not work.
0
 
LVL 12

Expert Comment

by:danishani
ID: 40353634
Are you doing the import manually or are you using a Command function like DoCmd.TransferSpreadsheet() ?
0
 

Author Comment

by:SteveL13
ID: 40353657
Manually.
0
 

Author Comment

by:SteveL13
ID: 40353663
Although eventually I need to DoCmd.transferspreadsheet.
0
 
LVL 12

Assisted Solution

by:danishani
danishani earned 250 total points
ID: 40353665
Its hard to tell why its not working.
Did you try to have the first row more than 255 characters, in order to see if that helped?
0
 

Author Comment

by:SteveL13
ID: 40353671
Yes.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40354033
You could save the Excel as an CSV file. Then import/link this.

/gustav
0
 

Author Comment

by:SteveL13
ID: 40354506
Gustav,

I had tried your suggestion but it didn't work.  But to be sure I'll try again and get back.
0
 

Author Comment

by:SteveL13
ID: 40354598
I just tried it again... saving the Excel file as a CSV Comma delimited (.csv) file and manually imported it into a test table.  The cell (field) with the character count over 255 stopped at 255 characters.

Very frustrating.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40354609
Have you tried just linking the worksheet? And/or inserting a very long text in the first row?

/gustav
0
 

Author Comment

by:SteveL13
ID: 40354712
I did try inserting a very long text in the first row but that didn't help.  And I can't use the method of linking to the worksheet because the intent is for outside people to send the Excel file to the office.  Then using DoCmd.transferspreadsheet append a table.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40354763
Then you may automation - open the Excel file and read the content of the range in question.

/gustav
0
 

Author Comment

by:SteveL13
ID: 40354765
As a test if anyone is willing to try it, I've attached an Excel file with the data "dummied" because I can't show the real data.  Plus a pretty much empty Access 2010 d/b.

I have tried over and over again to manually import the Excel file data into a new table and have all of the contents in cell B57 import properly and not cut off at 255 characters.  The Excel cell contains 1,077 characters and I sure wish I could make them all come over into the Access table.

--Steve
NotesToImport.xlsx
TestImport.accdb
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40354822
If you have a long text as (one of) the first row(s), you can both import and link the worksheet.
Choose "Long Text" for the notes field.

/gustav
NotesToImport.xlsx
TestImport.accdb
0
 

Author Comment

by:SteveL13
ID: 40354983
Long text isn't a choice for me when I'm setting up the manual import.  I only have the following:

Choices
0
 

Author Comment

by:SteveL13
ID: 40355000
Plus I can't d/l your example file back to me.  When I click on it I get:

Standard ACE DBµnb` ÂUé©gr@?œ~Ÿÿ…š1Åyºí0¼ß̝cÙäßFûŠ¼N?Xì7äçœúý(檊`í7{6LÑß±ÎWCv ±3Ây[+|*£à|™˜ý-ãÏAI„f_•øЉ$…gÆ'DÒîÏeíÿÇF¡x íé-bÔT4.0Wd»v°:ÀðÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÞ Y)îîîîS ++ Y  ì Y îY  Y Y  Y  îþY  Y  î Y î Y î Y î Y þ Y 2ŸþY  Y  î Y  kþY ConnectDatabaseDateCreateDateUpdate FlagsForeignNameIdLvLvExtraLvModule LvPropName OwnerParentIdRmtInfoLongRmtInfoShortTypeƒÿÿîÿÿÿÿîÿÿÿÿîÿÿÿÿîÿÿîÿÿÿÿîÿÿÿÿîÿÿÿÿîÿÿÿÿîÿÿ»´YÿÿÿÿYÿÿÿÿIdParentIdName        ÿÿ©OY|Ïi!&S  +Y Y Y  Y 2pþACMFInheritableObjectIdSIDƒÿÿXÿÿÿÿÿÿNÿÿÀÿÿÿÿýÿÿ•ÿÿ¸ 5&€ˆYÿÿÿÿObjectIdÿÿë YS  Y  Y Y Y  Y þ Y þY  Y þAttributeExpressionFlagLvExtra Name1 Name2ObjectId Orderƒÿÿÿÿÿÿÿÿÿÿÿÿÿÿ Yÿÿÿÿ"ObjectIdAttribute  ÿÿË -YSSS  Y Y Y  Y j þ Y þ Y þ Y ¦þ Y þccolumn
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40355121
Pick Memo.

Right-click file, Save As, change file extension from html to accdb.

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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