Importing data with line feed

[wlcummings]

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?????

[ajetrumpet]

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...

[wlcummings]

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

[wlcummings]

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

[thhui]

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.

[wlcummings]

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.

[wlcummings]

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.

[thhui]

It seems that normal find and replace cannot be done with special character in the table view.

Using VBA is a must.

[wlcummings]

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

[thhui]

Use the function in the query to replace the character in a field.

[wlcummings]

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.