Problem with Text Qualifiers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Problem with Text Qualifiers

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2012-04-26 : 23:52:13

I'm trying to import a .CSV (commas are the delimiter) into a Table. Within certain columns of the source file, there are messages that have commas. As such, each text element is qualified with three quotation marks (""")

Problem is: Sometimes within a given field that is qualified there is a further set of text that is qualified with four quotation marks (""""). For example:

Field1,"""This is, some, example """"text"""" that might be, contained within the field""",next field

I wish I could say I have control of this field. Its a response file from listings on eBay, so there is NOTHING that can be done. Here is a snippet from the file:

138,Add,Failure,"""20169|717|73|21916519""","""Warning - Invalid store category ID, 0, since it is non-leaf.  So item has been listed to the 'Other' store category|0, since it is non-leaf.  So item has been listed to the 'Other' store category||Error - The package weight is not valid or is missing. Provide a valid number for the weight.||Error - Please enter a valid starting price for your item (eg. US $0.99).|US $0.99||Error - Listing is missing required item specific(s): """"US Shoe Size (Women's)"""". Please provide the required item specifics.|US Shoe Size (Women's)|53557|""",,,,,,,,,,,,,,,,,,,,,,,,,,,,CAPELLA-05,,,,,,,,,,,

Any help someone can provide?

BTW, the line delimiter is LF, NOT {CR}{LF}. I have the text qualifier set to three quotation marks ("""). I'm not getting anywhere.

Thanks

benking9987
Posting Yak Master

124 Posts

Posted - 2012-04-27 : 00:02:27

I forgot to put the header, in case this helps...

Line Number,Action,Status,ErrorCode,ErrorMessage,ItemID,ReferenceID,ApplicationData,StartTime,EndTime,AuctionLengthFee,BoldFee,BorderFee,BuyItNowFee,CategoryFeaturedFee,CurrencyID,FeaturedFee,FeaturedGalleryFee,FixedPriceDurationFee,GalleryFee,GiftIconFee,HighlightFee,InsertionFee,InternationalInsertionFee,ListingDesignerFee,ListingFee,PhotoDisplayFee,PhotoFee,ProPackBundleFee,ReserveFee,SchedulingFee,SubtitleFee,CustomLabel,PrivateNotes,BasicUpgradePackBundleFee,ValuePackBundleFee,ProPackPlusBundleFee,SellerInventoryID,CrossBorderTradeNorthAmericaFee,CrossBorderTradeGBFee,RefundFromSeller,TotalRefundToBuyer,CorrelationID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 16:14:14

you can handle this in ssis by putting a derived column task in the data flow and applying expression using REPLACE() function in SSIS to strip off the quotation marks to "". then replace the actual column with this modified column in the output which populates the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources