SQL Server 2008 - Bulk Insert a whole text file into one field

Question

I have a text file (txt) containing formatted text (just line breaks, carriage returns and tabs) It also contains German language characters.

I want to use the Bulk Insert comment in T-SQL to read in the text file into one field within a database table.

I ran this command:

 CREATE TABLE #MyTestTable (
    MyData NVARCHAR(MAX)
 )

 BULK INSERT [#MyTestTable]
FROM 'D:\MyTextFile.txt'

 SELECT * FROM #MyTestTable

The problem is that it reads each line of the text file into a new row in the Temp table. I want it to read the whole file (formatting and all) into one row.

Also the German language characters appear to be lost - replaced by a non-printable character default in the Results View.

Anyone any ideas how I can achieve this?

Thanks.

Answer 1

• don't use bulk insert. it is made to take one record per line. You need to write code.
• Properly handle the transition from you text file to the unicode (nvarchar) in code. bulk insert probably appplied the standard codepage, loosing your characters.

This really cries for some minor programming job - an hour work or so, plus naother testing and as long for running as it takes.

Answer 2

You can use ROWTERMINATOR and CODEPAGE parameters. Default row terminator is '\r\n'. For the CODEPAGE, you need to know encoding of your raw file and default collation of your DB.

BULK INSERT [#MyTestTable]
FROM 'D:\MyTextFile.txt'
WITH (ROWTERMINATOR = '\0',
      CODEPAGE = 'ACP')

Also see http://msdn.microsoft.com/en-us/library/ms188365.aspx

Answer 3

Use this FIELDTERMINATOR = '|', ROWTERMINATOR = '\n'

Where | is your column delemiter