| Author |
 Topic  |
|
|
benking9987
Posting Yak Master
112 Posts |
 Posted - 04/26/2012 : 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
112 Posts |
Posted - 04/27/2012 : 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
India
48012 Posts |
Posted - 04/27/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Problem with Text Qualifiers
