1

I am trying to download spatial data (points) from a site that offers it in two forms: a KML and a tab-separated list. Unfortunately when I download the KML file, the attributes table is basically blank, and even more importantly, I can't clip the points to a polygon in QGIS (I posted a question about this a while back called 'Can't clip point shapefile with missing attribute data', but have not resolved the issue yet).

My next step is to get the tab-separated list into a usable form like a csv file, with the ultimate goal of converting to a shapefile. I have tried saving the .pl (perl script) file that opens in the browser, and opening it in Xcode (the default) but it doesn't contain all the information it should (at least not that I can see). I also tried copying the text from the browser and pasting it in TextEdit, using Find and Replace to switch the tabs for commas, then saving the result as a csv, but the result was messy and thus not usable (there appears to be tabs in places where there shouldn't be which causes the data to misalign within the table).

Any suggestions on how to get this data into a usable form?

Here is the data (on the upper right, under 'Download Options').

3 Answers 3

3

Select all data (cmd/ctrl + A) and paste it in a text editor. In QGIS go to Layer / Add Layer / Add Delimiter Text Layer and select the optimal settings for your file, as this example (with the same data):

enter image description here

But be careful, the data is in NAD83 and WGS84 (and other not informed). You can change the datum in layer Properties, for example first to NAD83, select the right rows and use Save as to a new layer. Do the same with WGS4. You can reproject each layer to a unique datum with Save as option and after this merge both layers.

1
  • Thanks for the answer. Yours is better than mine because you get it straight into QGIS without the intermediate steps. And thanks for the suggestions about the data, I did notice that those issues so I appreciate the help.
    – ia200
    Jul 18, 2017 at 20:15
1

You can use Microsoft Excel, LibreOffice Calc, or (I'm guessing) any number of other spreadsheet programs to do this.

In Calc, you would:

  1. Save the downloaded file as a .TXT file.
  2. Open it in Calc and choose Tab as the Separator: enter image description here
  3. Go to File > Save As, choose Text CSV.
4
  • Thanks for the suggestion. I think for most common file types this would work well. This didn't work for me because the file, which opens in a web browser, is a .pl file. I am not familiar with this file type but if you save it as is, or as a .txt file all you get is a file about search results and saying no records were found. However, you did make me realize I over complicated my solution a bit. I'll update that to improve it.
    – ia200
    Jul 18, 2017 at 20:09
  • @ia200 It worked for me, I downloaded the file, saved as .TXT instead of .PL, and Calc opened it as a tab-delimited table. Really, the site shouldn't be giving you the file as a .PL file, because it isn't a PERL script, it's plain text.
    – Dan C
    Jul 18, 2017 at 20:13
  • Interesting, I'm not sure why I wasn't able to do that. I agree that the file shouldn't be a .pl but that is all that comes up when I hit cmd-s with on the webpage provided. Maybe I was doing something wrong when accessing the data.
    – ia200
    Jul 18, 2017 at 20:31
  • @ia200 No I think you're using the site correctly, I was saying that it's kinda the website's fault for confusing things by giving the user (you) a .PL file instead of a .TXT file when it is in fact a plain text file.
    – Dan C
    Jul 18, 2017 at 20:36
0

I actually figured out how to get the data in the correct format. After looking at the raw data again, I realized there are some comas present, which caused the issues when I converted to a csv. Since there were also semicolons I had to use an unusual symbol. In the end my solution was this:

  1. Copy all of the text from the webpage that opens when you click 'Retrieve selected records as tab-separated list' (ctr/cmd-a to highlight, ctr/cmd-c to copy).
  2. Paste text into TextEdit (similar word processing programs should work)
  3. Open Find and Replace (for TextEdit look under Edit -> Find... -> Find and Replace).
  4. In order to enter a tab into the Find search, copy and paste one from the text you just pasted (if you try to enter it directly it just takes you to the Replace line).
  5. Choose a symbol to replace the tabs with that isn't present in the data (I used '@' in this case).
  6. Replace all, and save the file. In TextEdit (and probably other word processors) it only allows you to save as one of several word files so I just saved it as the default .rtf
  7. Find the file in Windows Explorer/Finder and click the file so it allows you to change the name; simply add .csv to the end (it usually asks if you really want to do this, just say yes).
  8. Finally open the file in OpenOffice, Numbers, Excel etc. I used OpenOffice, which asks you what separator to use whenever you open a csv file. Since I had comas and semicolons in my data I unchecked those options and chose the custom option and entered the '@' symbol. If you are using something other than OpenOffice, you might have to look up how to change the separator.

This worked great for me, so hopefully this will help anyone else who has this same issue.

Update: After reading @Dan C's suggestion I realized I overcomplicated my solution: After step 2, skip to step 7 and then simply choose the 'tab' option as the separator when opening the csv file in step 8. This achieves the same thing but with less steps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.