Changing <br> newlines to CR LF

Curious...being that csv files are plain text files, why wouldn't you just use a regular text editor's search/replace function (notepad for example) to do this rather than install a new app?

Notepad interprets the 0D 0A as text, not carriage return linefeed. Maybe there is a way around this, but I don't know it.

Not sure if this works but if you're interested, try the following.

Open Notepad. In Notepad do the following:

Press and hold ALT then press 1 3
Release ALT key
(that's your CR or carriage return character)

Press and hold ALT then press 1 0
Release ALT key
(that's your LF or line feed character)

Highlight and copy those 2 characters.

--------------------------------

Now launch Wordpad. In Wordpad do the following:
Open the CSV file
Press CTRL H
Search and replace whatever sequence of characters you're looking for with the 2 non printable characters (CR LF). Hint: paste the non printable characters in "Replace with" field.

When done, click File - Save As. Select Unicode Text Document.

I have been trying to edit, remove or otherwise work with the line break character(s) in the US Federal & State Campgnds.csv file with absolutely no success. I have tried all of the suggestions regarding "Alt + 10 & 13" as well as many variations such as 010, 0010, , etc. Even tried the Hex editor. Trying to separate the fields in Excel results in the loss of the phone number (goes into outer space I guess). When I try to pull it into Extra POI Editor, it shows up as the Less than sign followed by the characters b and r (could not display them here without breaking this sentence. There is no right hand bracket. Tried to find that sequence as well with no luck.

I can work around this situation with a little extra work, but would like to know what this character really is and how to handle it.

Thanks in advance

Curt

Enter this formula into cell E1 and then copy it down.

=MID(D1,LEN(D1)-11,12)

Copy the entire Column E and then do a PASTE Special, Values to retain the data. You can use the ASAP utilities to delete 14 trailing characters in Column D.

Curt,

The file in question has hard line breaks and not line feed breaks (ALT 0010 codes). The find and replace will not work as you found out.

A_USer suggestion should help you out or if you need another suggestion please post up again.

My post at http://www.poi-factory.com/node/23417 titled "Line Breaks In A Nutshell" and dated 07/10/09 might help. Hope it does.

a_user, DRMCMTR & whoaboy. Thanks for your replys and input. a_user, I ran the formula and it performed as expected with exceptions. When you do not have a clean file (i.e., additional data covered by the line break character in some of the entries, some phone numbers beginning with the () characters, some phone numbers that were missing a digit, etc.). I had cleaned up the file by other means, but bottom line is that hand massaging is required for a clean file.

whoaboy, I have followed your postings from the first one. I am aware of the variations of placing line breaks, but this one totally messed up my mind as for as removing the character. I would be interested to know what you came up with regarding the "US COE & STATE PARK CAMPGNDS.CVS" file.

As for as I know, everything is a character (either non-printing=functional or printing=alphabet and special characters.) It beats me what that character is. I even tried "Alt-255" which is a fun character. Looks like a space but isn't. Used in a file name, it can throw people for a loop.

Anyway, I was curious about what the character was(is). There are many work arounds for most things, just takes time.

Curt

The Line break character in the "US Federal & State Park Campgnds.csv" file is the "ALT+ENTER" character inserted into the file by method number 2. It is a control character, non-printing, and that's why it looks like a long space. It is the ASCII character 010 - NL. To view multiple lines in one cell of Excel, it may be necessary to adjust the row height or set "AutoFit". It can be removed in Excel by doing a Find "ALT 010" on the number keypad only (with NumLock on), and replaced with anything you desire (a comma, space, etc.). Some laptops and other keyboards may not have a separate number keypad, in which case a number keypad (in the middle of the alpha keyboard) may be accessible by using the Fn key. Hope this helps.

Alt-010 does make a change on the selected cells by writing a comma. However, on my desktop computer, the comma is placed behind that little box thingy. If you now try to do a "Text to columns" to separate the entries, the telephone number goes off into space (or somewhere). I have not been successful in getting rid of the little box with any of the replacement commands. I must be having a senior moment.

I have a full blown computer with separate number keys, I am a dyed in the wool DOS man and have worked with ASCII characters since 1972. I still can't figgure it out.

Curt

In Notepad, when I want to replace the "little box thingy" with another character, I highlight the box thingy, press Ctrl+c to copy it to the clipboard and then paste it in the "Find What" field of the Replace dialog box by pressing Ctrl+v.

I don't bother trying to figure out the ASCII code.

Last, I just type the character I want in the "Replace With" field.

What program are you using to edit your POIs? Also, if you downloaded the same US Federal and State Campgrnds.csv file that I did, I see no need to edit line breaks. They're already there, and they should work the same on your Nuvi 750 as they do on my 780.

Been there, done that, however, when I open the .csv file in NotePad, the entries below depict what I get. NO BOX THINGY to select. Also tried to copy the space at the end of the first line with no results. I can delete it by editing each and every line, but I know better ways to clean the file. Maybe it is a setting within NotePad and I need to check it out. I do want to state that I have a clean file, it just bugs me that I can't get rid of this particular thingy or I can't make it work right. Could be computer settings if not software.

Coordinates, Park Name and State-Type are on one line with the phone number on the following line.

-87.34886,33.45047,"Burchfield Branch Park","AL Corp of Eng
205-497-9828"
-87.42979,33.25425,"Holt Lake","AL Corp of Eng
205-553-9373"
-87.42849,33.26382,"Deerlick","AL Corp of Eng
205-759-1591"

Curt - I sent you a PM.

As you found out, this file was created with CR/LF characters (the Enter key) at the end of each line. Windows Notepad will not do the Find/Replace of non-text characters like these, so you can quit trying. You'll just give yourself a headache.

I found another way, I think, and have created a modified file for you to test. I'll send it to you via email when you respond to the PM. If it works, I'll explain how I did it. If not, then its back to the drawing board for me.

I sent him a message as well. I used Excel and a couple of formulas and the ASAP utilities to clean up the file in less than 30 minutes - I even got rid of some dupes. The malformed phone numbers - the ones missing digits were deleted.

Gary and a-user. I have responded to both emails. If these things work, we need to get them out here for others to see and work with.

Many thanks to both for the work you have put into this effort. It is my intention to try to get the file really cleaned up and converted to a .gpx file with much more information. I am a camper and am very interested in having correct locations and information for these types of recreation areas.

I would also like to thank whoaboy (and any/all) others for looking into this problem. We may never see it again, but if we do, maybe we will know what to do with it.

Curt

Sounds like you did more work on the file than I did. I was just trying to find a way to replace the CR/LF at the end of the first line of each entry with a LF character.

@CurtHinson:

The problems with the file are all caused by data input errors when the file was created. Interesting puzzle... here's how the errors can be corrected:

• Open the file in Extra POI editor (EPE) and save as Garmin CSV format. This will replace all the CR/LF in the description field with "<br".

• Open in Notepad and change all "<br ," to ","

• Find/replace "<br _," to ","
  (phone number error in Doll Mountain site)

• Find/replace "979/ ,885-3613" to "979-885-3613"
  (phone number error in Stephen F Austin site)

• Manually fix extra CR/LF in description field of 5 sites
  • Pomme de Terre Hermitage Area
  • Pomme de Terre Pittsburg Area
  • DeSoto Pool 9 Mississippi River
  • Eau Galle Lake Spring Valley
  • Jennings Randolph Lake Elk Garden
• Save file in Notepad.

The file is "fixed" and can now be used in EPE or Excel. In Excel you could add LF with CHR(10) as was previously mentioned.

But there is still a logical data problem... 37 Missouri campgrounds have additional info on electrical, water and sewer that appears before the phone number. You might consider moving this info to a separate column to be merged into the description when generating POIs.

WAASup. I will check it out tomorrow. Just trying to find a logical way to break it down so that the information becomes individual columns without the data (phone number) disappearing in a cloud of smoke. As for the extra data (elec, water and sewer), I just discard that right now, since it is my desire to re-invent this file as a .gpx with that information and more taken right from the sites them selves. I had pulled the file into Extra POI Editor but did not try to save it as a .csv file. Interesting.

Again thanks,

Curt

The CSV file created by Extra POI Editor has all the information in separate columns. Column D (address field) has "address", phone number and extras (elec, water, sewer). The Column D data can be separated using Excel's Text to Columns feature.

Have you considered using Excel to store, manage and manipulate the data then use Extra POI Editor to create the GPX file? Together they make a powerful combination!

EPE will map Excel columns in a CSV file to GPX POI fields. See Options, CSV Fields for CSV Field Assignments.

A new HELP file was added in Extra POI Editor V3.40. It contains more information on this feature, and can be downloaded at:
http://turboccc.wikispaces.com/Extra_POI_Editor#toc5

EDIT: Added HELP info

WAASup, I do have an Excel file with all 24 columns set up for conversion to .gpx. Actually, I have 25 columns because I reverse and combine the coordinate columns so that a copy and paste can be done when using Google Earth, Google Maps or any other program that the coordinates have to be reversed. Saves typing them in. I know that Extra POI Editor maps the coordinates, but I have found that they are a little off (the coordinates in the file arn't that good), so I verify with other programs.

For those that are not necessarily familiar with reversing and combining, a simple formula does the trick. Simply insert a blank column (it becomes C), then type in the following formula:

=b1&","&a1

This will combine the two coordinates while reversing them and separating them with a comma.

Click on that cell and highlight all other cells in Col C, press CTRL D, and it will fill and reverse all lines. Results will be displayed (not the formula) and it can be used to copy and paste in other programs, but it is still a formula. Modifying either or both of the coordinate fields will automatically change (update) column C. This column will eventually be deleted, but if there is a desire to keep it and/or any other columns that contains a formula, that column should be highlighted and using ASAP utilities, "change formulas to their calculated values".

I definately will check this out tomorrow as well as the files provided by a_user and Gary-A.

Curt

Cool! I didn't know about Ctrl-D. Thanks!

This can also be done using Excel - Highlight Column C, Ctrl-C, then right-click, select Paste Special, then select "Values".

WAASup. I am pretty sure that I have figgured out this particular problem. I tried your method of Extra POI Editor saved .csv, removed the < (less than) and letters "br" and pulled it back in to Extra POI Editor. Most of the information is still missing (I think).

Anyway, I got to thinking about another post regarding a hex editor. I tried that, but couldn't get it to work right because I wasn't doing things I should have been doing. I have and use Hackman Editor for my hex editor, so I called it up and determined that the characters in question were 0D 0A. I did a global change replacing those two with 20 (space) and it replaced every occurance, which literally trys to put everything on one line when pulled into Excel. Excel doesn't like that. So it was back to the drawing board. In this particular file, the majority of the entries are Corp of Eng and State Park, so I did a global replacement of the g in Eng and 0D and 0A (which in the editor looks like 670D0A) with 672020 (g-space-space). Worked great. I did the same thing with Park substituting the letter g for k (6B hex). Except for those entries that were messed up, Excel pulled everything in according to Hoyle. You do need to tie the two characters to another character next to it. I think that files with changing data would require too much work this way. I will be happy to explain this procedure in greater depth to anyone who would be interested.

BTW, I used a copy of the .csv file and saved it to the copy name.

I need to learn more about Extra POI Editor.

Curt

PS: Had to edit since most of the post disappeared because I put the less than with letters br together on the same line. Learn something new everyday, die and forget it all.

You can post those special HTML characters if you follow these tips:

http://www.poi-factory.com/filter/tips

Scroll about half way down.

Here is another possible solution using word:

1) Open file in word
2) Find: ^P and Replace with ** (replace all)
3) Find: **- and replace with ^p-
4) find: ** and replace with , follow by space
5) Save file as text
6) Rename file as CSV.

