I try to import csv data into mySQL (microsoft slq server 2014 on windows 7). I use the following code
USE [DataAnalytics]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
DROP TABLE [dbo].[npi_yy]
CREATE TABLE [dbo].[npi_yy](
[NPI] [NUMERIC] (10) NULL,
[Entity Type Code] [NUMERIC] (1) NULL,
[Replacement NPI] [NUMERIC] (10) NULL,
[Employer Identification Number (EIN)] [VARCHAR] (9) NULL,
[Provider Organization Name (Legal Business Name)] [VARCHAR] (70) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
LOAD DATA INFILE
'c:\data\npidata_20050523-20160612_sample2.csv'
INTO TABLE [dbo].[npi_yy]
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
And got an error:
Msg 102, Level 15, State 1, Line 354
Incorrect syntax near 'INFILE'.
I checked that CREATE TABLE
worked by looking at the empty table.
and checked c:\data\npidata_20050523-20160612_sample2.csv
exists by opening it by putting the filename in explore
Head of the csv:
"NPI","Entity Type Code","Replacement NPI","Employer Identification Number (EIN)","Provider Organization Name (Legal Business Name)"
"1588667638","1","","",""
"1497758544","2","","<UNAVAIL>","CUMBERLAND COUNTY HOSPITAL SYSTEM
"1306849450","1","","",""
"1215930367","1","","",""
"1023011178","2","","<UNAVAIL>","NAPA VALLEY HOSPICE & ADULT DAY SERVICES"
Anybody know what was wrong? Any help's appreciated.
Except of the LOAD DATA
statement, the code you posted is for Microsoft SQL Server. LOAD DATA
is a statement specific to MySQL. They are two unrelated database software, with different syntax rules and they extended the SQL standard in different ways. They cannot be mixed.
The LOAD DATA
statement is an extension of the standard provided by MySQL. Microsoft SQL Server provides the BULK INSERT
statement for this purpose.
Your code should be something like:
BULK INSERT [dbo].[npi_yy]
FROM 'c:\data\npidata_20050523-20160612_sample2.csv'
WITH (
FIELDTERMINATOR = ',',
FIRSTROW = 2,
ROWTERMINATOR = '\r\n'
)
GO
Read more about the BULK INSERT
statement.