SQL Load Data InFile

[jstevens]

I'm having a challenge loading data to a table using the following code. The error being returned is "Incorrect syntax near 'INFILE'.

Code: Select all

LOAD DATA INFILE 'C:\MyPath\Accounts.csv'
INTO TABLE Accounts
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

I'm able to load the data using the import from a flat file. Right-Click on Database Name>Tasks>Import Flat File

Any suggestions are appreciated.

[HansV]

Which platform are you using? LOAD DATA INFILE is MySQL as far as I know.

[jstevens]

Platform: MS SQL Server Management Studio

[jstevens]

Hans,

You're correct my fault for not noticing.

Code: Select all

BULK INSERT Accounts
FROM 'C:\MyPath\Accounts.csv'
WITH (
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n',
	FIRSTROW = 2, --skip header row
	TABLOCK
	)

[HansV]

Yes, that should work.

[jstevens]

Hans,

One additional question regarding Bulk Insert as I'm encountering a load error: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Id).

I modified the table to include an auto-incrementing field ... [Id] [int] IDENTITY(1,1) NOT NULL,

In doing so, I get the error relating to the "Id" field. If I remove the "Id' field I can load without any challenges.

Your thoughts are appreciated.

[jstevens]

Hans,

I found a solution here.

Add the "Id" column to the load file but leave the records blank.

[HansV]

Thanks for the update.