Importing fields with carriage returns
I am baffled after two weeks of failures. I'm trying to import a CSV file. It works fine except for where my original data includes carriage returns in a field. Every time the import hits a CR, it starts a new record in the table. The problem field is with multiple-choice questions where a question (with a return) is followed by 5 choices (with a return after each choice). What in the world am I doing wrong? Thanks for any help you might provide.
3 replies
-
Hi Bill
You aren't doing anything wrong. CSVs use commas to separate fields and carriage returns to separate records. What is the original source of your CSV? can you substitute thecarruage return for aonther character in the offending field? It can be substituted back in after the import.
Regards
John
-
Hi, John. Thanks much for your reply. I have a database of 200,000+ academic questions (each record includes a question plus several other fields), many of which include commas in the text. Many of the questions include a stem followed by 5 choices, with each choice preceded by two spaces and a letter (A-E), so there are often multiple carriage returns within a record. And sometimes the choices may extend to two lines I produced a textfile from my old Hypercard database using the bar character "|" to separate fields within each record, and that works fine. I was hoping the multiline text field would allow the importation of the text while preserving the formatting. Is there possibly a way to tell the Ninox system to ignore the carriage returns? Based on your note, I will see if some kind of global "find and replace" for carriage returns and/or commas within the text might work. I have found I can directly type in a question with CRs and commas and have the question be perfectly formatted in Ninox. Anyway, I'll be messing with your find and replace idea in the hours to come to see if that might solve the problem. Of course, if there is a more efficient way to do the import, I am definitely all ears. Thanks again for your response.
-
In addition to a record separator — usually "Carriage Return", and a field separator — usually the comma, but can be set to something else, the .csv format also includes the concept of a "Text quotation" character — usually "Double Quote", but can be set to something else. The idea is that, inside a matching pair of "Text quotation" characters, the other formatting characters are not acted upon. So, if the field separator is the comma, fields containing commas may be surrounded by "Text quotation" characters to make the comma a normal character. The same applies to the record separator: if a field contains "Carriage Return" characters, surrounding it by "Text quotation" characters makes those line separators "internal" to the field.
While exporting data from a piece of software, one should thus first look if that piece of software includes the capability to define a "Text quotation" character. That's the easiest way to solve the problem. Otherwise, one must find a way to process the .csv file in some text editor to insert the "Text quotation" characters at the right places.
When importing a .csv file, Ninox correctly interprets those formatting characters, which can be set to match the options of the input file.
Content aside
- 3 yrs agoLast active
- 3Replies
- 449Views