CSV File Breakdown

 

Hey Everyone,
I have been breaking down CSV's and trying to separate them in columns for a SQL database as of recent, but the CSV's only seem to be breaking down to the longitude, Latitude, State, Name, and the rest is meshed together (Phone number, address, fax number). Does anyone have any scripts that can break this down or a possible software? I definitely understand that different CSV's don't exactly match up to all other CSV's. Any idea's would be helpful. As of now.... I am trying to write C# scripts in visual basic, but want a more exact science.

Thanks,
Brandon

Extra POI Editor

EPE might do some of what you want.

A CSV file has 3 defined fields

bskaer1 wrote:

Hey Everyone,
I have been breaking down CSV's and trying to separate them in columns for a SQL database as of recent, but the CSV's only seem to be breaking down to the longitude, Latitude, State, Name, and the rest is meshed together (Phone number, address, fax number). Does anyone have any scripts that can break this down or a possible software? I definitely understand that different CSV's don't exactly match up to all other CSV's. Any idea's would be helpful. As of now.... I am trying to write C# scripts in visual basic, but want a more exact science.

Thanks,
Brandon

A Garmin CSV has 3 defined fields, Longitude, Latitude, and POI Name. the fourth field you are having issues with is an optional description field and is left up to the file creator to populate as desired. Most of the files here have standardized on street, city, state, zip and phone, but there is no set standard. If there is a GPX version of the file, it usually breaks down the elements to where they can be built into a database. But then, why do you want to reinvent the wheel?

--
Illiterate? Write for free help.

hmm...

I apologize as I am new to this. The basic concept of what im trying to do is break down a CSV... as many files are not GPX. I am trying to break down Column D in most Csv's on Poi factory into be separated columns. The remaining columns should be broken down to- one column for phone number, another for address, another for state, another for zip, and the last for possible intersection or any additional notes that are left.

At the current time I am having to separate the data myself, but I am trying to find a program that can do it for me.

excel

"Text to columns" works on some csv files.

--
1490LMT 1450LMT 295w

Maybe...

If the file is offered in 2 versions, you can convert the formatted .gpx file into a .csv file. This way you can control how the output is presented. Using Extra_POI_Editor, just select the m-column .csv option and place each field into it's own column.

--
Striving to make the NYC Metro area project the best.

The best ways to deal with this

I've dealt with this in a different context (in my case, working with POI Factory files to import them into a CSV format friendly to some smartphone and PDA GPS programs), but here's what I'd recommend:

a) Get a copy of Extra POI Editor--really, this IS the Swiss Army knife of POI conversion tools in such a way that it keeps the extra info in a usable format. grin

b) Go to Options > M-Column CSV Fields, then create a custom setup the way you will want the fields to appear in your SQL database, save this under a custom name like "SQL Friendly".

This way--as we'll see--EPE will do the conversion for you, at least to a friendlier CSV formatting.

In the case of GPX--open GPX in EPE, save as "M-Column CSV".

In the case of a pre-existing CSV, it's tricker but still doable:

a) Open up the CSV in a text editor (I recommend the free-as-in-beer-and-as-in-speech Notepad++) and see what format it has the comments in.

b) Open EPE, click Options > M-Column CSV Fields--there's usually options for the most common forms of formatting. Either open a pre-set or make another custom one and label it something like "Garmin Default" or the like.

c) THEN drag and drop your CSV file into EPE, it SHOULD import properly.

d) Go to Options > M-Column CSV Fields, select your custom "SQL Friendly" selection, close, then save as M-Column CSV and it should separate things out nicely for you.

Some stuff will need a bit of hand massage, but you can generally get it close to a "Garmin Standard" format such that EPE can convert it for you. I've successfully done this both for making my own POIs and for converting Garmin CSVs to a format that Sygic RUPI Converter can use (for Sygic Aura for smartphones and PDAs).

Try this procedure

bskaer1 wrote:

