Convert .CSV to .ov@

 

Does anyone know how to delete the character in the address field of the .CSV files? It looks like a box with the vertical lines "ghosted". You can pull the CSV file into Excel and it looks like it is a special symbol for a carriage return. POIEdit will not pull in files like this unless you remove everyone of these characters. I tried "search & replace" from within Excel but Excel will not recognize the special character to find it. The converter programs that convert to OV2 just delete the address and phone number column. I have laboriously gone through a file and deleted this "carriage return?" character and replaced it with a "space" and POIEdit is then able to pull it in, save all the info and convert it to an OV2 file. There has to be an easier way

So if a CSV file has

So if a CSV file has linebreaks in the address field, what would be done to the address for output to the OV2 file?

I have a free converter script at http://dr5274.googlepages.com/poiconverter, but it doesn't do anything for OV2 files. When I get some time, I could add a CSV to OV2 converter, if I know what the output format is.

David

--
nüvi 200

Linefeed or carriage return?

Cyrus B Brown wrote:

Does anyone know how to delete the character in the address field of the .CSV files? It looks like a box with the vertical lines "ghosted". You can pull the CSV file into Excel and it looks like it is a special symbol for a carriage return. POIEdit will not pull in files like this unless you remove everyone of these characters. I tried "search & replace" from within Excel but Excel will not recognize the special character to find it. The converter programs that convert to OV2 just delete the address and phone number column. I have laboriously gone through a file and deleted this "carriage return?" character and replaced it with a "space" and POIEdit is then able to pull it in, save all the info and convert it to an OV2 file. There has to be an easier way

If the character is a 'linefeed', you can use POI Verifier to easily remove them. But I've found in some POIs the author entered a "carriage return" instead of the linefeed. I corrected one file for a member by modifying the source code to look for the carriage return and change it to a linefeed. But I don't have the source code with me; and won't be home for at least another month.

If you want to send me the file (or email me which one it is), I'll work on it for you.

RT

--
"Internet: As Yogi Berra would say, "Don't believe 90% of what you read, and verify the other half."

I went ahead and did it by "hand"

I appreciate the offer. It appears all or most of the .CSV files cannot be opened by POIEdit (to convert them to .OV2) because of these characters. My expertise is limited. I just noticed that in an Excel spreadsheet there is a "special character" between street, city and phone number. I had to remove them for POIEdit to open the file. "CSVtoOV2" deletes the address column.

The Address Column

Cyrus B Brown wrote:

I just noticed that in an Excel spreadsheet there is a "special character" between street, city and phone number. I had to remove them for POIEdit to open the file. "CSVtoOV2" deletes the address column.

If you want the address to appear in the POI you can always cut & paste the info from column 4 into column 3 using Excel then convert the .csv using POIEdit. The .OV2 files use a 3 column format, i.e., lat., long. and address/info.

I know, it can be time consuming especially if it's a large file! wink

ML

--
Freedom isn't free...thank you veterans! Heard about the tests to detect PANCREATIC CANCER? There aren't any! In Memoriam: #77 NYPD-SCA/Seattle Mike/Joe S./Vinny D./RTC!

That will not work in this case because that columns enties

have those carriage returns or line feeds and POIEdit will not accept them.

Combining Cells in Excel

Quote:

If you want the address to appear in the POI you can always cut & paste the info from column 4 into column 3 using Excel then convert the .csv using POIEdit. The .OV2 files use a 3 column format, i.e., lat., long. and address/info.

I know, it can be time consuming especially if it's a large file! wink

ML

Actually - it isn't time consuming if you put this formula in the top cell of column 5 and drag-copy it down:
=C1&" "&D1
(the drag-copy will change the column to C2/D2, C3/D3, etc)

Then copy that column and go to column 6 and in the top cell right click and select 'paste special' and select 'value'. Now you can delete column 3 and 4 and your done.

Sounds a little convoluted, but it is really quick smile

Thanks

RapidPirate wrote:
Quote:

If you want the address to appear in the POI you can always cut & paste the info from column 4 into column 3 using Excel then convert the .csv using POIEdit. The .OV2 files use a 3 column format, i.e., lat., long. and address/info.

I know, it can be time consuming especially if it's a large file! wink

ML

Actually - it isn't time consuming if you put this formula in the top cell of column 5 and drag-copy it down:
=C1&" "&D1
(the drag-copy will change the column to C2/D2, C3/D3, etc)

Then copy that column and go to column 6 and in the top cell right click and select 'paste special' and select 'value'. Now you can delete column 3 and 4 and your done.

Sounds a little convoluted, but it is really quick smile

Tried it and it works great! Thanks for the formula. smile

Mike

--
Freedom isn't free...thank you veterans! Heard about the tests to detect PANCREATIC CANCER? There aren't any! In Memoriam: #77 NYPD-SCA/Seattle Mike/Joe S./Vinny D./RTC!

CARRIAGE RETURN

retiredtechnician wrote:
Cyrus B Brown wrote:

Does anyone know how to delete the character in the address field of the .CSV files? It looks like a box with the vertical lines "ghosted". You can pull the CSV file into Excel and it looks like it is a special symbol for a carriage return. POIEdit will not pull in files like this unless you remove everyone of these characters. I tried "search & replace" from within Excel but Excel will not recognize the special character to find it. The converter programs that convert to OV2 just delete the address and phone number column. I have laboriously gone through a file and deleted this "carriage return?" character and replaced it with a "space" and POIEdit is then able to pull it in, save all the info and convert it to an OV2 file. There has to be an easier way

If the character is a 'linefeed', you can use POI Verifier to easily remove them. But I've found in some POIs the author entered a "carriage return" instead of the linefeed. I corrected one file for a member by modifying the source code to look for the carriage return and change it to a linefeed. But I don't have the source code with me; and won't be home for at least another month.

If you want to send me the file (or email me which one it is), I'll work on it for you.

RT

hopefully this helps ... http://www.dba-oracle.com/t_ms_word_replace_line_breaks_carr...

Replacing Line Break characters.

Want to use Ctrl-H to replace a specific character in a range of cells with a
line break (Alt+Enter) - how do I access special characters? (^p didn't work)

In the "Find What" box of the Replace dialog, hold down your left ALT key
and type 0010 on the numeric keypad (not the number keys above the letters).

http://www.microsoft.com/office/community/en-us/default.mspx...

You learn something new each day.

jhalsted wrote:

In the "Find What" box of the Replace dialog, hold down your left ALT key and type 0010 on the numeric keypad (not the number keys above the letters).

Thank you, Nice trick!

--
Garmin Nüvi 650, 255WT

Great Tips

I really wish I would have found this thread before I spent all day yesterday manually changing a 2000 item POI file.