4

I'm trying to import a point located geophysics data file (.dat) in QGIS 3.18, which uses fixed width columns instead of character based delimiters. Excel has a feature to import these data, but I could not find a matching method in QGIS.

An example of the data, which is "Point located data (dat) [100.7 MB]" from http://pid.geoscience.gov.au/dataset/ga/133023

   195099   1950999925.00         5338572  115.112162  -21.662860           2.950          -7.859         -10.809        9787594.02           2.950          -7.859            0.05            2.67          211.91          220.82          245.01          241.71         500.000           1.500           1.500            0.06            6.00           1.500           1.500             0.5               4             1               7               7               7               3               7               7               7               3 ONSLOW A/S PS25 WRKSHP          B  TR  GDA94  MSL         GRS80    B   DEG  m  m     m       um      m  m     um        m  m      m      m      um      m     m      um        MAN    SUR   ELN  W98    SUR     ELN  DFA     AUSGEOID98  GRS80    PEO                 99999 31-DEC-99      31-DEC-99  Pre March 2003 value:978773.71mg (Isogal65 "O")                                         
   195099   1950999926.00         4427037  118.631315  -20.375324           8.000           6.110          -1.890        9786311.72           8.000           6.110            0.05            2.67         -249.54         -256.47         -243.97         -252.93         500.000           1.500           1.500            0.06            6.00           1.500           1.500             0.5               4             1               7               7               7               3               7               7               7               3 PORT HEDLAND A/S TERM  PS26     B  TR  GDA94  MSL         GRS80    B   DEG  m  m     m       um      m  m     um        m  m      m      m      um      m     m      um        MAN    SUR   ELN  W98    SUR     ELN  DFA     AUSGEOID98  GRS80    PEO                 99999 31-DEC-99      31-DEC-99  Pre March 2003 value:978645.56mg (Isogal65 "O")                                         
   195105   1951053320.00         5338583  116.114682  -23.759529         335.760         324.240         -11.520        9787736.92         335.760         324.240            0.18            2.67           32.32         -335.10           67.88         -307.83         100.000          10.000          10.000            0.06            5.00          10.000          10.000             0.5               3             0               4               4               4               3               3               4               4               3 EDMUND GATE 33-20               B  TR  GDA94  MSL         GRS80    B   DEG  m  m     m       um      m  m     um        m  m      m      m      um      m     m      um        MAN    PGE   ELN  W98    PGE     ELN  DFA     AUSGEOID98  GRS80    NOR          TNK413       31-DEC-99      31-DEC-99  Pre March 2003 value:978787.96mg (Isogal65 "O")                                         
   195205   1952050030.00         5338837  115.780630  -32.103506          12.800         -19.849         -32.649        9793973.77          12.800         -19.849            0.70            2.67        -1006.26         -983.74         -905.64         -919.96         100.000           2.000           2.060            0.06            2.00           2.000           2.060             0.5               3             0               7               7               1               3               7               7               1               3 3                               D  TR  GDA94  MSL         GRS80    B   DEG  m  m     m       um      m  m     um        m  m      m      m      um      m     m      um        DIG    MIB   ELN  W98    ALT     ELN  DFA     AUSGEOID98  GRS80           99999        99999 31-DEC-99      31-DEC-99  Comment is null                                                                         
   195205   1952050100.00         5803151  116.011741  -32.149894          56.690          26.251         -30.439        9794633.12          56.690          26.251            4.17            2.67         -242.42         -272.19         -148.57         -212.01         100.000           2.000           2.060            0.06            2.00           2.000           2.060             0.5               3             0               7               7               1               3               7               7               1               3 10                              D  TR  GDA94  MSL         GRS80    B   DEG  m  m     m       um      m  m     um        m  m      m      m      um      m     m      um        DIG    MIB   ELN  W98    ALT     ELN  DFA     AUSGEOID98  GRS80           99999        99999 31-DEC-99      31-DEC-99  Comment is null                                                                         
   195205   1952050150.00         4428660  115.898128  -31.961563          16.150         -15.925         -32.075        9793758.65          16.150         -15.925            0.87            2.67        -1093.95        -1075.89         -995.10        -1013.17         100.000           2.000           2.060            0.06            2.00           2.000           2.060             0.5               3             0               7               7               1               3               7               7               1               3 15                              D  TR  GDA94  MSL         GRS80    B   DEG  m  m     m       um      m  m     um        m  m      m      m      um      m     m      um        DIG    MIB   ELN  W98    ALT     ELN  DFA     AUSGEOID98  GRS80           99999        99999 31-DEC-99      31-DEC-99  Comment is null

