none
bulk insert chokes on some forward slashes but not others RRS feed

  • Question

  • Table like so:

        [Hotelid] [int] NULL,
    [CountryCode] [nvarchar](10) NULL,
    [CountryName] [nvarchar](100) NULL,
    [State] [nvarchar](100) NULL,
    [CityName] [nvarchar](100) NULL,
    [DisplayName] [nvarchar](100) NULL,
    [Category] [int] NULL,
    [StarRating] [int] NULL,
    [AccomodationType] [nvarchar](250) NULL,
    [Address] [nvarchar](250) NULL,
    [ZipCode] [nvarchar](100) NULL,
    [Area] [nvarchar](100) NULL,
    [District] [nvarchar](100) NULL,
    [Chain] [nvarchar](100) NULL,
    [Lng] [nvarchar](100) NULL,
    [Lat] [nvarchar](100) NULL,
    [RoomCount] [int] NULL,
    [Phone] [nvarchar](50) NULL,
    [Fax] [nvarchar](50) NULL,
    [Email] [nchar](200) NULL,
    [Website] [nchar](200) NULL,
    [IsEUCountry] [nvarchar](10) NULL

    Have file to import like so:
                  
        HotelId|CountryCode|CountryName|State|CityName|DisplayName|Category|StarRating|AccomodationType|Address|ZipCode|Area|District|Chain|Lng|Lat|RoomCount|Phone|Fax|Email|WebSite|IsEUCountry

        4001530|AR|Argentina||San Bernando|Hostal Del Sol||4||Chiozza 1566,7111|||||-56.6772|-36.7016||54-02257-46-1164\/3372Rva 0810-4444-678 02944-527575|54-02257-46-1164/3372|||False


    bcp import command:

    BULK INSERT Table FROM  'File.csv' WITH ( FIRSTROW=2, FIELDTERMINATOR = '|', ERRORFILE = 'error.txt', MAXERRORS = 1000, ROWTERMINATOR = '\n')

    Error like so:

    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 18 (Phone).

    If I remove the forward slashes from the phone column, the row imports- it never complains about the forward slash in the Fax column, just the phone column.

    How do I escape the forward slashes in the Phone column or specify the command line, and why does it not care about the forward slash in the FAX column. This is immensely frustrating...
    Wednesday, June 21, 2017 12:40 PM

All replies