POI CSV Splitting Application

Mike,

Thanks very much, I don't see how it could get any easier than this. Works perfect on the couple of files I've tried so far.

Good to hear... Let me know if you have any problems

Mike

Well, I just did Redlight-Cameras.csv and tried to put in "TX followed by a space and got an error "Runtime error 52 Bad file name or number". I'm sure the " did it but it showed up in the Col# 4 box so I put it in to limit the search.

Yeah, the " might be throwing it off... I'll look into that maybe later tonight...

Edit: The way I've seen the POI CSV files working is like this:

Lat,Lon,"Name","Comment"

I made the assumption that no quotation marks were allowed inside of the Name or Comment fields because those actually bound the comment field. Does the CSV file you have actually have quotes WITHIN the quotes? Like This:

Lat, Lon, "Name = "Me" ", "Comment"

Mike

Just tried the Speed-Cameras file and you definitely can't use the quotes as a character to search for, I tried using the space afterwards and it won't work because the program removes all trailing and leading spaces. Perhaps I'll re-complile tonight and leave out that routine.

Mike

No " that I see in the file. Maybe you might just not display the quote in the Col box although there may be a need someday to enter a quote. Whatever you decide will be fine.

I get "Runtime Error "5" Invalid Procedure Call or Argument" when doing (1 state TX Col 4) on file Cracker Barrel.csv

I also got run time errors on several files.

mmullins98 on the Cracker Barrel file you will see I have the state in front of the name in column 3.You can easily pull out in state using sort for that column.

Thanks, I have done that in the past but now am just trying out the new splitting program.

Thanks again.

Using "Wal-Mart_US.csv" (this is the file under the title "Wal-Mart, Sams clubs & Wal-Mart Supercenters in one file") there are three rows that are left out when searching col # 4 with "Watch for a comma" checked and Pull out only one State TX.

The missing rows contain:
Sam's Club #6453
Super WalMart #3826
Wal-Mart #283

All 428 stores in Texas will be selected if you leave the "Watch for a comma" field unchecked.

Why? The string "TX" appears before a comma in the rows with the three "missing" stores:

"9700 N Capital Of TX Hwy, Austin, TX 78759"
"1911 Marsha Sharp Freeway, Lubbock (Txtech), TX 79415, (806) 747-3454"
"795 Tx Ave, Bridge City, TX 77611, (409) 735-2417"
 
 
raptrflite's program is working as designed

The program may be working as designed but this is a bug and should be changed. An additional change in the program code could check to see if there is a comma before TX in the rest of the row and then not delete the row.

Nice catch Waasup! You saved me some debugging time.

The format used in the CSV file was:
lat,lng,name,"comment"

The line for Jensen, FL doesn't have quotes around the 4th column. I have added some error checking and tried to beef it up a little to allow changes like this, but I also made the comma search work a little better, and made it watch for multiple occurances of the text in a column.

If the app spits out an error now it will show you the line in was stuck on. Check that line for consistancy with the rest of the file and if it's fine, let me know the file and the line it stopped on.

Just ran the Wal-Mart file and it finds way too many matches. Example: looking for ",LA" it finds ",Las Vegas" and calls it a hit. I'll need to modify the app to allow you to type in a format like this: ',XX,' that will look for comma-state-comma, or 'State=XX' to look for 'State=' then the state.

I'm at work now, so I'll work on it over lunch break, but not sure how far I'll get.. gotta eat too!

Mike

Now you have to make it work with gpx files too!

lol... maybe once we get most of the oddball things in CSV files taken into account I'll look into it.

OK, where are the "split" files located?

They should be in the same folder that the original CSV was in.

Updated version....

Ok. While my work simulations were running and I had nothing to do but stare at the " XX% complete" window I updated the Splitter.

The install is here:
http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

Or if you have already installed, just replace the EXE with this:
http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

Lots of changes!

1) Case sensitivity - This is pretty important as it will prevent a lot of false hits (Usually, provided the file was written with state abbrevs capitalized). Try the Wal-Mart/Sam's Club file without it!!

2) Search Format - Let's say you know the state abbreviation appears like this every time: ',"OH'
Select the ',"XX' search format and it will look for that pattern exclusively and will fill in the XX with the state abbreviation. If you don't see one in the list that will work, just type your own and use XX as the placeholder for the state.

3) Results Output: This is no longer just a line. When the search completes, the results box will fill with info to let you know which lines were never written to a file, and which ones were written more than once.

4) 'Show Commas': Since the separating commas are often a good hint to finding the state, you can choose to see them in the First-Line preview.

5) Added Canadian Province abbrevs. - For our friends to the North

Notes:
1) The code for comma-watching is no longer in there. The code is strict to the format you provide

2) You may find a couple of lines (or maybe MANY) that were not written and won't write no matter WHAT format you try. These will have to be edited by hand. There's only so much automation you can do in those cases.

3) If the number of lines written is greater than the number of total lines, that means some of the lines were written to more than one file. See the output results for which ones were written multiple times.

