SQL 2019 Bulk Insert with FIELDQUOTE option for double quote .txt file?

Question

Hello, have SQL Server 2019 and am trying to BULK INSERT from a .txt file which every column data is wrapped with the double quotes character " and delimited by vertical bar pipe |

This flat file has no column header, contains 1000 records, and 90 Columns.

Microsoft mentions:

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017

FIELDQUOTE = 'field_quote'

Applies to: SQL Server 2017 (14.x).

Here's what I have so far:

BULK INSERT [dbo].[Products]
FROM 'C:\Test_Bulk_Import\Products.txt'
WITH
(
DATAFILETYPE = 'widechar',
FIELDTERMINATOR ='"|"',
FIELDQUOTE = '"',
ROWTERMINATOR ='"\n',
FirstRow=0
);

I get following:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000, column 90 (Last_Column_Name).

Strange when I query the table 999 records within .txt file got imported into my target Table (looks like missing 1 record since flat file contains 1000 records) and the very 1st record in 1st Column includes the double quote " character along with its data within that cell. All of the rest of the records/cells within Table look good.

I'm trying to Bulk Insert this without using a Format File or SSIS.

Can this be done since I do have SQL 2019 like Microsoft mentions FIELDQUOTE option should be able to do?

Please note my flat file is .txt file not .csv

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

Earlier I said to make changes to the source file, of course you can also modify the result of the bulk insert.
After each bulk insert you can execute an update statement such as the following:

update [Products] set [ProductName]=substring([ProductName],2,len([ProductName])-1)  

If you want to automate this process, you can create a job at some point every night that contains bulk inserts and this update statement.

Best regards,
Seeya

Answer 2

Slight correction in my above outcome...

All 999 of the records in just the 1st Column of imported Table do contain the initial double quote " character (but not the ending) and all of the other Columns/records data cells look good.

Here's an example of what got imported:

[ProductName] [Color] ...etc

---

---

"Bicycle Black
"Chair Green
"Desk Red
...etc

Answer 3

Thanks SeeyaXi-msft for your reply in trying.

As an example to simplify and security purpose the target SQL Server Table contains 4 (instead of 90) Column headers which are [ProductName] [Color] [Price] [AcuiredDate].

The corresponding source flat file Products.txt that I'm trying to import has no Column headers:

"Bicycle"|"Black"|"111.11"|"01-01-2001"
"Chair"|"Green"|"222.22"|"02-02-2002"
"Desk"|"Red"|"333.33"|"03-03-2003"
"Vacuum"|"Yellow"|"444.44"|"04-04-2004"

After the Bulk Insert like my coding listed earlier above the target SQL Table contains 3 records (missing the last 4th record Vacuum):

"Bicycle Black 111.11 01-01-2001
"Chair Green 222.22 02-02-2002
"Desk Red 333.33 03-03-2003

Regards.

Answer 4

Hi @techresearch7777777 ,

I did a test in my database. I have the same situation as you (missing the last line and each line has leading quotation marks).
From the official document, you will know ROWTERMINATOR = 'row_terminator'. Your statements contain ROWTERMINATOR ='"\n'. This is reason that the last row in your txt file is missing a newline, so the last row encounters an error. This is why when you select * from this table you will see the last row missing.

FIELDQUOTE specifies a character that will be used as the quote character in the CSV file. Please refer to my screenshoots:
See my cursor position:

Then you will get the result.

Regarding the handling of double quotes, if you don't want them, you can just remove the quotes from the beginning of each line of your source file.
You will get the result:

Best regards,
Seeya

---

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.

Answer 5

Thanks Seeya for your response, appreciate it.

I tried mimicking your statements (in my case I do need DATAFILETYPE = 'widechar', or elase I get related error) and still get the same results insert completes but missing last row and first columns data cells have initial double quotes.

My flat .txt file has 1000 records so manually removing them is not really an option since it gets sent to us in this format.

Any other ideas?

Regards.