ayudh
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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.
ASKER
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).