Flat File Source Column Parsing Error

Hello All,

I have come across this issue with the Flat File Source when the delimiter is set to a comma.

"""KAILUA KONA,HI""","CA",

In the data snippet above and with the setting of using a comma as a column delimiter

and a " as the text qualifer.

the data will be parsed in this fashion:

"""KAILUA as a column:

HI""" as a column

CA as column

when it should be

"KAILUA,HI" as a column

CA as column.

Is there a way to let the Flat File Source to let it know not to parse the data in multiple quotes ?

Thank you

Eric Flores

The flat file parser does not know how to handle embedded qualifiers.

In this case, you can workaround this by loading the qualified data and then stripping the qualifiers in Derived Column or Script transform.

HTH.

|||

I will try that out and see if it will work. Though in my data snippet, the comma in question is not even a qualifier but is part of the data.

What I do not get is that in DTS for SQL Server 2000 when specifying a flat file as having comma as the column delimited and a double quote as the text qualifier that when given the data snippet in my previous post that it would parse the data into the correct two columns.

|||

Eric,

feel free to go to the product feedback site below and report your issue. That will create a bug in our system.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||This has been "resolved" in product feedback. Was there a resolution or is this just the way it is going to be?

Flat File Source Column Parsing Error

Hello All,

I have come across this issue with the Flat File Source when the delimiter is set to a comma.

"""KAILUA KONA,HI""","CA",

In the data snippet above and with the setting of using a comma as a column delimiter

and a " as the text qualifer.

the data will be parsed in this fashion:

"""KAILUA as a column:

HI""" as a column

CA as column

when it should be

"KAILUA,HI" as a column

CA as column.

Is there a way to let the Flat File Source to let it know not to parse the data in multiple quotes ?

Thank you

Eric Flores

The flat file parser does not know how to handle embedded qualifiers.

In this case, you can workaround this by loading the qualified data and then stripping the qualifiers in Derived Column or Script transform.

HTH.

|||

I will try that out and see if it will work. Though in my data snippet, the comma in question is not even a qualifier but is part of the data.

What I do not get is that in DTS for SQL Server 2000 when specifying a flat file as having comma as the column delimited and a double quote as the text qualifier that when given the data snippet in my previous post that it would parse the data into the correct two columns.

|||

Eric,

feel free to go to the product feedback site below and report your issue. That will create a bug in our system.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||This has been "resolved" in product feedback. Was there a resolution or is this just the way it is going to be?sql

flat file source and destination - need fixed width output

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.