I'm trying to insert data from CSV files into tables using a cursor and open rowset bulk insert. this works for me 99% of the times but if one of the fields in the csv files contains a comma( , ) the data import is ruined and all the columns get imported one column after their place. For example it should look like this:
timestamp username name IP title 20190331 ABCD12G david hertz 126.96.36.199 null
But if i have a comma in the name column(for example) it will look like that:
timestamp username name IP title 20190331 ABCD12G david hertz 188.8.131.52
the csv example:
This is the syntax i'm using:
insert into [player table] select 20190331,* FROM OPENROWSET(BULK 'D:\folder\2019\03- 31\Player_statistics.csv', FIRSTROW = 2, FORMATFILE='D:\folder\test\xml\Player_statistics.xml') as t1
how can i change this to ignore the comma in the middle of a word?(i can't limit the CSV since its taken from another client that will not limit characters.
the xml i'm using for the import with openrowset:
when you perform regular bulk insert you have an option to specify the delimiter fielterminator = '","' , I could not find something like that in openrowset.
What can help fix this issue?