importing data from a CSV file to an SQL Table , Not working well

Question

hello everyone , 

i'm trying to convert my csv file to an sql table ,

the issue is like this , i have a table in my sql server containing this columns :  id    | user_name | entities 

and a csv file with allo this data , ( columns separated by ; and data in the same column separated by , ) 

and in the entities part i might have for some users like 100 entites , separated by a comma , when i importa data , that doesn't go well , all the columns are like in a chaos , so can you please help me to figure this out , here is an exemple of data related to some user  : user1 in my csv file 

1 ; user1 ; entity1, entity2, entity3 ...entity100 

i want it to bacame like this in my sql table : 

id    | user_name | entities 

1    | user1          |  entity1 

2    | user1          |  entity2

....

100| users1       | enty100 

and so go on for the other users , thank you so much for all the help you'are providing 

Answer 1

Hello,

How do you Import the CSV file? For the required result you can use the UNPIVOT function, see FROM - Using PIVOT and UNPIVOT

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hi Rania,

    After importing the data into a table by using column separation with " ; "

write a new function to split comma separated  values .

using that function you can insert the data into original table 

Please see the below link to get the function to split comma separated values in the column.

https://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx

Note : In sql server 2016 there is an inbuilt function to do the operation  

( STRING_SPLIT ( string , separator ) )

Sample Query Will look like 

select * from tablename cross apply SplitString(columntosplit,',')

Answer 3

thank you soo much for your answer , i'm gonna try to do this methode , but unfortunattly , i have an error , while trying to do the importation : 

Erreur 0xc02020a1,Erreur 0xc020902a :  and  that's because  my entity is so longue , so it ends up by beeing truncated 

i'm using nvarchar(max) for thios column ? do you have any ideas on how to solve this issue ? 

  

Answer 4

thenk you , it's working , i've just changed the type to Text and now it's working , i was able to importe data 

now i want to separate it , but it's not working : 

SELECT [Id_User], [UserName]
FROM [dbo].[Users_Accounts]  
    CROSS APPLY STRING_SPLIT([Entity], '|');  ; 

it gives me the following message : 

Nom d'objet 'STRING_SPLIT' non valide.

 

thank you all (again ) :) 

Answer 5

Assuming you are using a earlier version of SQL SERVER that doesn't support STRING_SPLIT (pre 2016), check out Erland's article here for alternatives that can be used.

http://www.sommarskog.se/arrays-in-sql-2005.html#iterative 

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 6

Hi RaniaD,

   Please create a new function in the database to split the string 

https://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx

go through the link you will get a user defined function form the link .. create that function and use that one instead of the inbuilt function (which is only supported in 2016)

Please mark the above a answer if that is  helped you  ...