Sql-server – specify delimiter in openrowset bulk insert

bulk-insertopenrowsetsql serversql-server-2016xml

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?

Best Answer

but if one of the fields in the csv files contains a comma( , )

If all lines contains a comma, the solution can be

<ROW>  
  <COLUMN SOURCE="1" NAME="username" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="name1" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="name2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="IP" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="5" NAME="title" xsi:type="SQLNVARCHAR"/>  
</ROW>

INSERT INTO [player table]
SELECT 20190331, 
       username, 
       name1+','+name2, 
       IP, 
       title 
FROM  OPENROWSET( BULK 'D:\folder\2019\03-31\Player_statistics.csv', 
                  FIRSTROW = 2, 
                  FORMATFILE='D:\folder\test\xml\Player_statistics.xml') t1

If only some lines contains commas, and none title is null/empty, then try

INSERT INTO [player table]
SELECT 20190331, 
       `username`, 
       CASE WHEN title IS NULL 
            THEN name1
            ELSE name1+','+name2 END, 
       CASE WHEN title IS NULL 
            THEN name2
            ELSE IP END, 
       CASE WHEN title IS NULL 
            THEN IP
            ELSE title END
       IP, 
       title 
FROM  OPENROWSET( BULK 'D:\folder\2019\03-31\Player_statistics.csv', 
                  FIRSTROW = 2, 
                  FORMATFILE='D:\folder\test\xml\Player_statistics.xml') t1

If some title is null/empty, I cannot find simple way to distinguish.

UPDATE

The solution can be: divide your CSV on two fields only - username and the whole slack, the slack then can be divided to separate fields using common string functions in the SELECT part of importing query (maybe with using intermediate variables in chain calculations).

Related Solutions

Sql-server – Bulk Insert into view and find missing values

For the purpose of this answer, I am assuming your INSERT statement looks something like this:

INSERT INTO
  dbo.vSubCategories (CategoryName, SubCategory)
SELECT
  CategoryName,
  SubCategory
FROM
  ...  /* reading rows either from the CSV directly or
          from a staging table into which the CSV has been imported */
;

So, the first thing your trigger needs to do is to insert the (new) categories into tbCategories:

INSERT INTO
  dbo.tbCategories (CategoryName)
SELECT
  CategoryName
FROM
  inserted
EXCEPT  -- do not insert already existing category names
SELECT
  CategoryName
FROM
  dbo.tbCategories
;

Now all the category names in the imported dataset can be mapped to their IDs using the tbCategories table. And that is what the trigger should do when inserting the subcategories:

INSERT INTO
  dbo.tbSubCategories (CategoryID, SubCategory)
SELECT
  c.CategoryID,
  n.SubCategory
FROM
  inserted AS n
  INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
EXCEPT  -- exclude existing sub-categories, just in case
SELECT
  CategoryID,
  SubCategory
FROM
  dbo.tbSubCategories
;

For reference, here is a complete trigger script:

CREATE TRIGGER dbo.vSubCategories_InsteadOfInsert
ON dbo.vSubCategories
INSTEAD OF INSERT
AS
BEGIN
  -- insert categories
  INSERT INTO
    dbo.tbCategories (CategoryName)
  SELECT
    CategoryName
  FROM
    inserted
  EXCEPT  -- do not insert already existing category names
  SELECT
    CategoryName
  FROM
    dbo.tbCategories
  ;
  -- insert sub-categories
  INSERT INTO
    dbo.tbSubCategories (CategoryID, SubCategory)
   SELECT
    c.CategoryID,
    n.SubCategory
  FROM
    inserted AS n
    INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
  EXCEPT  -- exclude existing sub-categories, just in case
  SELECT
    CategoryID,
    SubCategory
  FROM
    dbo.tbSubCategories
  ;
END;

Sql-server – Importing Data in Parallel in SQL Server

Borrowing from my old answer

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 and up then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

Best Answer

Related Solutions

Sql-server – Bulk Insert into view and find missing values

Sql-server – Importing Data in Parallel in SQL Server

Related Question