Text Qualifier {"} Not Working

Text Qualifier {"} Not Working

• DennisCIS

  Actually I tried to upgrade dts package from 2000 to 2005 standard sql server. The text file contains delimiter (,) and text gualifier{"}. I don't know why the text qualifier does not work.

  Thanks, anyway.

  Jian

  
  
  

• gd2q

  I haven't used the Import/Export wizard in SQL 2005. However, I did have a look, and found some documentation on this value's purpose. Have a look here: http://msdn2.microsoft.com/en-us/library/ms178430.aspx
  
  According to this, it sounds like this field is used, it will surround the output data with the qualifier specified - i.e., it is not a delimiter (field value separator) for the input data. Is that what you intended
  
  
  
  

• Chook_rl

  Try running the following line first:

  SET QUOTED_IDENTIFIER OFF

  Have a look at the following for details:

  http://msdn2.microsoft.com/en-US/library/ms174393(SQL.90).aspx

  
  
  

• Matthew Mickelson

  In the Import and Export data wizard, there is a textbox for text gualifier. The default is none. Although it is set to be {"}, there is no function. The result is the same as none.

  Jian

  
  
  

• Jon Voigt

  When you say "it is not working" what do you mean Can you provide some sample data (input), and the results that you would expect. Actually from what you have mentioned above, it sounds like the delimiter is used to separate fields, and the text qualifier to group field values together such that the delimiter is ignored. For example:
  Input:
  FIELD1,FIELD2,FIELD3,FIELD4
  001,Mrs,"Jones, Alana",F
  
  Output:
  

  FIELD1	FIELD2	FIELD3	FIELD4
  001	Mrs	Jones, Alana	F

  
  Is the above behaviour what you'd expect
  
  
  

• Jie

  I just tried a simple example along the lines of the data you just gave me, with the following input:
  

  "19760101","","1fDx"
  "19760102","","1fdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
  Dx"
  "19760103","","1fDx"
  
  

  Note the carriage return on line 2-3.
  
  I ran the SSIS import wizard by selecting my database in Management Studio, then choosing import. I then followed these steps:
  

  1. In data source, I selected "Flat File Source"
  2. I entered the file name
  3. It automatically detected the file's locale and code page
  4. The format is set to "Delimited", the Text Qualifier to "<none>", the Header Row Delimiter to "{CR}{LF}", and Header Rows To skip to "0" (you'd set this to 1 if you had a header row).
  5. On the preview page, the sample data appears as you described - broken with regards to data enclosed in quotes, but split over several lines

  I also tried using Excel, but it had a similar outcome.
  
  Another (messy) approach, along the lines of using a regular expression, would be to open the file in MS Word, and remove the extra carriage returns first.
  In MS Word, first turn off the option to replace quotes with special quotes:
  

  • Tools -> Auto-Correct options. Select the "Auto-Format as you Type" tab. Ensure "Straight quotes" with “Smart Quotes” is UnChecked.
    

  Then, perform the following replaces in order, in Word:
  

  1. all end-line carriage returns with double-carriage returns (Find "^p" replace with a string not used elsewhere in the file, eg "[#CrLf#]").
  2. all broken end-line carriage returns with nothing (Find ^p, replace with an empty string)
  3. re-replace the previous string with carriage returns again (Find "[#CrLf#]" replace with "^p")
  4. then save your file as a text file, without formatting, and
     

  ... then Open it in Excel:
  

  1. Open the file in Excel. It should automatically come up with the Text Import Wizard
  2. Choose "Delimited" on the first page
  3. On the Step 2 of 3 page, uncheck "Tab" as a delimiter, then choose "Comma" instead. Ensure the Text qualifier is set to the quote (")
  4. On the Step 3 of 3 page, in the bottom pane, select all fields, and choose "Column Data Format" as "Text" above
  5. After clicking finish, save the file as an XLS file

  ... then re-attempt your SSIS import using the saved XLS file as your imput
  
  It's very messy, but failing other options, it should work.
  
  Note that
  
  
  

• Scott Fletcher

  Hi,

  Here are input data structure:

  "19760101","","1fDx"

  Output is expect as follows:

  1st field 2nd field 3rd field

  19760101 1fDx

  Actually the output is shown as follows:

  "19760101" "" "1fDx"

  As you mentioned, the text qualfier is not recognised. When one of the context in the field is longer than one line, it could not read all. For example, the 3rd filed is "1fDxx

  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" for the input, and then thr output is "1fDxx. The rest of context was missing. That's the problem I have. The input structure in sql 2K is OK, but not working in sql 2005.

  Thanks for your interest.

  
  
  

• scompa

  As a work-around, perhaps you could try one/more of the following suggestions:
  

  1. If you just have just one file/data table to import, then import the data into a temporary table using SQL 2000 DTS, then either just copy the data from the SQL 2000 DB to the SQL 2005 DB using TSQL (and linked servers), or export the data, and re-import it
  2. The format seems to be simple CSV (Comma Separated Values) - Try to open the imput data file in Microsoft Excel, then export the data as either a new CSV file, or an XLS file, then use the output file as your SQL 2005 SSIS input data source
  3. Failing the above two suggestions, try to use a Regular Expression to remove the line-breaks in the file (based on the number of quotes found), and once this is done, re-open the file in Microsoft Excel along the lines of suggestion (2).

  I find it strange that it doesn't just work as you expect, as your case seems to be a pretty stock-standard scenario...
  
  
  
• Text Qualifier {"} Not Working