Hi guys,

I read this thread. Is there anything you want me to do?

If you explain clearly what you have and what you want to do, I may be able to do something about it. That CR/LF thing caused me headaches as well. It seems also different GPS do not behave the same way. Some prefer , some others prefer CR/LF.

Anyways. Maybe I can make this configurable. I think your problem is when you read CSV files, right?

Turbo

First of all, I would like to publicly appologize to dhayner for hi-jacking his thread. His post dealt with converting the "Less than + Break) to a CR/LF for those that needed a file using that combination. Some do, some don't. Anyway, I chimed in with a problem I was having with the US Federal and State Campgrounds.csv file. I guess that most (99.99% ?) people wouldn't have a problem with it, but I always want more than just a set of coordinates, a park name, State and phone number. I am researching the entries in the file to gather as much information as I can and get it into a .gpx file which will allow many people (if they have a GPS unit that will show it) to have a better idea of what a particular park/campground looks like with better identification (Street address, City, etc).

The problem with the file was that it had extraneous information in it, a few of the entries had bad or non-existent information, phone numbers had both parenthesis and dashes and some even had periods.

The first thing I do with a .csv file is to pull it into Excel. Then using various means, I massage the data in those columns that have combined data usually separating the data with commas. Using a line from this file, it can be seen that the information on one line (col A, B and C) is as follows:

