6

In Excel we have option called 'Text to columns' to split the data in a column.

Is there any option available in QGIS?

There is an option which splits data from left or right in QGIS but I want entire data to be split.

enter image description here

6
  • 1
    Are there always three values to split in COLUMN?
    – BERA
    Oct 28, 2021 at 7:16
  • @BERA Not three there are more some times
    – Bruno B
    Oct 28, 2021 at 7:24
  • 2
    Excel has a lot of columns to work with, your data does not, excel can have numbers, letters, formulas, OLE objects etc. in a column, your data can not, so it's obviously easier in Excel. Multiple field calcs with the python string.split() function using the first column .split('-')[index] where index is the number identifier of the column you have got would work or alternately you could do your text-to-columns in excel and join that table to your data using the single value you do have. Oct 28, 2021 at 7:42
  • 5
    you can use the processing tool "refactor fields", create new fields and for each one, paste the expression and adapt it a little : string_to_array("your_origin_column", '-')[0] (0 for the first new column, 1 for the second new column, 2 for ...). You can easily create a QGIS processing model with 6 new columns and if your data has only 3 data, the last 3 columns will be filled with a NULL value. Oct 28, 2021 at 11:46
  • 1
    @Taras: luckily, QGIS can import Excel spreadsheets too :D Oct 28, 2021 at 13:29

2 Answers 2

7

Use QGIS expressions to convert your string to an array containing each number as an element with string_to_array. You now can use array_get ([your_array],[index]) to return every element. Index starts with 0.

So the whole expression to get the third value (index=2) looks like ("column" is the name of the input field):

array_get (string_to_array ("column",'-'), 2)

For an input field containing 143-685-6749, the expression returns 6749. If you change the value of 2 at the end by 1, it returns 685, setting it to 0 returns 143.

enter image description here

4
  • what if we want to split data from the end. If we dont know the number of elements in the cell.
    – Bruno B
    Apr 13, 2023 at 2:56
  • Not sure what you mean. Maybe function array_length() is what you need
    – Babel
    Apr 13, 2023 at 7:18
  • what i mean is in above example we know that there are four elements or five elements. But in my dataset their are multiple elements seperated by - . I want to split the last element how can i proceed
    – Bruno B
    Apr 13, 2023 at 8:57
  • Depends very much an the very use case. Provide detailed information how exactly your data structure looks like and what output you expect. Best post a new question and be as precise as possible. To just get the last element of the array, use array_last().
    – Babel
    Apr 13, 2023 at 9:04
5

In case a PyQGIS approach is needed:

def text_to_columns(text_column_name, new_columns_name, delimiter): 
    layer = iface.activeLayer()
    if not layer.isEditable():
        layer.startEditing()

    splits = [feat[text_column_name].split(delimiter) for feat in layer.getFeatures() if feat[text_column_name] != NULL]
    max_split_length = len(max(splits, key=len))
    new_fields = []

    for i in range(1,max_split_length+1): 
        field_name = f'{new_columns_name}_{i}'
        new_field = QgsField(field_name, QVariant.String)
        if layer.fields().lookupField(field_name) == -1:
            layer.dataProvider().addAttributes([new_field])
            layer.updateFields()
        new_fields.append(field_name)

    for i, feat in enumerate(layer.getFeatures()): 
        if feat[text_column_name] == NULL: continue
        feat_text_parts = splits[i]
        for text_part, field in zip(feat_text_parts, new_fields): 
            feat[field] = text_part
        layer.updateFeature(feat)
    layer.commitChanges()

text_to_columns('COLUMN', 'NEW_COL', '-')

This will create the the fields depending on what the biggest array is after splitting the texts. The Script will create something like this: enter image description here

3
  • 1
    Nice but can be more optimized : the for feat in layer.getFeatures() [...] feat[text_column_name].split(delimiter) is done twice, a new_columns_name+'_'+i is more pythonic like that f"{new_columns_name}_{i}" and will add the layer as function argument. Oct 28, 2021 at 13:53
  • I am getting this error AttributeError: 'QVariant' object has no attribute 'split'
    – Taras
    Jan 20, 2022 at 10:08
  • I have edited my answer. The error occurs when you have a NULL value because NULL is a type of QVariant. You may need to import NULL from qgis.core when using the script from outside the qgis python console. Jan 20, 2022 at 11:42

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.