Mike

Now we're talking. Fixed all known problems so far. Thank you so much.

I'll continue testing more now.

YW! Unfortunately, there's only so much we'll be able to do with it because of the number of ways that each line in the file can vary.

Thanx for the input and testing

One of these files may be Starbucks.csv from Starbucks Coffee - US. I can't get any state out of it, it may be operator error but if you have time you might take a look at it.

Thanks

there are a large number of combined state poi's that do not have a state or even a city listed. So you may find this will only work on some files.

Thanks for that! Tested it with Canadian Tire and Costco.
Works great to split data for different provinces!

One suggestion - how about a "Divide into all Provinces" option?
(or have an all States/Provinces drop-down)

Ran into a problem using "Divide into all States" where files for U.S. states are sometimes created when the source data is all-Canadian.

For example, Alberta street data creates a Nebraska file because often street names have NE (Northeast) in them:

3516-8th Ave NE,Calgary AB T2A 6K5,403-248-6400

Does it still write the Alberta file in addition to the Nebraska file? I'll add something to the effect of a States only/ provinces only flag. I'll look at the Starbucks, too.

I modified the interface so that now you select [All], or [US Only], or [Canada Only], or the state or province you want, then press the 'Go' button. You can also enter any word or phrase there that you want like "Washington" and it will look for that.

Since the Starbucks file has full state names, those will have to be pulled one at a time for now using the state name in the search field. Maybe later I'll get the drive together to add state/province names to the code...

http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

Mike

Thanks again, Mike!

The "[Canada Only]" option works perfectly.

One thing I noticed if that if you use a word/phrase for the search, you must also specify which state/province/territory in "Search for:" to name the output file.

If you use "[All]", it will create 72 files with the search results, one for each state/province/territory (even American Samoa ).

I have downloaded the file, but will not have a chance to check it out until tonight. I have been using Excel to do that type of work for me, and yes, there is some manipulation usually required to achieve the desired objective.

I don't go to StarBucks, but out of curosity, I downloaded the file, opened Excel and performed a "Data"/"Text to column" function using the semi-colon as a separator and whooooooeeeee, very quickly decided that there was some cleaning up that could be done. If nothing else, that action will show you potential problems with a particular entry. It might work on the GPS, but it sure isn't pretty. In this case, the file can now be sorted by Full State name (after corrections are made) and a column can be inserted beside it. I then type "AL" in the new column on the first line of Alabama, hightlight that entry, hold the shift key down, use the down arrow to scroll to the last entry for Alabama, release the shift key, press the "CTRL" key and hold it while pressing the "D" key. All of Alabama will now have the Correct State Designation. I just repeat that procedure for the other states.

To put the columns back together, insert a column or go to the first blank column, type [=X1&","&Z1] without the brackets and it will combine the columns. This is a formula and must be typed as shown with the exception of anything between the quotes. For instance the formula above will insert a comma between the two entries. A comma-space will add a comma and a space. Nothing will eliminate spaces between the two entries. Once combined, the file will have to be saved as a .csv which will eliminate all formating, OR, one can obtain a copy of the ASAP utilities which will provide the opportunity to highlight the column and convert the formula data to real data. The other columns can now be deleted leaving only the combined column.

There are many ways to accomplish the same objective and I an always looking for easier ones. I look forward to trying out your program.

Curt

Just curious... What us the data/text function?

FYI, if you have ASAP you can delete all rows that don't contain "alalama" without editing the records
to create the state file.

This new program should be great for those who don't use Excel/ASAP

It will convert a column of cells in one or more row(s) into multiple columns based one or more delimiters (tab,comma,blank,etc.)

For example a cell containing the string "a;b;c" would be split into three columns containing "a","b" "c" if a semicolon were specified as the delimiter.

And also for those of us who do

Thanks, raptrflite. This is a great little utility. Much appreciated.

CurtHinson/WAASup,

Thanks for the reply and explanation. I did not know you could do this to sub-divide a column.

Another great feature for the tool bag.

DRMCMTR, I should have mentioned that anyone who has not used the Data/Text to column feature of Excel and would like to try it out, be sure that the column to be split has enough columns to the right to accomodate all of the entries. If not, it will ask if you want to overwrite whatever is to the right of the column in question. The best procedure if to cut and paste it to the first empty column after all other data. If you do not have a character to be used as a delimiter, insert one with the global change feature (CTRL/H). In the find what box, type what ever you want to look for (ex: Alabama, Florida, line dance, whiskey) and in the change to what box, type what ever you wish it to be (Alabama to ,AL) or (Florida to ,Florida) or (line dance to line dance and one legged dance) or (whiskey to wine, women and song).

Curt

Raptrflite, that is a very neat little program. I like the way it auto creates a separate file for each state. Good work.

Curt

TY.. I'm glad it is useful. Waasup pointed out that it would be nice to be able to enter a manual filename for manual searches. I'll get to that sometime this weekend. Got family stuff to do

