Jump to content

 
Photo

Editing street indexes

- - - - -

  • Please log in to reply
8 replies to this topic

#1
Charles Syrett

Charles Syrett

    Ultimate Contributor

  • Validated Member
  • PipPipPipPipPipPip
  • 537 posts
  • Canada

I've been asked to edit some street map indexes that list streets by name, followed by tab, followed by page and index grid reference. For example: Bloor St. W. (tab) 39--M14. The main thing I'm being asked to check is that the grid reference is corresponds to the correct page number. (In the example, I would ensure that M14 really is on page 39, and not on, say, 49.)

I'm tempted to bring the whole shebang into Excel and separate the pages from the grid refs as separate columns, but I'm not sure that there's any automatic way to do this. Sitting down and banging a tab manually in between each of these is not my idea of fun. Any Excel/Word experts out there that can suggest something?

Thanks!

Charles Syrett
Map Graphics
http://www.mapgraphics.com

#2
jrat

jrat

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 143 posts
  • Gender:Male
  • Location:Hagerstown, MD
  • United States

if your index is a set amount of characters you could use the LEFT and RIGHT functions. My excell is a bit rusty so check the help. But they can be used to get only the left/right most x characters.

#3
Paul H

Paul H

    Paul from JIMAPCO

  • Validated Member
  • PipPipPip
  • 74 posts
  • Location:Round Lake, NY
  • United States

I've been asked to edit some street map indexes that list streets by name, followed by tab, followed by page and index grid reference. For example: Bloor St. W. (tab) 39--M14. The main thing I'm being asked to check is that the grid reference is corresponds to the correct page number. (In the example, I would ensure that M14 really is on page 39, and not on, say, 49.)

I'm tempted to bring the whole shebang into Excel and separate the pages from the grid refs as separate columns, but I'm not sure that there's any automatic way to do this. Sitting down and banging a tab manually in between each of these is not my idea of fun. Any Excel/Word experts out there that can suggest something?

Thanks!

Charles Syrett
Map Graphics
http://www.mapgraphics.com


When opening the file in Excel the text import wizard will prompt you to choose Delimited as the original data type, then choose Tab as the delimeter.

#4
natcase

natcase

    Ultimate Contributor

  • Validated Member
  • PipPipPipPipPipPip
  • 571 posts
  • Gender:Male
  • Location:Minneapolis, Minnesota
  • Interests:cartography
    aeshetics
    cartographic design
    John Bartholomew
    road maps
    large-scale mapping
  • United States

Excel will import as tab delimited. You could use a formula using SEARCH and the LEFT and RIGHT functions. In this case,

LEFT ([source], SEARCH([source],"-",1)-1)

would return "39" and

RIGHT ([source], (LEN ([source]) - SEARCH([source],"-",2))

should return "M14".

Alternatively, you could replace all "--" with tab in a text editor and import as tab delimited.

For complex reconfiguring, I usually use FileMaker, but any decent database program would do it. I find it has more tools for selecting and altering. FME would do this too.

Nat Case
INCase, LLC

Minneapolis, Minnesota USA
maphead.blogspot.com



#5
Charles Syrett

Charles Syrett

    Ultimate Contributor

  • Validated Member
  • PipPipPipPipPipPip
  • 537 posts
  • Canada

Thanks, Nat. I don't actually know where to start on Excel formulas, so the simple Find>Replace option looks good. However, none of the apps I've tried -- TextEdit, Word, Excel -- will let me replace with tab. I just get bounced back up to the Find field. Is there some alternative way to enter tab into the Replace field so that it will actually replace with the tab?

Charles Syrett
Map Graphics
http://www.mapgraphics.com

Excel will import as tab delimited. You could use a formula using SEARCH and the LEFT and RIGHT functions. In this case,

LEFT ([source], SEARCH([source],"-",1)-1)

would return "39" and

RIGHT ([source], (LEN ([source]) - SEARCH([source],"-",2))

should return "M14".

Alternatively, you could replace all "--" with tab in a text editor and import as tab delimited.

For complex reconfiguring, I usually use FileMaker, but any decent database program would do it. I find it has more tools for selecting and altering. FME would do this too.



#6
Hans van der Maarel

Hans van der Maarel

    CartoTalk Editor-in-Chief

  • Admin
  • PipPipPipPipPipPipPip
  • 3,857 posts
  • Gender:Male
  • Location:The Netherlands
  • Interests:Cartography, GIS, history, popular science, music.
  • Netherlands

Yeah, this is the kind of stuff FME really can shine at. I've often tried to do similar things in Excel or OpenOffice and it's usually a lot of trial and error.

One thing you could try is importing as tab delimited, then replace the "--" in the 2nd column with some (single) character that won't occur anywhere in the index, like "@". Then re-save it as CSV but specify that same characted as seperator, and don't let it quote any output values. Re-import and specify the special character again as seperator. That *could* (would? should?) do the trick.
Hans van der Maarel - Cartotalk Editor
Red Geographics
Email: hans@redgeographics.com / Twitter: @redgeographics

#7
gregory

gregory

    Key Contributor

  • Validated Member
  • PipPipPip
  • 96 posts
  • Gender:Male
  • Location:Cracow
  • Poland

Charles,

I know from you activity at georeference.org that you're also a manifold user. Why don't you give a try ?
As far as I unterstood you problem you can do it using Copy firt token/ Copy last token in tabular view of your data.
http://www.manifold....ext_strings.htm

Hope it helps,
Gregory

#8
Unit Seven

Unit Seven

    Legendary Contributor

  • Moderator
  • PipPipPipPipPip
  • 266 posts
  • Gender:Male
  • Location:New Zealand
  • New Zealand

To do a find and replace with a tab in a text editor you need to type ^t for Ultraedit or InDesign to insert a tab character maybe this will work in Notepad as well....or in this case I would just open in excel and use Data > Text to columns to split the column at -- When I found this tool I found myself using it a lot.
S a m B r o w n

U N I T S E V E N
unit.seven@gmail.com

Miramar, Wellington
N E W Z E A L A N D

#9
Charles Syrett

Charles Syrett

    Ultimate Contributor

  • Validated Member
  • PipPipPipPipPipPip
  • 537 posts
  • Canada

....or in this case I would just open in excel and use Data > Text to columns to split the column at -- When I found this tool I found myself using it a lot.


Bingo -- that did it! Right under my nose the whole time. :rolleyes:

Charles Syrett
Map Graphics
http://www.mapgraphics.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

-->