I apologize as I am new to this. The basic concept of what im trying to do is break down a CSV... as many files are not GPX. I am trying to break down Column D in most Csv's on Poi factory into be separated columns. The remaining columns should be broken down to- one column for phone number, another for address, another for state, another for zip, and the last for possible intersection or any additional notes that are left.

At the current time I am having to separate the data myself, but I am trying to find a program that can do it for me.

Step 1: Get a copy of EPE
What follows will be a link to EPE - but I would like you to use a special technique to go to the link – a technique that will keep this Exercise active and will also let you access Turbocc’s program.

When you click on the link below, hold down the CNTL key as you left click. This will open a new window in your browser under a new “Tab”. You can then come back to this Exercise by clicking on its tab in the browser.

1.1Access the program at:
http://turboccc.wikispaces.com/Extra_POI_Editor#toc5

1.2Scroll down about 2/3rds of the way on Turbocc’s webpage and download the latest “zip” file into a some new folder on your computer. Also, download the latest help file “zip”.
1.3Navigate to that folder and extract the files from the zip archive.
1.4 Then, from that folder, run Extra_POI_Editor.exe.

What you will see will be four panels within EPE that are currently empty. Before proceeding,

Step 2: Set preferences.
2.1 Click on Options in the menu,
2.2 Select “m-col CSV Fields”
2.3 Scroll down to 20 - user profile
>2.3a In col 5, find State (via down arrow)
>2.3b In col 6, find Post Code
>2.3c In col 7, find Phone
>2.3d In the "Change Selected Profile Name" at the top right, type "Separate the Columns"
>2.3e Click Ok
>2.3f Click close.
2.5 Still under Options - click on Preferences. We will be working in feet.
2.6 Under units, change the Editor setting to Imperial/US.
2.7 Over to the right under the CSV Output section, put a check mark in the “Remove Column Heading” check-box.
2.8 Finally, click Close.

Step 3: Open one of your smaller .csv files using EPE and see what happens when you save it as a m-column .csv file

as others have stated

bskaer1 wrote:

I apologize as I am new to this. The basic concept of what im trying to do is break down a CSV... as many files are not GPX. I am trying to break down Column D in most Csv's on Poi factory into be separated columns. The remaining columns should be broken down to- one column for phone number, another for address, another for state, another for zip, and the last for possible intersection or any additional notes that are left.

At the current time I am having to separate the data myself, but I am trying to find a program that can do it for me.

As others have suggested, Extra_POI_Editor has already tackled a lot of what you are seeking. The program is Windows only but can parse the fields separating different elements based on a standard deliminator. Once the CSV is parsed, the results can be exported into a multicolumn spreadsheet which can be the basis of your database.

--
Illiterate? Write for free help.

Red Lobster

Take a look at the Red Lobster file. Can EPE separate the fields when the delimiters are not all the same?

--
1490LMT 1450LMT 295w

conversion

I do it all the time, I use EXCEL to manipulate the files, use the state/province abbreviation as a starting point, build macros that convert the state/province to comma delimited fields(you might have to do this differently for each file), use the excel function to separate into columns, use the filter function on the state to locate records that don't fit, adjust those records separately.

The EPE works beautifully.

The EPE works beautifully. Have to do some excel macro's to get it to work perfectly after, but the EPE does 90% of the work. Thanks everyone for your suggestions and help.

MY Way

When I break down CSV files I go to the KISS principal.
So a person who wants to follow, download the "Acura Dealers in USA.CSV" POI file.
Then open it in Excel and then save it as txt.
Open in Word and then replace (control H) the tabs with commas. Then replace the paragraph marks with commas which makes for one long sentence. Then replace comma hyphen with paragraph hyphen and then save it and open in Excel and save as CSV. Out of the 270 POI's there are two mistakes because of two extra commas were in the original file. With Word one can break down where to place the comma with the replace function.
I hope I wrote it right.
As I was going grocery shopping I realized that instead of using comma it would be faster using TAB as the replacement with no error.

clarification

Not sure what each version of Word does, but this might help some people

when you are in the "edit and replace" mode

"Tab" is "^t" (without the quotes)

"Paragraph" is "^p" (without the quotes)