Browsed by
Category: Excel

Translating text for ArcGIS – Google Translate + Excel – A quick guide

Translating text for ArcGIS – Google Translate + Excel – A quick guide

This is a useful tip that has been pointed out to me by a colleague, so useful in fact I have to make sure I note it down somewhere.   Until my idea is incorporated into ArcGIS this is a quick and dirty workaround for translating Arabic labels in ArcGIS.

arcgis_arabic_translation
Yes, but what does it mean?

We have received some GIS data from a client, it’s landuse in a geodatabase with Arabic labels.  Trouble is our maps are needed in English!   In the past we’ve tested the Microsoft translation tools (for office) against Google’s online translate tool and found that Google does a much better translation.

So how do we translate this large landuse table quickly and easily?   Use Excel of course!   Please note that this bullet point list assumes you know ArcGIS and Excel quite well, to instruct from a beginners point of view would be a bit to long winded for me.

  1. First create a field name for the English Translation in ArcGIS
  2. Open a new Excel document
  3. Copy (using this method) the table from ArcGIS into Excel.
  4. Create a PivotTable that lists the row labels (in this case Arabic Landuse)
  5. Copy and paste this list out (so the text is static, you probably don’t have to)
  6. Copy the Arabic text into the text box at translate.google.com

    google_arabic_translation
    Google Translate
  7. Now Copy that English translation text list back adjacent to your Arabic landuse list into Excel

    excel_arabic_translation
    Pasted back into Excel
  8. Now you have to use some Excel magic, select the English and Arabic text and under the Formulas tab (in Excel 2007) define a name ( in this case I called it English Translate)
  9. Once you have done this go to the English Landuse field name column and type in code like this “=VLOOKUP(E7,EnglishTranslate,2)”.   E7 is the Arabic landuse in your original table in Excel, EnglishTranslate is that Name you defined above and the number 2 is the column number of the EnglishTranslate you need to use if matched.
  10. Then click and drag copy this down your English translation field to check it works.
  11. Now copy back this data into ArcGIS or Join/Link it.

Well that’s it if you have any improvements/questions/suggestions please add them in the comments section below!

CityEngine Area Test Rule using Excel (free download)

CityEngine Area Test Rule using Excel (free download)

Further to my recent post on using Excel to create rules for CityEngine (CityEngine rule creation using Excel):

Attached for download below is a zipped up macro enabled Excel2007 file, hopefully it’s self explanatory but if not leave a comment below and I’ll try and help.   The basic premise is that you should be able to easily add and modify this file by using excel’s ability to drag and copy.   Have a play with it and if you like it can you let me know? Please Note: Every effort on my part has been made to ensure this has no virus or malware associated with it (I use Microsoft Security Essentials and Malwarebytes Anti-Malware), however please ensure that you do your part to and use an appropriate security scanner too. 

Cityengine Area Test Xlsm
Cityengine Area Test Xlsm
cityengine_area_test_xlsm.zip
Version: 1
19.1 KiB
293 Downloads
Details...
Excel Macro – Convert Cell Fill Colour to Hexadecimal Code

Excel Macro – Convert Cell Fill Colour to Hexadecimal Code

Exciting code being posted today I know, this is related to a previous post (CityEngine rule creation using Excel) but could come in use in other projects that use hexadecimal colour codes…

First create a new macro called “ConvertToHex” and use the following code:

 

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim str0 As String, str As String
Dim cel As Range
For Each cel In Selection
str0 = Right("000000" & Hex(cel.Interior.Color), 6)
str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
cel = "#" & str & ""
Next cel
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Customize your toolbar and add this macro as a button, then select a cell and change its fill colour, then making sure you’ve still selected the colour cell press the macro button or run this macro, voila it should write in the cell it’s hexvalue!

CityEngine rule creation using Excel

CityEngine rule creation using Excel

Okay I’m a planner so I use Excel a lot especially for population statistics and analysis of landuses and planning standards.   It would be nice to turn some of our planning standards tables into rule files for CityEngine to make.  For now though I’m trying out a number of workflows to test plot sizes.

What I wanted to do was choose the colours of my plots based on its area, this method allows me to pick a cell fill colour in Excel and run a macro over it to create the hex code which using the magic of CONCATENATE function creates the correct syntax.

The Excel sheet you see above can then be copied and pasted into a CGA rule file in CityEngine, the results below show that the colours I’ve choosen have come in well in CityEngine….

Why use this method?  Well I’m thinking of combining some of our data within CityEngine and this could be a handy way of doing it.   Here I can relatively easily copy additional test areas once and excel, using formulas, can create the rest of the rule file.

I’ll be posting a test Excel sheet soon here, as well as the macro code for take a cell fill colour and creating the hexadecimal code from it.

UPDATE(02/08/2012) You can download the Macro-Enabled Spreadsheet from this post.

Top Tip: Creating a folder contents list in a simple text file

Top Tip: Creating a folder contents list in a simple text file

I have to use the keyboard?!

Updated 28/02/2012 to amend the syntax and remove instructions on making an xls file (Excel opens plain text files anyway)

Coming to the end of a project I realised I needed to create a table listing all of the files and folders within for that project.

This was a GIS (using ArcGIS) with many MXDs and associated data files, so the prospect of manually creating a list was a non-starter for me.

As with most discoveries laziness has been the driving factor in today’s “top tip”!

So I did a little research and with the helpful post located at HandyExcelTips.com found the perfect solution that has been around forever.   To be honest I don’t know why I haven’t known about this as it seems quite a simple request…

These instructions have been tried on Windows Vista only but I’m pretty sure Windows XP and 7 (works on Windows 7) will work just as well.   If not let me know in the comments section below!   The usual disclaimers about don’t try this if you’re not sure apply.

Okay so here is how to do it (step 1 for a list with all the heading and summary info and step 2 to just have a list):

Step 1 Create a simple text or Excel file listing files and folder in a particular folder:

  • Click start and in the start search text box type “cmd” and hit ‘enter’.
  • Next type in the Command Prompt box to change your location to the desire folder you wanted to make a list from e.g.

 

cd d:\test\list

  • Now to create a text file (which you can open in Excel) with the contents of the folder you have navigated to type this in:

dir > filelist.txt

Now you can use file explorer to open your file and folder list, with either Excel or Notepad!

Step 2 Create a simple text listing all sub-files and folders and without the header information or summary:
Following the first 2 steps above type this instead:

dir  /s/b > filelist.txt

Not rocket science I know but I have found it very useful.

Export attribute tables from ArcGIS to Excel.

Export attribute tables from ArcGIS to Excel.

Okay not the most exciting topics ever approached but hopefully this is useful.

  1. Open ArcMap and Excel
  2. ‘Right-Click’ on your polygon layer in ArcMap and select “Open Attribute Table”
  3. Now Click on the Options button and then “Select All”
  4. In the right left* hand grey column (see the small black arrow? click there) ‘Right-Click’ to see this menu:
  5. Now switch to a blank workspace in Excel and paste your data, voila!

Okay you can export this data as a file but after step 3, select ‘Export’ and select your file format dbase, txt, File and Personal Geodatabase tables. I tend not to use this method as I find copy and paste far simpler.

Updated: Click on the left column/border (in grey), thanks to Pepix for spotting my error, apologies for any confusion.