Convert CSV stored in a string variable to table

Question

I've got CSV data stored in a string variable in SQL:

@csvContent = 
'date;id;name;position;street;city
 19.03.2019  10:06:00;1;Max;President;Langestr. 35;Berlin
 19.04.2019  12:36:00;2;Bernd;Vice President;Haupstr. 40;Münster
 21.06.2019  14:30:00;3;Franziska;financial;Hofstr. 19;Frankfurt'

What I want to do is to convert it to a #table, so it would look like

SELECT * FROM #table

 date                 id  name   position       street        city
 ---------------------------------------------------------------------
 19.03.2019  10:06:00 1   Max    President      Langestr. 35  Berlin
 19.04.2019  12:36:00 2   Bernd  Vice President Haupstr. 40   Münster
 21.06.2019  14:30:00 3   Franzi financial      Hofstr. 19    Frankfurt

The headers aren't fixed so the CSV could have more or less columns with differnt Header names.

I've tried it with split_string() and pivot but didn't find a solution for this.

1. By saying "the headers aren't fixed", do you mean that the table needs to be dynamically created? 2. Where did the string variable come from?--i.e., is there an underlying question of, say, how to load a CSV file into a table? — rd_nielsen, Mar 19 '19 at 14:37
Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Esteban P., Mar 19 '19 at 14:37
No the value comes from a stored procedure call with the string (CSV), BULK INSERT is not possible. Yes the table has to be dynamicly there could be more or less columns in the CSV — Alexander Brühl, Mar 19 '19 at 20:41

score 0 · Answer 1 · answered Mar 19 '19 at 14:35

0

If you are using SQL server, this might be a solution for your request:

How to split a comma-separated value to columns

answered Mar 19 '19 at 14:35

Esteban P.

2,789
2
27
43

I already tried these Solutions but they all depend on fixed amount of column Headers which is not the case in my Problem here – Alexander Brühl Mar 19 '19 at 20:43

score 0 · Answer 2 · answered Mar 27 '19 at 06:38

Hope it will help you

    CREATE TABLE #temp(
        date date,
        id int ,
        name varchar(100), 
       . ....... //create column that you needed
    )
DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #temp
        FROM ''' + @CSVFILE+ ''' WITH 
        ( 
            FIELDTERMINATOR ='';'',
            ROWTERMINATOR =''\n'', 
            FIRSTROW = 2
         )';

        EXEC(@sql);

        SELECT *FROM #temp
        DROP TABLE #temp

Convert CSV stored in a string variable to table

2 Answers2