Best automated way to import large data from csv

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best automated way to import large data from csv

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2014-04-04 : 09:44:04

hi guys,
we are running a 64bit windows version and cant seem to get an automated data import to work, being 64bit compatibility.

I would like to know whats the best method to automate importing large amounts of data from a csv file. I am trying a bulk insert, but giving errors.

Here is my code, what other options are available as best practices, as the csv files can have up to a million records

USE titan;
GO
BULK INSERT  dbo.ImportTest FROM 'd:\MAM-NAP.csv'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

I get this error

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (adesuwa).
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 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 4 (adesuwa).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 4 (adesuwa).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

_____________________

Yes O !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources