I am trying to load a txt file containing weather data of the following format:
year city country avg_temp 1820 AL United States 9.86 1821 LA United States 12.36 1822 DE United States 18.03 1823 BR United States 20.36 1824 XY United States 21.65
While using LOAD, 'States' is being populated in the field avg_temp. I understand that it is considering 'United' as Country and 'states' as avg_temp. Is there any way to fix this?
LOAD
left([@1:n],4) as year,
right([@1:n],4) as avg_temp,
subfield([@1:n],' ',2) as city,
if(SubStringCount([@1:n],' ')>3,subfield([@1:n],' ',3)&
' '&subfield([@1:n],' ',4),subfield([@1:n],' ',3)) as country
FROM
[C:\Users\frank\Desktop\Neues Textdokument (3).txt]
(fix, codepage is 28591, embedded labels);
@Frank_Hartmann I like the approach of doing your own parsing using SubField(). I might modify it to take advantage of the fact that SubField can take a negative index, meaning counting from the right.
