4

I have a table defined as such:

CREATE TABLE [dbo].[IpMetadata](
    [StartIp] [bigint] NOT NULL,
    [EndIp] [bigint] NOT NULL,
    [CountryCode] [char](10) NOT NULL,
    [ProxyType] [varchar](50) NULL,
    [ProxyDescription] [varchar](50) NULL,
    [IspName] [varchar](100) NULL,
    [MobileCarrier] [varchar](50) NULL,
    [MobileCarrierCode] [varchar](50) NULL,
    [Latitude] [varchar](50) NULL,
    [Longitude] [varchar](50) NULL,
    [PostalCode] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [Region] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [GmtOffset] [varchar](50) NULL,
    [SupportsDaylightSavings] [char](10) NULL,
    [MetroCode] [varchar](50) NULL,
    [AddressCount] [int] NOT NULL,
     CONSTRAINT [PK_IpMetadata] PRIMARY KEY CLUSTERED 
    (
        [StartIp] ASC,
        [EndIp] ASC
    )
)

I have a UTF-8 encoded sampling file (D:\data\ipsnip.csv) with tab-delimited, CRLF terminated rows to insert into this table like so:

#start-ip   end-ip  edge-two-letter-country proxy-type  proxy-description   isp-name    mobile-carrier  mobile-carrier-code edge-latitude   edge-longitude  edge-postal-code    edge-city   edge-region edge-country    edge-gmt-offset edge-in-dst edge-metro-code address-count
0   0   **                  0   0   0   0   reserved    *** *** +9999   n   -1  0
1   255 **                  0   0   0   0   reserved    *** *** +9999   n   -1  254
256 16777215    **                  0   0   0   0   reserved    *** *** +9999   n   -1  16776959
16777216    16777343    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   127
16777344    16777407    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777408    16777471    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777472    16778239    cn          chinanet fujian province network        0   26.0786 119.298 350000  fuzhou  35  chn +800    n   156115  767
16778240    16779263    au          big red group       0   -37.8387    144.99  3141    south yarra vic aus +1000   n   36206   1023
16779264    16781311    cn          chinanet guangdong province network     0   30.6611 104.082 510000  guangzhou   44  chn +800    n   156196  2047
16781312    16785407    jp          i2ts inc.       0   35.6838 139.754 100-0001    tokyo   13  jpn +900    n   -1  4095

I run the BCP command like so:

bcp MyDatabase.dbo.IpMetadata in D:\data\ipsnip.csv -F2 -Slocalhost -n -T

I get a response back like so:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

BCP copy in failed

I have tried explicitly specifying the column and row terminators. I have tried using unicode column definitions. I have tried changing the line ends to LF instead of CRLF. I have tried replacing the field terminators with semicolons/ pipes. I have tried -n and-N. I don't know what else to try. Can anyone assist?

  • I would start by verifying the integrity of the file - for instance loading it into Excel and checking that all rows have the same number of tab characters. Do you need to escape any tab characters? – Daniel Hutmacher Jun 7 '17 at 13:36
  • there's 187 tabs, 11 rows... it divides evenly, so that doesn't seem to be it. – Jeremy Holovacs Jun 7 '17 at 14:14
  • Does column 1 really have a hash in it? Have you tried with only 1 row of data? Or -w for unicode characters (I assume you checked that but thought it's worth mentioning) – Jacob H Jun 7 '17 at 14:37
  • The header column has a hash in it, but nothing else does. The original file has 13M records, so I figured I'd use a handful to test with, but I haven't reduced it to just one row. I'll try it out and report if that makes any difference. – Jeremy Holovacs Jun 7 '17 at 15:02
  • I have reduced to four lines and it still doesn't work. There seems to be an issue with either -n or -N parameter when using your data. It doesn't seem to be native UTF-8 at all. I tried saving the file as UTF-8, UTF-16 with and without BOM, native UTF and even UTF-16 big endian, but bcp doesn't seem to care. – John aka hot2use Jun 7 '17 at 15:08
2

So it turns out that the -n and the -N are not what I wanted; I had assumed it used the db metadata to implicitly convert the incoming data, but it looks like it's expecting binary data with that.

I changed it to -c and it worked without issue.

|improve this answer|||||
  • Why was it not what you wanted? Is -c required for tables that contain character columns? – Travis Jul 14 '18 at 0:35
  • it's used when dealing with characters in general, i.e. text files. – Jeremy Holovacs Jul 15 '18 at 14:56
0

Jacob's comment under the question helped me!

Does column 1 really have a hash in it? Have you tried with only 1 row of data? > Or -w for unicode characters (I assume you checked that but thought it's worth mentioning) – Jacob H Jun 7 '17 at 14:37

Adding -w in the code helped to import my files.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.