none
Justify a text RRS feed

  • Question

  • When you import data into a Memo field in an Access table from a CSV file, line breaks are lost. How do I get them back
    Monday, February 12, 2018 3:03 PM

All replies

  • Justify a text
    Monday, February 12, 2018 3:00 PM
  • If you open the file in Excel, the line breaks are present?
        If so, have you tried saving it as an xls(x) and importing the Excel file directly?

    What about creating a VBA routine to perform the import (read the file and process the data)?


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, February 12, 2018 5:52 PM
  • A CSV file should not have line breaks in a line because by definition a line break makes multiple lines. So it depends on what characters are that you are calling line breaks and what other processing is occuring.


    Sam Hobbs
    SimpleSamples.Info

    Monday, February 12, 2018 6:00 PM
  • When you import data into a Memo field in an Access table from a CSV file, line breaks are lost. How do I get them back

    Hi GermanBH,

    I don't think the line breaks are lost but not fully understood by Access.

    Access needs Chr(10)Chr(13) as line break, and it could be that Excel has only Chr(10) or Chr(13). On import you could replace all Chr(10) characters to Chr(10) & Chr(13). If that does not help, then try to convert all Chr(13) to Chr(10) & Chr(13).

    Imb.

    Correction:

    Thanks Dirk.

    Chr(10) & Chr(13) should be read as:  Chr(13) & Chr(10)

    Imb.

    • Edited by Imb-hb Tuesday, February 13, 2018 9:10 AM Correction
    Monday, February 12, 2018 6:12 PM
  • Excel stores line breaks within a cell as the line-feed character, Chr(10), while Access stores them as the carriage-return + line-feed combination, Chr(13)+Chr(10).  If your .CSV file was created by Excel, that may be the problem.  In my test, I found that the line break I had in my imported field was not visible, but it was actually there, just not recognized by Access.

    I don't think you can prevent make an Excel-style line break be recognized by Access, but you can either use a text editor to do a find-and replace on the .CSV file before importing it, or run an update query in Access to correct the line breaks in the table after you have imported it.  Such a query would look something like this:

    UPDATE [MyTable] SET [MyField] = Replace([MyField], Chr(10), Chr(13) & Chr(10))


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 12, 2018 7:16 PM
  • Access needs Chr(10)Chr(13) as line break, and it could be that Excel has only Chr(10) or Chr(13). On import you could replace all Chr(10) characters to Chr(10) & Chr(13). If that does not help, then try to convert all Chr(13) to Chr(10) & Chr(13).

    Imb - the line break combination is the other way around:  Chr(13) & Chr(10) -- carriage return followed by line feed.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 12, 2018 7:19 PM
  • Yeah, like in the constant vbCrLf. Better yet, use vbCrLf. There are also vbCr and vbLf. vbNewLine is the same as vbCrLf except for a Macintosh it is the correct value for Macintosh.

    Miscellaneous Constants



    Sam Hobbs
    SimpleSamples.Info

    Monday, February 12, 2018 9:15 PM
  • Yeah, like in the constant vbCrLf. Better yet, use vbCrLf. There are also vbCr and vbLf. vbNewLine is the same as vbCrLf except for a Macintosh it is the correct value for Macintosh.

    Unfortunately, the VBA constant vbCrLf can't be used in a query, so you have to go with Chr() expressions.

    The difference in vbNewLine between Windows and Macintosh is important if you are programming for other Office programs, which have Mac versions.  Alas, poor Access does not.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 12, 2018 9:23 PM
  • The difference in vbNewLine between Windows and Macintosh is important if you are programming for other Office programs, which have Mac versions.  Alas, poor Access does not.

    Yes of course. I hope that anyone reading about vbCrLf will remember vbNewLine when it is important.



    Sam Hobbs
    SimpleSamples.Info

    Monday, February 12, 2018 9:47 PM
  • Imb - the line break combination is the other way around:  Chr(13) & Chr(10) -- carriage return followed by line feed.

    Thank you, DIrk.

    Sometimes the speed of thinking is a little de-synchronized with the speed of typing.

    In practice I have all these definitions are somewhere wrapped in the code.

    Imb.

    Monday, February 12, 2018 10:37 PM