Mike

Mike, now that I know how manual searches work, I'm OK picking any state for the filename. I was just surprised at the results from picking "[All]".

Enjoy your family time! (I know I'll enjoy mine)

Update to CSV Splitter - June 8

http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

I went ahead and made a line to select the filename for the output just in case you want to try to use the program for a custom search (Like "Chicago" instead of a state). Also added search by state name as well as abbreviation, or both!

Also found that "Virginia" and "Washington" would include hits from "West Virginia" and "Washington DC", so I put some code in there to watch for that.

Hints and Tips:
- The 'Search Format' line is meant to modify the 'Search Word'. for example:
if Search Format = ",[SA]," and
Search Word = "AK",
the app will replace [SA] with AK and the final search term would be ",AK,". When you pick All, or Canada, or US, it iterated through all the states and replaces [SA] with the state abbreviation.

If [SA] and [State] do not appear in the format, then the format is ignored and the text in the Search Word box is used as the search term.

The Out File does the same thing, it replaces [SA] with 'AK' (for example) and [State] with 'Alaska' (for example)

- Try to enter a search format that will minimize the number of lines written more than once. I found that in the Starbucks file '; [State]' worked pretty well as opposed to just '[State]' which would find many of the streets named after states.

- NE (northeast) as in '45 NE 53rd street' is also the abbreviation for Nebraska, so that's a typical double hit. Hard to watch for that one.

The Starbucks.CSV file has MANY different formats, and has quite a few lines with missing info, so this program will get you mostly there, but you'll need to look through the lines not written, and the lines written twice and fix them manually

**Future(maybe)**
Maybe I can make it so the program will let you click on one of the lines written twice, or not written at all and append it to the file it goes in.So if the program outputs a line with "sc" which a case-sensitive state search missed as "SC", you would click on it and append it to the South Carolina file just generated by the program.

Whew! Sorry so long....

oh... btw: updated the install program with the new .EXE

http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

Mike

raptrflite (Mike if I may). I just downloaded the latest version and will be using it to work with my Shell Station files. Thanks for the hard work and if I may, I would like to treat you and the missus (family?) to dinner. If interested, let me know how (check, PayPal, etc.). I have done quite a bit of programming in my day and firmly believe that if something is worth having it is worth paying for.

Curt

Curt,

Definitely Mike

Thank you! I really appreciate the offer

but.... (always a 'but', right?)

I use SOOOO many freeware and open-source programs that I feel I am giving back to the net community by offering my services (however small). I really enjoy programming (most of the time ) and hope that you just enjoy the apps I put out.

Mike

Mike, I totally understand where you are coming from, but my feeling regarding this issue is no different than my feeling about supporting POI-Factory by being a member. DW and I travel considerable distances throughout the year, but I really do not feel the need to have the RED LIGHT and SPEED CAMERA files. I get them because it gives me a way to payback what I get from this site. Too old to get involved in programming anymore, so I just take it easy and try to help out where I can. I know that many (most?) times I come across as a KIA, but in many cases, I have probably forgotten more than many will ever know.

Thanks again for your efforts. I would also like to take the time to extend my thanks to everyone that has contributed to this site, or for that matter, those that use their time to provide services to any other site.

Time to get back to doing whatever it was I wasn't going to do, so have a nice day (everyday).

Curt

In the new version, when the program finishes splitting and lists the lines it didn't write, you can now select those lines and append them to the correct file after you edit the line! Also you can remove the copy of the line that was written more than once.

To do this:
1) Run a split
2) Look at the results listed in the textbox at the bottom. If a line wasn't written and you know where it should be, click the line (You can edit the line right there so that it is correct before you append it)
3) rt-click and select 'Add Line to File'
4) Choose the CSV file you want to append the line to in the selection box.
5) Done!

Same idea with removing a line. So, if the search wrote this line from the starbucks file:
"-76.51468,38.30077,Starbucks 23105 Three Notch Rd; California; Maryland 20619; 240-895-0361,"

to California and Maryland, just select this line, rt-click, "Cut From File", select the California file, and it will pull it out.

Also added an INI file so it saves the settings you had before.

I ran this with the Cracker Barrel file which had 4-5 lines not written and it made short work of adding those lines to the correct files after editing them.

http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

Let me know if there are any issues
Mike

I see that there are several issues within the poi file "Wendys_USA_Canada.csv". But after several attempts to remove consecutive commas and then process the file I cannot get past an error message "An unhandled error occured reading the file".

Also I notice if I pick a different file without restarting csvsplitter then the "First line of file" data does not change.

Thanks Mike, if you have time to take a look.

The link:

http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

brings back a Page has been deleted error.

It appears that v060909 has been replaced by v062309. The new link is:
http://sites.google.com/site/raptorbase/Home/POICSVSplitter_...

Sorry.. been busy...

I found the problem and updated the software. The Wendy's file had a lot of trailing commas in the file and it was throwing off the software..

Mike

Where is the updated software?