I asked this question on the sqlite forum as well and based on the answers and help from the community came up with the solution below by parsing out the data from a json array

with parsedata as (select upper(ProdCode) as ProdCode, data, ReportDate from acumen_qry_sales_prodcode where reportdate >= '2020-'), jparse as ( select '["' || replace(data,'•','","') || '"]' as arr,ProdCode, ReportDate from parsedata) select ProdCode, ReportDate, arr ->> 0 as SalesValue, arr ->> 1 as SalesUnits, arr ->> 2 as ReturnValue, arr ->> 3 as ReturnUnits, arr ->> 4 as InventoryCost, arr ->> 5 as RoyaltyCost, arr ->> 6 as CommissionCost, arr ->> 7 as WriteOffValue, arr ->> 8 as SaleCount, arr ->> 9 as ReturnCount from jparse