16

I'm trying to figure out why the BULK INSERT command I'm using isn't recognizing the FORMAT and FIELDQUOTE options used in the command. 

BULK INSERT dbo.tblM2016_RAW_Current_Import_File
FROM '\\x\tms\SCADA.dat'
WITH
(
    FIRSTROW = 1,
    FORMAT = 'CSV',
    FIELDQUOTE = '"',
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

For some reason, I'm getting the error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'FORMAT'.

Addition: FORMAT shows up in pink text in SSMS, FIELDQUOTE shows in black text, the others show in blue text. It seems the command isn't recognizing FORMAT and FIELDQUOTE as keywords for some reason.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jake
  • 341
  • 1
  • 3
  • 12
  • 2
    Are you sure you're connecting to a 2016 server that supports this new feature? – Damien_The_Unbeliever Feb 13 '17 at 15:16
  • Yes, I am sure. Here is the output of the version variable. `Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64->bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)` – Jake Feb 13 '17 at 15:42
  • 1
    Actually, it looks like I may have been mistaken - I don't think it's in 2016 either. I think it only works in what's currently being touted as "vNext". – Damien_The_Unbeliever Feb 13 '17 at 15:52
  • 1
    I was getting same error trying to use bulk insert in sql server 2012 – rinilnath Feb 21 '21 at 06:16

2 Answers2

22

The FORMAT and FIELDQUOTE specifiers are unfortunately not yet available in production servers.

According to the documentation:

Input file format options
FORMAT = 'CSV'
Applies to: SQL Server vNext CTP 1.1.
Specifies a comma separated values file compliant to the RFC 4180 standard.

FIELDQUOTE = 'field_quote'
Applies to: SQL Server vNext CTP 1.1.

NOTE: This answer is current as of 13th of february 2017. At some point "SQL Server vNext" will cease to be CTP and become production server. The question, however, is in relation to SQL Server 2016 which thus lacks these features.

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
  • 3
    As at 17 Oct 2018 the MS docs for `bulk insert` state that the `format` option "Applies to: SQL Server 2017 (14.x) CTP 1.1.". Ditto `fieldquote`. – youcantryreachingme Oct 16 '18 at 23:40
2

For those of you still running into this issue and wanting to simply read in a CSV file, I was able to work around the feature being missing on my SQL Server version by removing the FORMAT and FIELDQUOTE options:

BULK INSERT #MyTempTable
FROM 'C:\\temp\importfile.csv'
WITH (
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n', 
    FIRSTROW = 2
)

SQL Server version: 2016 (SP3-CU1-GDR)

Isaac
  • 96
  • 1
  • 5