Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to fix the embedded text qualifier issue while exporting data to CSV flat file?

People also ask

How do I change the text qualifier in CSV?

Click on Export icon > Export Data to CSV and the Save as CSV Options box opens. Set the Text qualifier as either double or single quotes. Set the Column delimiter as a comma. Click OK.

What is text qualifier in flat file?

Text qualifier is used in the event that delimiters are contained within the row cell. Typically, the text qualifier is a double quote. In the event that the cell contains a delimiter and a text qualifier is not used, then the data that occurs after the delimiter will spill into the next column.

How to add text qualifier in CSV in SSIS?

Text Qualifier in SSIS Example STEP 1: Drag and drop the data flow task from the toolbox to control flow region and rename it as Text Qualifier in SSIS. Double click on it, and it will open the data flow tab. STEP 2: Drag and drop Flat File Source and OLE DB Destination from the toolbox to the data flow region.

What is text qualifier in CSV?

A text qualifier is a character used to distinguish the point at which the contents of a text field should begin and end. Say you need to import a text file that is comma delimited (separated by commas), and one of fields is a description that could potentially contain a comma.


I wouldn't offer this answer except that you worked so hard to document it and it's been upvoted with no answer after a month. So, here goes. Your only choices appear to be to change the data or change the tool.

Probably, I am clearly doing something wrong and missing the obvious. Could someone please explain to me what I am doing wrong here?

When the tool is broken and the vendor doesn't care, it's mistake to keep trying. It's time to switch. You put a lot of effort into researching exactly how it's broken and demonstrating it violates not only the RFC but the tool's own prior version. How much more evidence do you need?

CSV is a boat anchor too. If you have the option, you're better off using an ordinary delimited file format. For lots of applications, tab-delimited is good. The best delimiter IMO is '\' because that character has no place in English text. (On the other hand it won't work for data containing Windows pathnames.)

CSV has two problems as an exchange format. First, it's not all that standard; different applications recognize different versions, whatever the RFC may say. Second (and related) is that it doesn't constitute a regular language in CS terms, which is why it can't be parsed as a regular expression. Compare with ^([^\t]*\t)*[\t]*$ for a tab-delimited line. The practical implication of the complexity of CSV's definition is (see above) the relative dearth of tools to handle them and their tendency to be incompatible, particularly during the wee hours.

If you give CSV and DTS the boot, you have good options, one of which is bcp.exe. It's very fast, and safe because Microsoft hasn't been tempted to update it for years. I don't know much about DTS, but in case you have to use it for automation, IIRC there is a way to invoke external utilities. Beware though, that bcp.exe does not return error status to the shell dependably.

If you're determined to use DTS and to stick with CSV, then really your best remaining option is to write a view that prepares the data appropriately for it. I would, if backed into that corner, create a schema called, say, "DTS2012CSV", so that I could write select * from DTS2012CSV.tablename, giving anyone who cares a fighting chance to understand it (because you'll document it, won't you, in comments in the view text?). If need be, others can copy its technique for other broken extracts.

HTH.


I know this is two years old, but I am also now having this issue, as we need to use SQL Server 2008 for a contract we have (don't ask). After reading through this question, I realized I needed to do the replace suggestion, but when I went to do it in the query, I ran into truncation issues, because using the replace() function in the query itself would convert the text to a varchar(8000) by default.

However, I discovered I could do the same thing using a Derived Column step in between the DB Source and Flat File objects. For example, I have a column named "short_description," that could have quotes in it, so I just used the following function as the expression, and selected "Replace short_description" in the Derived Column:

REPLACE(short_description,"\"","\"\"")

This seems to have solved the issue for me.