skip to Main Content

This is Users table

CREATE TABLE Users
(
    Id INT PRIMARY KEY IDENTITY(0,1),
    Name NVARCHAR(20) NOT NULL,
    Surname NVARCHAR(25) NOT NULL,
    Email NVARCHAR(30), 
    Facebook NVARCHAR(30),

    CHECK(Email IS NOT NULL OR Facebook IS NOT NULL)
);

This is BULK INSERT

BULK INSERT Users
FROM 'C:UsersSAMIRDownloadsTelegram Desktopusers.txt'
WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = 'n',
      --FIRSTROW = 0,
      --UTF-8
      CODEPAGE = '65001'
);

So this is Users.txt file data:

`1, N'Alex', N'Mituchin', N'[email protected]', NULL`

When I load data from the file it sets Username to values like N'Alex'. But I want to have the data simply like Alex. How can I fix this problem?

2

Answers


  1. I recommend loading data into a staging table where are values are strings.

    Then you can use a simply query to get the final results. In this case, you can do:

    select (case when name like 'N''%'''
                 then substring(name, 2, len(name) - 3)
                 else name
            end) as name
    from staging
    
    Login or Signup to reply.
  2. There’s a better option for this. If the string delimiters and unicode indicators are consistent (they’re present on all rows), you should use a format file where you can indicate delimites for each column. This will allow you to set , N' as delimiter between the first and second columns, ', N' as delimiter for the second and third columns, and so on.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search