How can I convert this file into POIs?

Go from this:

;1030000198066
[POI]
Type=0x0100
Label=Marine View, Orewa, Rodney
Street Address=Marine View
City=Orewa
Region=Rodney
Country=NZ
Data0=(-36.58463540835,174.69547901665)
[END]

to this:

174.69547901665,-36.58463540835,"Marine View"

Is there any other data that I want to capture? Is there an automation tool that can already do this?

- Thanx
- Jon

I hope I am answering your question, THe file must be in a "Text" type file with the Extention being .CSV

The sting for the above will look something like this (must be on one line:

-36.58463540835,174.69547901665,"Marine View, Orewa, Rodney","Marine View,Orewa Rodney NZ"

Basicly,

LAt,LONG,"Name of place","Any comments, such as address/phone"

(Not sure if I got lat/long in propler order but you get the idea)

Hope this helps

Do you just want to modify this one, or is there many of them that you want to modify?

Thanks for the confirmation Bobkz!

Now, what tool do y'all use to manupulate a text file and move fields around to get em to be in the right format? I was gonna try to do this in Microsoft Word, but somehow I feel like there's a better way...

- Jon

I want to convert the ENTIRE file...

- Jon

you only have to do a subject if you are quoting, if you are replying you don't.

Since you are copying it from a website, you can strip out the returns. Then do a find and replace to get rid of all the extra stuff that you don't need, such as the titles, the parenthesis, etc.

Then you can do a trim function in excel, do another find and replace on the spaces and make them commas. Save as a csv file.

Gotya. Am working on it, but holy cow - Word, Notepad, and WordPad are all horrible at the search/replace thing...

- Jon

jbauer,
I would use Notepad instead of Word so make sure you to end up with any extraneous formating.

Use the Find/Replace feature to get rid of what you don't want.

Don't worry about the order of the data on each line at first. Just get it there.

Name, Latitude, Longitude

Once you get it to that stage. THEN open in in Excel if you want and you can move the columns to the correct order.

If you could send me a sample file that you want converted, I'll work on it or try to write a prgram to convert it. Just send me an email by clicking on my name and click on 'Contact'. I'll give you an email address to send the file.

Incidentally, for Garmin csv files, the Longitude comes first followed by the Latitude, Name, and Comment.

RT

X2 on that, never use anything but notepad. It ensures that there is no formatting being attached.

Ok, I'll try to use Notepad. My PC basically went into deep thinking mode on the first attempt at search/replace... It's a pretty big file.

Here's the note I just sent to RT...

Here's a sample:

;1030000198066
[POI]
Type=0x0100
Label=Marine View, Orewa, Rodney
Street Address=Marine View
City=Orewa
Region=Rodney
Country=NZ
Data0=(-36.58463540835,174.69547901665)
[END]

;1030000006692
[POI]
Type=0x0200
Label=MacMillan Road, Great Barrier Island, Auckland
Street Address=MacMillan Road
City=Great Barrier Island
Region=Auckland
Country=NZ
Data0=(-36.2556044816574,175.437840842381)
[END]

;1030000017418
[POI]
Type=0x0300
Label=Bledisloe Court, Pukekohe, Franklin
Street Address=Bledisloe Court
City=Pukekohe
Region=Franklin
Country=NZ
Data0=(-37.2085217272816,174.901417935713)
[END]

;1030000021388
[POI]
Type=0x0400
Label=Norbert Street, Brown Owl, Upper Hutt
Street Address=Norbert Street
City=Brown Owl
Region=Upper Hutt
Country=NZ
Data0=(-41.1039438167,175.0950794333)
[END]

;1030000055873
[POI]
Type=0x0500
Label=Glentui Bennetts Road, Starvation Hill, Waimakariri
Street Address=Glentui Bennetts Road
City=Starvation Hill
Region=Waimakariri
Country=NZ
Data0=(-43.254845722949,172.289051259352)
[END]

;1030000017419
[POI]
Type=0x0600
Label=Birdwood Road, Pukekohe, Franklin
Street Address=Birdwood Road
City=Pukekohe
Region=Franklin
Country=NZ
Data0=(-37.1935396667,174.89082095)
[END]

;1030000006693
[POI]
Type=0x0700
Label=Station Rock Road, Great Barrier Island, Auckland
Street Address=Station Rock Road
City=Great Barrier Island
Region=Auckland
Country=NZ
Data0=(-36.2914533132241,175.504054182774)
[END]

;1030000021389
[POI]
Type=0x0800
Label=Nicolaus Street, Trentham, Upper Hutt
Street Address=Nicolaus Street
City=Trentham
Region=Upper Hutt
Country=NZ
Data0=(-41.1303719013792,175.046942019476)
[END]

;1030000058813
[POI]
Type=0x0900
Label=Rimu Street, Toi Toi, Nelson
Street Address=Rimu Street
City=Toi Toi
Region=Nelson
Country=NZ
Data0=(-41.2797322203506,173.269071551892)
[END]

;1030000209502
[POI]
Type=0x0a00
Label=SH 35, Opotiki, Opotiki
Street Address=SH 35
City=Opotiki
Region=Opotiki
Country=NZ
Data0=(-38.0065997448695,177.28711879097)
[END]

;1030000055874
[POI]
Type=0x0b00
Label=Lewis Road, View Hill, Waimakariri
Street Address=Lewis Road
City=View Hill
Region=Waimakariri
Country=NZ
Data0=(-43.2868304535842,172.05481163357)
[END]

;1030000198068
[POI]
Type=0x0c00
Label=Francis Skinner Place, Orewa, Rodney
Street Address=Francis Skinner Place
City=Orewa
Region=Rodney
Country=NZ
Data0=(-36.5882108532885,174.690528489049)
[END]

;1030000006694
[POI]
Type=0x0d00
Label=Mulberry Grove Road, Great Barrier Island, Auckland
Street Address=Mulberry Grove Road
City=Great Barrier Island
Region=Auckland
Country=NZ
Data0=(-36.310428881306,175.495799363136)
[END]

;1030000209503
[POI]
Type=0x0e00
Label=SH 35, Opotiki-Potaka, Opotiki
Street Address=SH 35
City=Opotiki-Potaka
Region=Opotiki
Country=NZ
Data0=(-37.7695233667,177.6795776)
[END]

I think the final needs to look something like:

174.69547901665,-36.58463540835,"Marine View","Orewa","Marine View, Orewa, Rodney"

But I'm not positive on that final format - my goal is to be able to search for a street name on my Zumo, which for some reason, isn't working now, with the maps I've loaded from those NZ Open Map project ones...

- Jon

Notepad chokes on this file. It's 13MB.

- Jon

Are you using XP? If not, that is what you need to do.

Yep. Am running XP Professional.

- Jon

I thought that XP Pro notepad could handle it, must say that I don't work with file quite that large. You may have to break it up.

Long, Lat, Name, Comments

Given the info in your

174.69547901665,-36.58463540835,"Marine View","Orewa","Marine View, Orewa, Rodney"

you have 5 fields.

Should be more like

174.69547901665,-36.58463540835,"Marine View","Orewa, Marine View, Orewa, Rodney"

you may want to alter the entries, since Marine View seems to be the street, you could remove it from the comment field. You also wouldn't need the city listed twice. ie.

174.69547901665,-36.58463540835,"Marine View","Orewa, Rodney"

OR

174.69547901665,-36.58463540835,"Marine View,Orewa, Rodney",""

That would give you 3 fields and the fourth with no info in it.

I'm sure RT will have you fixed up in no time.

Yes, I created these .csv files 1st by using Notepad, Then I use seach / replace to fix what I can, rest has to be by hand.. Then I bring it in excel to check my formatting and corect the placement of the info.

Once I do that, I then use poi verifier to correct the rest for me.. It does take some work but once you get the hang of it, it go fast. Good Luck!

Trying to do a search and replace on a 13MB file is quite a task. My poor CPU is crying.

I've gotten big sections of the file loaded into Excel, but am trying to get the rest in there. 3MB out of 13MB are done so far...

Once I get it into Excel, it will be fun to try and manipulate some more...

Thanks for the help all!

- Jon

Too big? What about Editpad Pro. It comes with windows 2000, and am sure it is probably on XP.

Tried TextPad - I'm going into over an hour of waiting for my latest search/replace to finish...

- Jon

gees that is a big file!

I can get NotePad to take it, although it takes a bit to load. But slicing it into 4 files is still to large for Excel.

Is this a one-time conversion you're doing; or is it's an on-going conversion making it worthwile to write a program?

I never did understand why excel had a limit of rows that it could handle. some of the files that we transfer from one software package to another (in our business) are too large to open in excel, frustrates the heck out of me.

For me, it's a one time conversion, but the guys on here (http://gwprojects.org/forum/index.php) would probably love to have a custom conversion program...

- Jon

If I am correct in this matter the new Excel 2007 now has over a 1,000,000 rows instead of the usual 65,000

Yup. Looks like you are right!

https://www.cpa2biz.com/Store/ACT/Data+Analysis/Articles/Bes...

- Jon

I can't afford those expensive programs. I'm still trying to work with Excel 2002!!!!!!

2002????????? I am still on 2000...lol.

Well if I didn't get it for free by attending one of Microsoft Events, I would be still using 2000. I understand where you are coming from "very well"

Has anyone tried OpenOffice.. Its very good product and its free.. Looks just like MS Office apps but like I said, Its free..

http://www.openoffice.org/

Since it's a one-time conversion, I just converted this file. I sent a 'NZStreetPOImap_V.zip' file to you.

RT

RT, YOU ARE AWESOME!

It works LIKE A CHAMP!!!

So I have now loaded EVERY New Zealand street POI into my Zumo. Each POI marks a street. I can't search for them by street number, but the street should get me close enough!!!

FANTASTIC! That must have been a huge amount of work. I tried for most of the day to get it going, and I wasn't even half way there!

THANK YOU, THANK YOU, THANK YOU, THANK YOU. RT, you are the "bees knees."

- Jon

Yes, I have run into Open Office at several school districts, and over all they work ok. However, I have noticed and I can't remember any specific examples where I ran into issues with their "Excel" program.

BTW, I uploaded a newer version of this - deleted some blank entries. I also ran the POI verifier program and didn't find any biggies...

- Jon

Here's the procedure I used to convert this format to Garmin's POI format, using XP Pro Notepad and Excel 2002:

1. Since the row limit for Excel 2002 is 65536, I used Notepad to keep halving until each file had less than 65536 rows. I ended up with 16 files.
2. Using an Excel macro on each of the 16 files, I converted rows to columns. (If anyone wants this macro, send me an email and I'll send it via an attachment.)
3. Using Excel, I combined all 16 files into one file and ended up with 65389 rows by 10 columns (just fit into Excel's row limit with approximately 150 rows to spare).
4. Using Excel I deleted all columns except 'Label=', 'Street Address=', and 'Data0='.
5. Using Excel's RIGHT function, I stripped away the equal sign and all text to its left. For the label column, enter in cell D1 the formula:
=RIGHT(A1,LEN(A1)-6)
6. Highlite the D cells and do a EDIT\FILL\DOWN
7. Copy the D cells to E using COPY and PASTE SPECIAL\VALUES. Finally, cut and paste the E cells into the A cells. Delete the D cells.(Sometimes you can copy and PASTE SPECIAL directly to the A cells, but you run the risk of corrupting the data.)
8. Repeat steps 5 through 7 for cells B and C using the following formulas:
For Street Address, use: =RIGHT(B1,LEN(B1)-15)
For Data0, use: =RIGHT(C1,LEN(C1)-7)
9. Now remove the trailing character in the Data0 column by repeating steps 5 through 7 using the formula: =LEFT(C1,LEN(C1)-1)
10. To split the data column, highlite the C column and use DATA\TEXT TO COLUMNS\DELIMITED\COMMA
11. Highlite the correct cell to move, then cut and INSERT\CUT CELLS until you get the following arrangement:
Column 1 (Longitude, positive number in this case).
Column 2 (Latitude).
Column 3 (Street Name).
Column 4(Complete Address).
12. Highlite Rows 65390 to the end and delete.
13. It's a good idea to highlite and delete several columns to the right of the data. If this isn't done, unwanted commas may appear after the data when viewed with Notepad.
14. Save as a csv file.

When I ran 'POI File Verifier' I noticed there were 24 rows with no street, city, or region data. jbauer removed these before posting the POI file.

RT

I wrote a Perl script that will generate a gpx file from your input file. Two questions: 1) what is the name of the waypoint? 2) do you want a symbol assisoted with each waypint? Here is a list of symbols: http://home.online.no/~sigurdhu/12MAP_symbols.htm. Just email me your input file and I'll convert it for you.