Converting text.csv file to multi-column/cell csv file format

 

I have recently joined this forum and it is interesting to read that others have been there, done that on my problem!

I have a Mio Spirit 689 satnav and I beieve it will accept 2,3 and maybe more column /csv format files for POI.

I have obtained a large file in text.csv format.
As it is, I suspect it will not import directly into the Mio. It is just one large continuous listing of text/numbers.
Also, it would probably be quite diffcult to view with the object of editing.
I had a look at it with MS Excel but al I mananged to do was create another format - crushing all the data into just one cell!
Could anyone please give me ideas/ directions on how to go about converting this data that would be meaningful to both me & my Mio?

thanks

overthetop

depends

It depends on how the source is formatted. Just because it is comma separated doesn't necessarily mean it will import cleanly into excel. Import is really the best Excel function to use for reading a text file, but a lot will depend on if the source has the records separated.

--
Illiterate? Write for free help.

welcome, be careful

Welcome to the forum. Of course, a text editor can edit a text file about as well as Excel can, maybe even better if you are trying to clean up a file to meet your needs. Be careful, any suggestion that the predominant approach of "just throw anything you want into any column, with no logic or even consistency" might not be the best way to do things will be met with great resistance.

@overthetop

Hi overthetop. Welcome to the monkey house!

I just opened one of my POI files in TurboCCC's awesome "Extra POI Editor" application (which we call "EPE" for short)...

http://turboccc.wikispaces.com/Extra_POI_Editor

...then did "Save As..." and chose "Mio CSV" as the file type. When I opened the file in Notepad I saw that it is a fairly standard 4-column CSV layout:

longitude,latitude,POI_name,address

...so an entry for the location of one of my fondest (though hazy) memories of my trip to London years ago would be

-0.1699,51.5164,The Monkey Puzzle,"30 Southwick St, London"

The first two fields are numbers, so they have no double-quotes around them. The third and fourth fields are text, and notice that the fourth field is surrounded by double-quotes while the third one is not. That's because the fourth field contains a comma itself, so it has to be surrounded by double-quotes so the comma it contains is not misinterpreted as a field separator.

If you are using Windows I highly recommend EPE. If you give us a sample of what the file looks like when you open it in Notepad we can probably help you open it in EPE and the save it as "Mio CSV".

Sample needed

overthetop,

Maybe if you supplied a sample line or 2, we could see if the file is too cobbled or maybe it's the way you are opening it.

--
Metricman DriveSmart 76 Williamsburg, VA

Send me your text file.

overthetop wrote:

I have obtained a large file in text.csv format.
As it is, I suspect it will not import directly into the Mio. It is just one large continuous listing of text/numbers.
Also, it would probably be quite diffcult to view with the object of editing.
I had a look at it with MS Excel but al I mananged to do was create another format - crushing all the data into just one cell!
Could anyone please give me ideas/ directions on how to go about converting this data that would be meaningful to both me & my Mio?

thanks

overthetop

I sent an email to your contact address. If you want to send me the text file (the file before you combined all fields), I'll attempt to convert it to a 4-column, Garmin formatted, csv file.

RT

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

Convert text.csv to Mio multi column sat nav data.csv

Thanks for replies.

The source file I used was obtained from http://speedcamerapoi.com/dl/uk-speedcamera.htm

I choose to download the txt.csv into Word and then transferred it into Excel. It put it into just one cell. Within Excel I was unable to turn the data into something meaningful.

However, I then used Excel 2003 to Import from Web, pointing to the website location and instructed Excel to download the file directly. It placed the data into just one column.
Once in Excel, I choose Text to Columns. That produced exactly what I beieve my Mio satnav needs.
The only questions remaining is: Do I use all 5 columns of data, or do I need only the first two columns (X and Y)? Can my Mio use the other info? Does the Mio even need it?

thanks again for your replies.

Brian

Converted

overthetop wrote:

The source file I used was obtained from http://speedcamerapoi.com/dl/uk-speedcamera.htm

I converted the text file to Garmin's 4-field format. Send me an email address (that will accept an attachment) using my contact address and I'll send you the file.

RT

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

I know what the problem is

One thing you should never do is transfer a file to MS Word and then take it into Excel.

Word has a way of hiding control characters in the file which royally screws up Excel.

Just open Excel and go "File + Open" and select the pure unadulterated .csv file that you downloaded from the website.

When you are finished manipulating the data, you can do a "Save As" and save it as a .csv file.

Notepad also works well (NOT Wordpad), but is cumbersome and can't sort.

--
Metricman DriveSmart 76 Williamsburg, VA

But what is the answer?

I know how to install individual custom POIs including negative values. If, for example, the East value is given as -1.2345 then I switch from East to West on the device and enter the value as 1.2345 (no negative sign).
However, if I have a large database in CSV format, how will the Mio Spirit Satnav handle it for custom POIs if many of the entries in the database have a lot of negative values when using the Mio desktop software?

I hope it doesn't require me to enter the POI coord values manually, just two values at a time.
Do I need to modify the database in some way or other, so that the database can be entered in as one large file using the Mio custom POIs software installing procedure?

thanks

Brian

overthetop

The file is on the way. It's in Garmin's 4-field format, i.e.:

-5.980036,54.597651,UK Spd Cams; Type=1,"SPEED=30, DIRTYPE=0, DIRECTION=0"

RT

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

"MioMore Desktop"

overthetop wrote:

I hope it doesn't require me to enter the POI coord values manually, just two values at a time.
Do I need to modify the database in some way or other, so that the database can be entered in as one large file using the Mio custom POIs software installing procedure?

Starting on page 71 of the manual at:
http://eu.mio.com/images/faq-images/Phoenix_Spirit_Series_UM...
it shows how to import csv POIs using their "MioMore Desktop" software.

I don't know the Mio required csv format; but it appears it's the same format as Navman uses, which appears to be the same as Garmin's format. If this is the case, you should be able to import the csv file I sent you by using their software.

If it doesn't load, let me know what csv format Mio requires and I'll modify the file for you.

Good luck.

RT

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