Multiline fields in Access exported to CSV

My Access table has a "memo" field type containing product description, which includes imbedded new lines. User enters data using Access form, new line using Ctrl-Enter, new paragraph using Ctrl-Enter twice.

When table is exported to CSV file (for uploading to an online application), Access breaks the line when it sees the imbedded new line within the product description field. This is (mis)interpreted as a new row by the online application. In example below, the data is interpreted as 3 records (rows) when it is all part of one row.

"120.00","Super Gizmo","This is the best thing since sliced bread. No home should be without one.

Available in Blue, Green and Yellow"

My understanding of CSV is that a new record should be correctly treated as a new row. Therefore the error seems to be that Access is creating new records in the CSV export instead of inserting newline characters? I have done a similar thing with an Excel spreadsheet and multiline cells are saved as a single CSV record.

Need help to get around this problem, i.e. how to save an Access table with multiline fields into a valid CSV file.
ayudhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jschrismanCommented:
In a CSV file the end of a record is normally marked by a Newline character. In MS-DOS/Windows the newline character is actually two characters. A carriage return followed by a line feed. Having the newline character combination in one of the fields is sure to confuse any program which tries to read in the CSV file.

I can't think of any way around it except to remove the newline characters from the field before exporting the table to a CSV file. Perhaps you could place another character in its place that the program which reads in the CSV file would recognize as an end of line character.

If you don't want to remove the character from the table the users are entering data in you could run a make table query which duplicates the original table except the memo field would have all CR/LF pairs stripped out. Or, just save it in a different format which the other program can read in. Most programs can read in a DBase file.

Let me know if this gives you any ideas and if I can do anything else to help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ayudhAuthor Commented:
Thanks to ischrisman for the insignt on how it works. But I still need a simple solution. Suggested solutions can work in theory but not practical for my end-user environment.

Is there a way to easily insert only a line feed character Chr(10), and not the CRLF combination Chr(13) Chr(10), while typing from the keyboard into an Access form field? In Excel, Alt-Enter does the trick. Doesn't work in Access (surprise, surprise).
0
jschrismanCommented:
Ayudh,

Did my answer help you find a solution?

I was going to add more to it to try and get the results you wanted. Do I need to?
0
ayudhAuthor Commented:
Your answer explained the problem very well. I am still considering which of the options you suggested to adopt. I accept that this problem is a limitation of Access, and have released the expert points to you. However, if you have anything further to add, I would be most interested to hear. Please read my append of 23 December in response to your first answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.