Using SQL BCP utility for Microsoft office documents

Question

Hi team,

I'm trying to import documents into SQL Blob column first and then to export them to a different location on my machine using SQL BCP utility. This works absolutely fine for txt, pdf and jpg files. But when I do the same for word / pptx documents, the files are exported but when they are opened, an error message pops up as below but the document opens up fine.

"Word found unreadable content in Test.docx. Do you want to recover the contents of this document? If you trust the source of this document, click Yes."

Below is the script that I have tried.

Is there a way to avoid this error? Any help is highly appreciated.

(
    ID INT IDENTITY(1, 1),
    PictureName VARCHAR(50) NOT NULL,
    PictureData VARBINARY(MAX) NOT NULL
);
INSERT INTO PicturesTest(PictureName,PictureData)
SELECT N'MyTestDoc.docx', BulkColumn
FROM OPENROWSET(BULK  N'C:\MyTestDoc.docx', SINGLE_BLOB) b
CREATE TABLE dbo.FilesTest
(
    ID INT IDENTITY(1, 1),
    FileName VARCHAR(50) NOT NULL,
    FileData VARBINARY(MAX) NOT NULL
);

INSERT INTO FilesTest(FileName,FileData)
SELECT N'MyTestDoc.docx', BulkColumn
FROM OPENROWSET(BULK  N'C:\sqlbcp\MyTestDoc.docx', SINGLE_BLOB) blob

DECLARE @sql VARCHAR(1000);
DECLARE @DBServer NVARCHAR(100) = 'localhost'
SET @sql
    = 'BCP "SELECT FileData FROM Testing.dbo.FilesTest " queryout "C:\temp\Test.docx" -T -n -S'+' localhost'
 
EXEC master.dbo.xp_cmdshell @sql
lob

Answer 1

Try defining a format file, such as this "C:\Temp\MyFormat.fmt":

14.0
1
1       SQLBINARY           0       0       ""   1     FileData             ""

Then add the -f C:\Temp\MyFormat.fmt option to your last BCP command.

Answer 2

@Kalpana Patchigolla

Thank you for posting your question here!!

I will suggest you use an open-source file extension for Microsoft Word such as .doc and .xls for Microsoft Excel.

Also, you can open your .csv using Microsoft Excel.

Hope this helps!

Kindly let us know if the above helps or you need further assistance on this issue.

Best Regards,

Sina

Answer 3

Hi @Kalpana Patchigolla

It looks like a data format change occurred after import/export.

See Data formats for bulk import or bulk export (SQL Server).

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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.