Importing UNIX data file

  • Article

When bulk importing data, the data can come from any source including the files created in other operating systems like UNIX. Here are two ways that you can use to bulk import a data file created under UNIX.

· Without using a format file

You can use the following script to bulk load.

DECLARE @bulk_cmd varchar(1000)

SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail

FROM ''<drive>:<path><filename>''

WITH (ROWTERMINATOR = '''+CHAR(10)+''')'

EXEC (@bulk_cmd)

However, the following will not work because 'n' automatically gets converted to 'rn' since it's the typical EOF string for Windows text files. The reason that n works like it does, because we want BCP commands from Unix world on Windows as well

bulk insert bcptest

from 'C:unixu.dat'

WITH (ROWTERMINATOR='n')

· You can use explicit format file with the line terminator character as “n”