Return to Question

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now. I have been pulling my hair out all day, and have yet to figure out a viable solution.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

Any ideas would be greatly appreciated.

edit for clarification: The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

EDIT: Accepted answer actually does not require the intermediate CSV, it can be done straight from original CSV. The INSTR is what I managed to get to work.

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT name, date, value, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

Accepted answer actually does not require the intermediate CSV, it can be done straight from original CSV. The INSTR is what I managed to get to work.

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT name, date, value, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now. I have been pulling my hair out all day, and have yet to figure out a viable solution.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

Any ideas would be greatly appreciated.

edit for clarification: The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

EDIT: I did not have the correct sqlite version to run the final edit, but the INSTR does work. Accepted answer actually does not require the intermediate CSV, it can be done straight from original CSV.

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT name, date, value, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now. I have been pulling my hair out all day, and have yet to figure out a viable solution.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

Any ideas would be greatly appreciated.

edit for clarification: The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

EDIT: Accepted answer actually does not require the intermediate CSV, it can be done straight from original CSV. The INSTR is what I managed to get to work.

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT name, date, value, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now. I have been pulling my hair out all day, and have yet to figure out a viable solution.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

Any ideas would be greatly appreciated.

edit for clarification: The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

I have a csv with "lat,lon" in a single column. I am trying to convert it with ogr2ogr to another format, and cannot figure out how to format the VRT file to accept this format.

I cannot do anything other than change the VRT at the moment due to other limitations. I would love to rewrite the csv, but this is the format that I have to deal with for now. I have been pulling my hair out all day, and have yet to figure out a viable solution.

example for formating:

name, date, value, latlon
a, 20130101, 0, "100.8\,40.2"
b, 20130101, 1, "101.2\,40.4"
c, 20130102, 1, "100.2\,41.2"
d, 20130103, 2, "102.0\,40.7"
e, 20130104, 3, "100.6\,41.0"

Any ideas would be greatly appreciated.

edit for clarification: The data is not a set number of characters, so splitting at a character number does not work right.

a, 20130101, 0, "10.844\,-40.2745"
b, 20130101, 1, "101.2010\,40.411111"
c, 20130102, 1, "-100.27867\,1.24522"
d, 20130103, 2, "-1.04577\,16.745637"
e, 20130104, 3, "100.617417\,-1.0757"

EDIT: I did not have the correct sqlite version to run the final edit, but the INSTR does work. Accepted answer actually does not require the intermediate CSV, it can be done straight from original CSV.

<OGRVRTDataSource>
    <OGRVRTLayer name="result">
        <SrcDataSource>test2.csv</SrcDataSource>
        <SrcSQL dialect="sqlite">SELECT name, date, value, SUBSTR(latlon,2,INSTR(latlon,"\,")-1) AS lat, SUBSTR(latlon,INSTR(latlon,"\,")+2,-1) AS lon FROM test2</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>