What are my options for storing data that contains html tags?

Question

I'm having difficulty inserting string texts that contains HTML tags. I'm using VACHAR as data type. I'm doing bulk insert from a CSV file into a database table. I keep getting these errors.

Msg 4864, Level 16, State 1, Line 294

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (id).

Msg 4864, Level 16, State 1, Line 294

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (id).

Msg 4864, Level 16, State 1, Line 294

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (id).

Msg 4832, Level 16, State 1, Line 294

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 294

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 294

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

This is what I have.

Bulk Insert AllAbstract
From 'C:\Temp\MyAbstracts.csv'
With (
	Firstrow = 2,
	Datafiletype = 'char',
	Fieldterminator = '","',
	Rowterminator = '0x0a'
	)
Go

Answer 1

I would store HTML data in nvarchar(MAX). But if you think that there will only be characters in your code page, or you have a UTF-8 collation varchar(MAX) should also work.

In any case, the errors you are getting are not due to the data type as such, but because the file does not comply to the options you have set. You have set that each field is terminated by the sequence ",", which seems unlikely to me. Furthermore you have specified that the sole row terminator is linefeed, which is what we can expect if it is a Unix file, but not if it is a Windows file.

Anyway, HTML or not, this is a sample of a line complying with the format you have specified:

ABC","DEF","88989","XYZ

If you think there are quotes missing, you are right. But this is what you have specified.

As for what you should specify - as other have said, we need to see a sample file as well as the CREATE TABLE statement for the table. We also need to know the output of SELECT @@version.

Answer 2

Hi @CharlieLor

Maybe you can refer to these posts.

https://stackoverflow.com/questions/33259735/bulk-load-data-conversion-error-type-mismatch-or-invalid-character-for-the-spec

https://stackoverflow.com/questions/12902110/bulk-insert-partially-quoted-csv-file-in-sql-server

https://stackoverflow.com/questions/96448/sql-bulk-import-from-csv

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Seems like I can't upload .csv file in here so I have rename it to test3.txt. This is my test data.

Here's the error

Msg 4832, Level 16, State 1, Line 294

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7301, Level 16, State 2, Line 294

Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

Completion time: 2023-02-03T10:26:54.1549588-06:00

Bulk Insert AllAbstract
From 'E:\Temp\test3.csv'
With (
	Firstrow = 2,
	Datafiletype = 'char',
	Fieldterminator = '","',
	Rowterminator = '0x0a'
	)
Go

Answer 4

csv files are a poor format to import html. html commonly contains quotes, commas, tabs, line feed etc.

this makes it very hard to define the column and line separators for csv. you can preprocess the html, but then you have to post process after import. writing your own import program makes more sense.