Results 1 to 11 of 11

Thread: Importing data with line feed

  1. #1
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    Importing data with line feed

    Importing from Excel 2010 file with line feeds within each cell (alt enter in excel) to Access 2010 but line feeds are gone upon import or copy/paste.

    It seems funny that a line feed is ctrl enter for access and alt enter for excel.

    How can I get the line feeds into access?????

  2. #2
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    not sure. I've seen this answered but I don't think I've seen an answer. what's the import look like?

    maybe there's another way around it either before or after you import the stuff? ("import the stuff"...ha ha, it sounds like I'm smuggling across a border!)

    that's funny...

  3. #3
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    Thumbs down Line Feed missing

    The data is on 3 or 4 lines like this in excel:

    Name
    Street
    City, State zip

    And like this in Access:

    name street city, state zip

  4. #4
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    line feed

    When I copy and paste from Access to Excel it keeps the Line Feeds.

  5. #5
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    See the following link.
    http://bytes.com/topic/access/answer...ly-into-access

    It explains that Access uses vbcrlf while Excel uses vblf for line break.

    Access could not display correctly the line break in Excel (vblf) while
    Excel correctly translate it from Access.

    In fact, the data imported from Excel to Access is correct.
    Only the display of it in Access is incorrect.

    I suggest using a vba function to convert it afterwards.

  6. #6
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    Carriage Return

    CR LF is not rocket science!!!! How can one company use two different standards, one in excel and one in access, for this simple function. This is still a problem after 20 years of development with Excel and Access. I can't believe this is not a major headache for 1,000 s of users. Or do we just say live with it?

    The VBA function may work but why should I have to do it? The function given in thhui reply requires creation of a temp table changing the CRLF to CR then importing the temp table to the real table. We do 5000 added entries a month so the temp to real is impractical. Gotta be a better way. ARGGGH!!!!

    By the way Thanks Thhui and no-thanks to microsoft.

  7. #7
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    Line Feed missing

    Can I do a find/replace? Tried find chr(10) and chr(13) and vbcrlf and vblf to no avail in access so can't do a F/R.

    Do not want to write VBA code unless absolutely necessary.

    Thanks in advance for any hints.

  8. #8
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    It seems that normal find and replace cannot be done with special character in the table view.

    Using VBA is a must.

  9. #9
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    Line feed after import from Excel to ACCESS

    OK I wrote a VBA module to replace the Excel (chr(10) code with vbCrLf and it works for one record at a time but I have 10,000 records so I want a method to do all the records in the file without operator intervention. Can't figure how to do it with a query or form or a report. Help?

    code I used

    Private Sub Command19_Click()
    Subdivision = Replace(Subdivision, Chr(10), vbCrLf)
    End Sub

    Subdivision is the field name

  10. #10
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    Use the function in the query to replace the character in a field.

  11. #11
    wlcummings is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Location
    WV
    Posts
    7

    Question Adding Function to Query

    Quote Originally Posted by thhui View Post
    Use the function in the query to replace the character in a field.
    Pardon my stupidity Thhui answered my question about updating an entire table to change the excel chr(10) to the access vbCrLf but for the life of me I cannot figure how to use the "buyer"=Replace("Buyer",Chr(10),"vbCrLf") VBA function in a Query -

    I guess my basic question is how do you put VBA in a query or do you have to use SQL -

    The VBA code works in Form but it only does one record at a time.

    Thanks for your patience

    I am importing a excel badly designed worksheet into access and it has tons of line feeds (Alt-enter)s. I would redesign the data base but have 6 years worth of data that would have to be retyped and do not have the resources to do that. I am redesigning the whole thing in Access, for future data entry, but need to do a quick conversion from the old excel data.

    Sorry thhui but i could not follow your earlier hint.

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

Similar Threads

  1. How to do line by line compare of VB code?
    By Buakaw in forum Access
    Replies: 2
    Last Post: 02-14-2011, 09:46 PM
  2. Replies: 5
    Last Post: 12-06-2010, 08:15 AM
  3. Importing data and data quality
    By fsmikwen in forum Import/Export Data
    Replies: 1
    Last Post: 02-01-2010, 01:15 PM
  4. importing data- find new data
    By cengineer in forum Import/Export Data
    Replies: 1
    Last Post: 12-10-2009, 06:56 AM

Tags for this Thread

Posting Permissions

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