Need to get spatial data from tab-separated list into csv or similar

Question

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').

Answer 1

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):

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.

Answer 2

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:
3. Go to File > Save As, choose Text CSV.

Answer 3

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.