Hi @maxbuiaus, looks like the text data is a single line without newlines, and every item is delimited by space.
If so, a possible way is:
- Read the whole text at once.
- Find every check-in entry from the text and store them into a list attribute.
- Decompose the feature into multiple features, each of which contains a check-in entry (i.e. list element).
- Split a check-in entry into each item (user, check-in time, latitude, longitude, and location id).
- Create Point from the coordinates (longitude, latitude).
In regular expressions, /s (backslash and lowercase s) indicates a whitespace character, /S (backslash and uppercase S) indicates a non-whitespace character, and + means "one or more of preceding character".
Hi @maxbuiaus, looks like the text data is a single line without newlines, and every item is delimited by space.
If so, a possible way is:
- Read the whole text at once.
- Find every check-in entry from the text and store them into a list attribute.
- Decompose the feature into multiple features, each of which contains a check-in entry (i.e. list element).
- Split a check-in entry into each item (user, check-in time, latitude, longitude, and location id).
- Create Point from the coordinates (longitude, latitude).
In regular expressions, /s (backslash and lowercase s) indicates a whitespace character, /S (backslash and uppercase S) indicates a non-whitespace character, and + means "one or more of preceding character".
For this kind of translation, sometimes JSON operations could also be effective.
Â
JSON Template Expression (XQuery expression):
Â
J
    let $data := fn:tokenize(fme:get-attribute("text_line_data"), '\s+')
    for $i in (1 to fn:count($data)) where ($i - 1) mod 5 eq 0
    return {
        "type" : "Feature",
        "geometry" : {
            "type" : "Point",
            "coordinates" : <
                xs:double($data $i + 3]),
                xs:double($data $i + 2])
            ]
        },
        "properties" : {
            "user" : $dataa$i],
            "check-in time" : $data $i + 1],
            "latitude" : $data $i + 2],
            "longitude" : $data2$i + 3],
            "location id" : $dataÂ$i + 4]
        }
    }
]
Â
Â
Thx you for your great support @takashi
Hi @maxbuiaus, looks like the text data is a single line without newlines, and every item is delimited by space.
If so, a possible way is:
- Read the whole text at once.
- Find every check-in entry from the text and store them into a list attribute.
- Decompose the feature into multiple features, each of which contains a check-in entry (i.e. list element).
- Split a check-in entry into each item (user, check-in time, latitude, longitude, and location id).
- Create Point from the coordinates (longitude, latitude).
In regular expressions, /s (backslash and lowercase s) indicates a whitespace character, /S (backslash and uppercase S) indicates a non-whitespace character, and + means "one or more of preceding character".
Thx you for your great support @takashi
Â
Â
I have tried your workflow, however it is not successful. I have attached a small part of the data bellow. Can you pls have look into this case
Â
test.txt
The sample text you have posted at first is just a single string line containing space separated values, but the new one you have attached is a table with tab-separated values. Since its format is completely different from the first sample, naturally my solution which is based on the first sample doesn't work.
Â
Â
If the format of the new sample data was correct, you could just read it with the CSV reader simply. When adding the reader to the workspace, select "tab" as the Delimiter Character, and leave the Field Names Line blank (or select "None" in FME 2018).
Â
Note: The 617th line in the sample data seems to be broken.
Â
Â
In this kind of question, it's highly recommended to post a minimal and valid sample data created in the same format as the actual data at first, in order to get a quick and valid answer.
Â
Â
Â
Â
Â
Hi @maxbuiaus, looks like the text data is a single line without newlines, and every item is delimited by space.
If so, a possible way is:
- Read the whole text at once.
- Find every check-in entry from the text and store them into a list attribute.
- Decompose the feature into multiple features, each of which contains a check-in entry (i.e. list element).
- Split a check-in entry into each item (user, check-in time, latitude, longitude, and location id).
- Create Point from the coordinates (longitude, latitude).
In regular expressions, /s (backslash and lowercase s) indicates a whitespace character, /S (backslash and uppercase S) indicates a non-whitespace character, and + means "one or more of preceding character".
The sample text you have posted at first is just a single string line containing space separated values, but the new one you have attached is a table with tab-separated values. Since its format is completely different from the first sample, naturally my solution which is based on the first sample doesn't work.
Â
Â
If the format of the new sample data was correct, you could just read it with the CSV reader simply. When adding the reader to the workspace, select "tab" as the Delimiter Character, and leave the Field Names Line blank (or select "None" in FME 2018).
Â
Note: The 617th line in the sample data seems to be broken.
Â
Â
In this kind of question, it's highly recommended to post a minimal and valid sample data created in the same format as the actual data at first, in order to get a quick and valid answer.
Â
Â
Â
Â
Â
Thank you for your great support, @takashi. I now can make it
Btw, with the UTC time in column 7, how can I convert to Local datetime (YYYYMMDDHHMMSS). Do you have any suggestion? I am totally new to FME
Thank you for your great support, @takashi.  I now can make it
Btw, with the UTC time in column 7, how can I convert to Local datetime (YYYYMMDDHHMMSS). Do you have any suggestion? I am totally new to FME
Assuming you are using FME 2017.1 or later.
Â
Firstly convert format of the input date/time value to the standard FME data/time format with the DateTimeConverter.
Â
- Input Format: "%Y-%m-%dT%H:%M:%S%Ez$" (ISO format with offset)
- Output Format: "FME" (auto), or "%Y%m%d%H%M%S%z" (FME format with offset)
You now have the UTC date/time value with FME format with timezone offset: YYYYmmddHHMMSS+00:00
Â
You can then convert it to local date/time with @TimeZoneSet function, and remove the timezone offset suffix with the @TimeZoneRemove function. Try setting this expression to the Attribute Value column in an AttributeCreator or an AttributeManager. Assume an attribute called "_datetime" stores the UTC date/time value formatted in standard FME format with timezone offset.
Â
@TimeZoneRemove(@TimeZoneSet(@Value(_datetime),local))
See these links to learn more about FME Date/Time functions and Standard FME Date/Time Format.
Â
Addition: In advanced use of FME Date/Time functions, this single expression does the entire conversion above at once.Â
Â
@TimeZoneRemove(@TimeZoneSet(@DateTimeParse(@Value(_datetime),%Y-%m-%dT%H:%M:%S%Ez$),local))
