Results 1 to 6 of 6
  1. #1
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44

    Import data from Excel to Access long text preserving source formatting

    Hi,

    Is it possible to preserve the format of source cells when importing data from Excel, so that any related report would look not only nice, but also easier to read (for example as illustrated for 'wrap text')?
    Click image for larger version. 

Name:	2022-07-25_09-57-26.png 
Views:	25 
Size:	54.6 KB 
ID:	48369



    Thanks,
    Daniel

    P.S.: I have found another thread with the exact same issue, however it's from four years ago and the link to the potential solution is unfortunately dead.
    Last edited by daredan; 07-25-2022 at 05:45 AM. Reason: marked as solved

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Wrapped text is an excel cell display setting, not a format within the stored text, so I'm not sure how you would be able to import it?

    That thread is about retaining carriage returns, which is certainly possible, with a little bit of code.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Try linking the xl file as an external table,
    then run append query.
    the target field should be LONG text (aka MEMO) type.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Try increasing the height of the datasheet row and reducing the column width- or use a continuous form

    there Is code you can use to determine the height of the tallest field in a recordset dependant on width and set the height accordingly

  5. #5
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    Thanks for your proposals. I tried various approaches, however it looks like that the source formatting of these cells is disregarded no matter if I import or link the data. Shifting row widths and heights resulted in line breaks, however they were of random nature and not connected to the source.
    Click image for larger version. 

Name:	2022-07-25_12-50-30.png 
Views:	21 
Size:	35.8 KB 
ID:	48370

    Maybe I just give a bit of background, disregarding if it's relevant for the issue described:
    We are expecting our reporting entities to provide explanations on financial figures on a monthly basis.
    This is done via an Excel template containing code to our financial system, so the numbers can be updated on the fly.
    Then they will return the files to us, where I want to put the content into an Access database to aggregate that information, so that my stakeholders don't have to open lots of individual Excel files.
    Naturally it would be nice to keep the readability of the Excel cell contents, as otherwise the benefit of the aggregation is partly taken away, if explanation content needs to be deciphered first.

  6. #6
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    I think I found a solution, that is good enough for me.

    I found this 'How to Import Data from Excel into Microsoft Access with Multiline Text (Cells with Line Breaks)' video explaining on what can be done.

    Applying his fix to my issue, the following update query did the job:

    Code:
    UPDATE tblTransaction 
    SET tblTransaction.Comment = Replace([comment],Chr(10),Chr(13) & Chr(10))
    WHERE (((tblTransaction.Comment)<>""));
    This is the result of just the replace part for comparison:
    Click image for larger version. 

Name:	2022-07-25_13-26-54.png 
Views:	19 
Size:	9.4 KB 
ID:	48372

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How can I import long text with line breaks from .XML
    By PeterG in forum Import/Export Data
    Replies: 3
    Last Post: 07-11-2020, 03:54 PM
  2. Replies: 1
    Last Post: 08-06-2016, 10:39 AM
  3. Replies: 14
    Last Post: 12-28-2015, 07:51 AM
  4. Preserving String variable formatting in text box
    By Monterey_Manzer in forum Forms
    Replies: 5
    Last Post: 06-18-2013, 09:39 AM
  5. Replies: 9
    Last Post: 01-17-2013, 05:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums