Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Export memo field (2003)

    I'm trying to export data from an Access database table to a text (.csv) file, and I'm hitting a snag with a memo field, "Remarks". The user enters data via a form, and this field uses a rather large text box. As the user composes in this text box, it sometimes seems necessary to hit the Enter key (twice) to start a new paragraph and to insert a line of space between the paragraphs. Well, I've learned the hard way that those carriage returns (or whatever you call 'em) can wreak havoc when I try to export the data. I've examined the export results in Excel, and I see blank rows where we've created those lines of space. Furthermore, those second and subsequent paragraphs get bumped to the "head of the line". That is, they appear as the first field in the next record, not as the tail end of the current record.

    I hope I'm making sense with this. My question, quite simply, is how to fix my data so it will export properly. I suppose I can run it through a query, but I haven't a clue how to fine-tune the field in the query to make it look right. I could go through the database and manually get rid of the extra carriage returns, but with the database approaching 1,000 records, it's impractical. Suggestions...?

  2. #2
    New Lounger
    Join Date
    Feb 2005
    Location
    Rio Rancho, New Mexico, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export memo field (2003)

    Lucas,

    I think I ran into something similar in the past and I think the majority of the problem lies in Excel as far as what the cell will accept. I have tried to export really long memo fields and it seems to truncate it to make it fit. Our solution was to limit the memo field. Not sure if you can do that, sounds like it isn't. It also seemed to not be a consistent rule interpretation by Excel. Some fields would take 255 characters while others would allow 280. My thought is that it is a glitch in Excel that might be worked by the Excel forum. Just a thought and sorry I don't have a better answer for you.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export memo field (2003)

    This will replace the carriage return with a space.
    Create this function in a module.
    Function ReplaceNewLine(tekst As String)
    ReplaceNewLine = Replace(tekst, vbCrLf, " ")
    End Function

    In your query add a column with as field : ReplaceNewLine([YourMemoField])

    Export your query.

    I don't know how this will handle the 255 character limit when exporting to Excel or cvs.
    Francois

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export memo field (2003)

    Have you specified quotes as text qualifier when exporting? If so, the memo field should stay together, even if it contains carriage returns/line feeds.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Why would you want to export a memo field to a csv file?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Export memo field (2003)

    Thanks for the reply, Randy

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Export memo field (2003)

    Hello, Hans! I gather from the way you ask the question that there must be a good reason not to export a memo field to a .csv file. I'll explain a little further.

    My team of writers and training developers has created a series of manuals. Members of the team are scattered "across the fruited plains" of the U.S. The first drafts (Word documents) went from the authors to the editors (me and one other employee). We editors put our recommendations into an Access database that I created. This DB has fields to identify the precise location of the passage we propose to change: Manual number, chapter/paragraph/line number, editor's name, date we entered the item in the DB, and

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export memo field (2003)

    When you use the export wizard, use Delimited and click on the Advanced button.
    In the new window, select quotes in the text qualifier textbox. See attachment.
    Francois

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Export memo field (2003)

    Ah, yes! Thanks, Fran

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •