How to remove "" in flat file

Question

Post reply

How to remove "" in flat file

parthi-1705

SSCrazy Eights

Points: 9277
More actions
November 8, 2010 at 7:38 am

#240916

Hi
The following is the input to my table from text file i have loaded a sample record i am using Import/Export Wizard --Flat file--Browse, but what happening is i dont want ""in column name and in inside the columns how to do this can we use SSIS for this to remove ""
"CEid","CDid","ID","LastName","FirstName","MiddleName","Relationship","Status","Gender","Birthdate","Type","ODate"
00001,1,"00000001","LASTNAME","FIRSTNAME","MID","SPOUSE",NULL,"M","01011902",NULL,"11082010"
Thanks
Parthi
Thanks
Parthi

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

In the TextQualifier option box on the flat file conntection screen add " into it

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

steveb. (11/8/2010)
In the TextQualifier option box on the flat file conntection screen add " into it

Thanks. It Worked for me so what is the use of TextQualifier ?whether for this kind of operation it is used or what

Thanks

Parthi

Thanks
Parthi

Ray K SSC-Dedicated Points: 31148 More actions · Answer 3

parthi-1705 (11/8/2010)
Thanks. It Worked for me so what is the use of TextQualifier ?whether for this kind of operation it is used or what

What if you have a comma (which is commonly used as a delimiter) as part of your data? For example, what if your data is something like "Here, there, everywhere"? How does the system tell the difference between a comma that's part of your data and your delimiter?

That's where a text qualifier comes in handy.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Ray K (11/8/2010)
parthi-1705 (11/8/2010)
Thanks. It Worked for me so what is the use of TextQualifier ?whether for this kind of operation it is used or what
What if you have a comma (which is commonly used as a delimiter) as part of your data? For example, what if your data is something like "Here, there, everywhere"? How does the system tell the difference between a comma that's part of your data and your delimiter?
That's where a text qualifier comes in handy.

Until your data ends up having quotes in it, such as the description for a 30" Oven/Range

Use tab delimited, for the sake of everyone everywhere 🙂

Mike John SSCertifiable Points: 7186 More actions · Answer 5

Sorry Ray, but if properly quote qualified quotes in the data are fine. Your example would become

"30""Oven/Range" - ie the embedded quote in the data gets doubled up.

Technically it is only necessary to surround each text field in " if that field itself contains a " or other character that would confuse things (NL, CR etc) but often every field gets quoted. Best to avoid that if possible as if the file is large and contains lost of blank fields you can end up with "","","" etc which does nothing except bloat the file.

The reason I feel this is important os we often see people using tab, pipe, ; etc etc to try an d work round not doing quote qualification properly and it nearly always hits another problem, eg you chose tab - what of the data contans a tab (One day it probably will!).

Excellent article on it here if I may refer to it:

http://en.wikipedia.org/wiki/Comma-separated_values

Mike

Ray K SSC-Dedicated Points: 31148 More actions · Answer 6

Mike John (11/9/2010)
Sorry Ray, but if properly quote qualified quotes in the data are fine.

No apology necessary -- hey, I'm not perfect! 😉

Personally, one thing I like to do is, if I'm dealing with data that either (1) only I have to worry about, or (2) will only be used with apps that I write, I use delimiter characters that either are not commonly used (pipe, tilde, etc.), or I use CTRL characters that aren't usually found on a keyboard.

But hey, that's just me! 🙂

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

Hi

For birthdate i need to insert in datetime column used derived column

and tried

(DT_DATE)(SUBSTRING("BirthDate",1,2) + "/" +SUBSTRING("BirthDate",3,2) + "/"+ SUBSTRING("BirthDate",5,4))

or

(DT_DATE)(SUBSTRING(BirthDate,1,2) + "/" +SUBSTRING(BirthDate,3,2) + "/"+ SUBSTRING(BirthDate,5,4))

but i am getting error

how to resolve this

Thanks

Parthi

Thanks
Parthi