I've tried a mix of tabs, spaces, etc delimiter options, but the comment / text fields include arbitrary space characters. Additionally, the full file is too large to open in Excel and save as a character delimited text file.

I would like to successfully import this file. Perhaps I've missed an option, plugin, or a regular expression I could use? I could edit the file to add delimiters using a script, but that would not be a generalised solution.

Taras
  • 32,823
  • 4
  • 66
  • 137
LSgeo
  • 143
  • 5
  • 1
    Have a go at the 'Regular expression delimiter'. Regular expressions are often used for parsing/sorting/playing around with text. Have a read up on Regular expressions and how you can split/merge text. You will want to look for something like 'If character and not number, and then only one space'..... if that makes sense. – nr_aus Oct 22 '21 at 08:13
  • It looks to me as if every field is separated by at least 2 spaces, whereas spaces within comments/text fields are just one space. If this is so, it should be possible to separate the different fields but I will leave it to those more expert than me to suggest an expression. – Leigh Bettenay Oct 22 '21 at 09:24
  • @LeighBettenay I thought that, too, but some fields (not only comments) separated by one space. Search for TNK413 for example. – Kadir Şahbaz Oct 22 '21 at 11:08
  • 1
    @KadirŞahbaz good call. Also, there are some text strings which contain double spaces- possibly as typo mistakes e.g. line 10 "PORT HEDLAND A/S TERM PS26". Note the data source also contains a "ddf" file which could possibly be used to define a "CSVT" file including field lengths but it is a complicated structure. – Leigh Bettenay Oct 23 '21 at 05:14

3 Answers3

5

As pointed out above and in the comments the ddf file in the download contains the file structure which can be used to construct the following regex:

(.{14}) (.{16})(.{15})(.{12})(.{12})(.{16})(.{16})(.{16})(.{18})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{14})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{16})(.{32})(.{3})(.{4})(.{7})(.{12})(.{9})(.{4})(.{5})(.{3})(.{6})(.{8})(.{8})(.{3})(.{6})(.{10})(.{3})(.{7})(.{7})(.{7})(.{8})(.{6})(.{7})(.{10})(.{7})(.{7})(.{5})(.{7})(.{8})(.{5})(.{8})(.{12})(.{9})(.{13})(.{13})(.{15})(.{11})

which will parse your data. It's a bit messy in that it doesn't include any field names, but it does seem to work when used with the settings shown on this screenshot: enter image description here

ian
  • 1,713
  • 1
  • 11
  • 21
  • 2
    Accepting this as the answer to my posted question, as it directly implements a fixed width csv parsing method in QGIS 3 (provided you know the correct column widths, or can interpret the data correctly).

    I suspect I will personally implement Thomas' answer, which provides a useful mechanism for interpreting the associated metadata file (.ddf) for header information.

     – LSgeo Oct 25 '21 at 07:15
4

Ian's approach is very interesting (upvoted it) but you'll have to do a lot of manual work to figure out the column width and column names.

