0

Is there a way to import a csv file or transform a table with delimited compound values?

Example:

CSV file has: | years | model | | -------- | -------------- | | 2019,2020,2021 | ABC |

Database should end up like: | year | model | | -------- | -------------- | | 2019 | ABC | | 2020 | ABC | | 2021 | ABC |

I am using HeidiSQL as my database GUI, so if it works with that, great! Otherwise I can run it through the command line if necessary.

Casey
  • 536
  • 2
  • 14
  • 28
  • Table shows up correctly in editor, but not on question page. Can't figure out why. – Casey Feb 18 '22 at 19:38
  • Best practise from my point of view would be to load the data first as is into a staging table and afterwards split the values out in the target table on a second step. How to do best depends on a few facts like: are there always 3 years mentioned or can the number differ and stuff like that. Have a look at MID() function to get an idea on how the values can be extracted out of the cell. – cboden Feb 19 '22 at 10:48
  • 'is there a way to import a csv file with delimited compound values' - no there isn't, either transform before you load or load and transform – P.Salmon Feb 19 '22 at 11:04
  • @cboden the number of years differ, so I'm not sure I understand how the MID() function could be used. My understanding is that you have to specify the number of characters for the function, but that number isn't consistent to apply to all lines. – Casey Feb 21 '22 at 16:44
  • @P.Salmon updated the question, can you expand on your "load and transform" method? – Casey Feb 21 '22 at 16:45

1 Answers1

0

I found this answer that uses a query to transform the data as @cboden and @P.Salmon mentioned: https://stackoverflow.com/a/5493616/12197178

With some modifications I got it to work.

WITH tmp(id, make, DataItem, years) AS
(
SELECT
    id,
    make,
    LEFT(years, CHARINDEX(';', years + ';') - 1),
    STUFF(years, 1, CHARINDEX(';', years + ';'), '')
FROM Testdata
UNION all

SELECT
    id,
    make,
    LEFT(years, CHARINDEX(';', years + ';') - 1),
    STUFF(years, 1, CHARINDEX(';', years + ';'), '')
FROM tmp
WHERE
    years > ''
)
SELECT
    id,
    DataItem,
    make
FROM tmp
ORDER BY id;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option
Casey
  • 536
  • 2
  • 14
  • 28