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 1.1.1.1 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 1.1.1.1
the csv example:
username,name,ip,title
ABCD12G,david,hertz,1.1.1.1,''
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?