If you know how to use Python, you could transform the dat file to a csv file while using the column specification and header names from the ddf file (provided with the dataset; see Leigh Bettenay's answer) automatically. And then open the (comma-separated) csv file in QGIS.

  1. read ddf file and extract column specification and header names
  2. read dat file with pandas in chunks
    • write first chunk with header to csv file
    • append all other chunks to the same csv file
from os.path import join
import pandas as pd  # pip install pandas

DATA_FOLDER = r"/home/user_name/Projects/Playground/stackgis/data/"


DAT_FILE = join(DATA_FOLDER, r"2019_National_Gravity_Grids_Ground_Gravity_PLD.dat")
DDF_FILE = join(DATA_FOLDER, r"2019_National_Gravity_Grids_Ground_Gravity_PLD.ddf")


CSV_FILE = DAT_FILE.replace(".dat", ".csv")


column_headers = []
column_specs = []


with open(DDF_FILE, "r") as ddf_file:
    next(ddf_file)  # skip first line
    for row in ddf_file:
        name, cols, *_ = row.split(" ")  # get column name and column specs
        column_headers.append(name)
        f, t = [int(c)-1 for c in cols.split("-")]
        column_specs.append((f, t))


reader = pd.read_fwf(DAT_FILE, colspecs=column_specs, iterator=True, chunksize=1000)


first_chunk = next(reader)
first_chunk.columns = column_headers
first_chunk.to_csv(CSV_FILE)


for chunk in reader:
    chunk.to_csv(CSV_FILE, mode="a", header=False)

NB. The dat file is processed in chunks to avoid memory issues.

Note: It won't work out of the box in the QGIS Python console as pandas does not come with the default QGIS Python environment. This link may help you: Installing external python packages for QGIS 3?.

In case you only need certain columns:

preserved_columns = [
    "SURVEY_NUMBER",
    "STATION_NUMBER",
    "OBSERVATION_NUMBER",
    "LONGITUDE",
    "LATITUDE"
]

first_chunk = next(reader)
first_chunk.columns = column_headers
first_chunk[preserved_columns].to_csv(CSV_FILE)


for chunk in reader:
    chunk.columns = column_headers  # needed to filter columns
    chunk[preserved_columns].to_csv(CSV_FILE, mode="a", header=False)

For completeness, if you are using geopandas, you could also convert the transformed csv file to a shapefile:

import geopandas as gpd  # pip install geopandas (using conda recommended though)

DATA_FOLDER = r"/home/user_name/Projects/Playground/stackgis/data/"


CSV_FILE = join(DATA_FOLDER, r"2019_National_Gravity_Grids_Ground_Gravity_PLD.csv")
SHP_FILE = CSV_FILE.replace(".csv", ".shp")


df = pd.read_csv(CSV_FILE)
gdf = gpd.GeoDataFrame(
    df, 
    geometry=gpd.points_from_xy(df["LONGITUDE"], df["LATITUDE"]), 
    crs="EPSG:4283"  # https://d28rz98at9flks.cloudfront.net/133023/133023_12_0.pdf
)
gdf = gdf.drop(columns=["LATITUDE", "LONGITUDE"])
gdf.to_file(SHP_FILE)

I could edit the file to add delimiters using a script, but that would not be a generalised solution.

After re-reading your question, I realized that I somehow overlooked this part of your question. Looks like you are not looking for a script. Oh well, hope my answer helps someone else.

Thomas
  • 1,735
  • 11
  • 26
  • This is still very useful - I hadn't noticed the ddf file containing "header" information, and this method should work for other .dat / .ddf files provided you can specify the file paths. That means it's generalised enough for other files! – LSgeo Oct 25 '21 at 07:13
3

You say you want a general solution and you would prefer not to edit or use a script. However, I'm going to suggest exactly that because the "general solution" is to use a dedicated geophysical program for which this (gravity) data is intended. This is an easy file structure to read for geophysical programs- they use the associated "ddf" file as the key (based I think on the 1980's vintage Intrepid program of BHP and others).

You can open the "ddf" file in something like Notepad++ and confirm all useful information is in the first 36 fields in the fixed-width format with no delimiter. None of these is a character field (in fact only the first 10 fields are important in a GIS environment- the rest are qualifying fields).

As noted in the comments to your question, there are problems in importing all fields because of variable spaces (and also typographic mistakes which result in double spaces in some text fields). To get around this, you could truncate each line at the end of field 36 (character 549) using a cut command. I used sed -e "s/(.{549}).*/\1.../" infile > outfile1. Note this needs to be double quoted in a windows command shell but is usually single quoted.

Then you can use a regular expression to do a global replacement in the truncated file of any 1 or more white spaces with a single TAB character. Using sed this would be sed -e “s/ +/\t/g” outfile1 > outfile2. You need to trap 1 or more spaces because there is a change at record 72,828 from 2 spaces to 1 space between the second and third field. This results in file with TAB-delimited fields.

Finally, you can open the file with Notepad++ and edit in a first line containing field names separated by tabs, by reference to the "ddf" file (note the first field is a dummy field and is empty in the approach I have suggested here). Then it becomes a straightforward csv import- though a little slow given there are 1,835,351 points. I appreciate this is all a bit cumbersome but it does achieve the job- I've confirmed that. Hopefully, others can suggest more elegant solutions.

Leigh Bettenay
  • 1,401
  • 8
  • 16