Solved

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

Posted on 2014-09-30
23
243 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
  • 12
  • 5
  • 4
  • +1
23 Comments
 
LVL 25

Expert Comment

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

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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:SteveL13
ID: 40353671
Yes.
0
 
LVL 49

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 49

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 49

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 49

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 49

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

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

10 Experts available now in Live!

Get 1:1 Help Now