-87.34886 33,45047 Burchfield Branch Park

Column D contains: AL Corp of Eng(CR/LF)205-497-9828

I break out column D so that I can have State in one column, Type (Corp of Eng, etc) in another column and the telephone number in another. During research, I will create additional columns to help me work with the file (i.e., I will correctly combine the coordinates into a single column so that a copy and paste is possible thereby eliminating typing in the coordinates in Google Earth, Maps or whatever. I add a column for the Street Address as well as the Zip Code. Other columns may be created while working with the file, but will not necessarily be used in the finished product.

The problem I had with this file was when I attempted to break out column D to State, Type and phone. Initially, I performed a global replacement on the column changing "(space)Corp of Eng" to ",Corp of Eng,". After all changes were made to the other types, I performed a "Text to Column" using the comma as a separator in the hopes of getting three columns of data. At this point, the telephone number simply went somewhere into the wild blue yonder. Many variations including using ASAP Utilities (which claims to be able to remove the little boogers) did not work. Tried the hex editor suggested by dhayner, but got frustrated when it was apparent that it removed all occurrences of 0D & 0A. Tried to pull it back into Excel and it complained that I had exceeded the capabilities of a spread sheet. I guess so, since all of the information was on one line. I woke up in the middle of the night and slapped myself for not thinking about the fact that each line had two or more of the CR's & LF's. Got up and fired up my Hackman Editor (hex editor), and did a global on the 0D & 0A but tied it to the last character of the type. First attempt was with AL Corp of Eng0D0A so I typed in 670D0A (gCRLF) for the search and 672020 for the replace (gspacespace) for the replace. Worked like a champ.

I better slow down here before I write a book. There are better ways to create line breaks, but some of the older files have the hard ones. If you have further questions, please do not hesitate to ask, BECAUSE, I have some for you regarding converting this data to a .gpx file using your Extra_POI-Editor.

Thanks in advance

Curt

Hi Curt,

Now, I know what you want to do. I just need to know if there is anything Extra_POI_Editor can do for you. What is your current state with this file?

I downloaded the .csv file and effectively the phone numbers are gone. If at least you cound add a column with the original string, I could "easily" (?) extract the phone number from it as long as the format is not too bad. Then, I could re-export the file as a CSV with the phone number in the address field or as a separate column.

I can also do reverse geocoding and get a POI address from coordinates. So instead of having only "Horseshoe Point", you will get "2998 Horseshoe Point Rd VA 24102".

Anyways. Let me know what I can do. We can also start a new thread for this.

To get back to the original thread, I can provide options to handle CR/LF, , 0D and 0A. Let me know what is required.

To avoid polluting this thread, you can post suggestions here:

http://www.poi-factory.com/node/21156?page=8

I monitor this thread frequently.

Turbo

This isn't really related to issues with the < br > form of the break, but converting between CR/LF which DOS uses and LF (often called newline when used by itself) is easily done with many free and small utilities. I'm sure it can be done with AWK, but if you just want a utility that does this and only this, search for a utility named dos2unix.

wants to learn a UNIX scripting language to do a function that can be accomplished using standard tools within the Office package.

AWK would indeed be a scripting language, although hardly limited to Unix. Well worth learning though, you can do extremely powerful things with it in just a few minutes of study.

However, I tried to make it quite clear that if one didn't want a powerful and useful scripting language, the dos2unix tool exists, and is much easier to use than any Office package. It's pretty much just dos2unix then list the input file and the new output file. You likely could find it on the web, download it, and run it converting a file before Office even finished starting up, let alone figure out how to make the conversion with an expensive tool like Office that really isn't intended as a file converter.

If you want to use Office for this, go right ahead. But there is no reason to be critical of someone offering a simpler and free alternative.

Got another request for line break conversion and decided to improved this feature.

Read this thread: Sun, 08/02/2009 - 3:23pm

http://www.poi-factory.com/node/23417