Problem
We wanted to import four lines of text into the Contact address information for Vendors. Since they were to be entered as separate lines we had problems importing it directly.
Solution
Note: To get a step-by-step video tutorial on importing data have a look at the Import tutorial on the Tutorials page (you will need to scroll down to find this).
User Import Tip:
Many thanks to John Wilcox for this tip!
——————————————————————————–
We did it this way:
Info was in Excel, with the data to be combined in four cells.
1. Using concatenate we copied into a 5th cell the following:
~[cell1]@[cell2]@[cell3]@[cell4]~
[The ~(tilde) & @ symbols were used as placeholders for later operation.]
2. Using find/replace, we replaced all commas in the cells with periods.
So much for grammar. We could have used an obscure character and later changed them back to commas.
3. We consolidated all the columns for importing and cut and pasted them into a word file as a table.
4. Using find/replace we replaced the ~ with ” (straight quotes-controlled in autocorrect in MS Word). We now had “” around the text that was to be separated into lines.
5. Using find/replace we replaced the @ with ^p (shift-6 small “P”, the symbol for a carriage return)
6. We converted the table text to comma delimited.
7. We saved the file as a txt file.
When we imported this, the target text was imported into one of the contact fields which allows carriage returns. The carriage returns within the quote characters all produced new lines in the text.
Tada!!!
The trick is to end up with a text file, with header row, that looks like this:
title1,title2,title3 [where title3 is the field to have CR’s] data1,data2,”line1(^p)line2(^p)line3″ [lines 1 2 & 3 will end up on separate lines in the field]
Import as comma delimited file and bingo.
