June 7, 2006 at 1:40 pm
hi guys , i need your help , I am importing data from a tab delimited file to my sql server database, but some of the fields containg " " since that tabdelimited file came from an excel file. now to delete those apostrophes from the fields do i have to do an update query or can i do it from the dts. does someone have an example of how the update query would look like?
June 7, 2006 at 6:19 pm
update MyTable
set MyField = REPLACE(MyField,'"','')
Yes, you could do it in the DTS package as well. There are multiple ways of accomplishing this.
June 7, 2006 at 8:29 pm
thank you so much!!!
June 8, 2006 at 1:46 am
Hi i have the same problem, but am importing a txt file to a access database this is done via a DTS there is no script like replace in access i could remove the 1st and last characters as they would be "" and but i could not be sure of this, you mentioned you can remove the "" in a DTS how can you do this ?
June 8, 2006 at 3:52 am
Assuming you use a Transfer Data Task you can specify a Text qualifier in the properties. Works the same way as the import of a text file into an Access table - it takes them out and defines the column as text.
June 8, 2006 at 9:09 am
Knut's solution will work of all the fields are qualified with quotes.
If they aren't, you have a couple of options (that come to my mind - there are probably many more):
1) Do the transformation / Data Pump task through a VB script and strip the quotes there.
2) Import into a temporary SQL Server table, update using the command I posted above and then import into your final destination.
June 8, 2006 at 9:28 am
I had it set up the way "Knut's" told me, but my file is causing me problems, so i have to use Text qualifier as <None> and then set them as Tabs it the only way i canget my data to transfer. i think i go with option 2 the temp table so i can clean up the "" marks out of the file. is their a way i can attach a file so i could let you have a look at it ?
June 8, 2006 at 9:40 am
Attachments aren't allowed. You could copy and past a few lines from the file if you want.
June 8, 2006 at 9:53 am
ok here you go i've copied the text in the format it is in, just to let you know i have to rename the file from .ttx to txt
Thanks
"REQUEST_ID" "SHIPMENT_ID" "PLACE_ID" "PLACE_ID_SHIP_TO" "PLACE_ID_TO_BILL" "NAME" "CITY" "ZIPPOST" "PHY_SVC_GRP_C" "REQ_CONTR_TYPE" "CONTRACT_ID_NOPRD" "PRODUCT_NAME" "SERIAL_ID" "CONTR_ID_PRODUCT" "PART_LINE_CODE" "PART_ID" "STD COST ($)" "QUANTITY" "extended_cost" "CREATED_DT" "WORK_DT"
"7244884" "3603082" "2114622" "2114622" "2114622" "SEB SAS" "ECULLY" "69132" "FR" "ECLIC4" "540478" "C2128V" "3116588080" "540478" "ECPS" "013R00588" 51.69 2.00 103.38 01-Jun-2006 01-Jun-2006
"7244884" "3603082" "2114622" "2114622" "2114622" "SEB SAS" "ECULLY" "69132" "FR" "ECLIC4" "540478" "C2128V" "3116588080" "540478" "ECPS" "006R01175" 34.46 3.00 103.37 01-Jun-2006 01-Jun-2006
"7245203" "" "1070098" "1070098" "1070098" "BANQUE FEDERALE DES BANQUES POPULAIRES" "PARIS" "75015" "FR" "EXTWARR" "" "3500" "WPK028231" "487919" "WBDU" "126N00243" 168.49 1.00 168.49 01-Jun-2006 01-Jun-2006
"7245208" "3603092" "1400946" "1400946" "1400946" "FRANCE TELECOM" "BETHUNE" "62400" "FR" "ECLIC8" "366558" "WC423V" "2241355627" "366558" "ECPS" "113R00666" 78.96 1.00 78.96 01-Jun-2006 01-Jun-2006
"7245209" "" "1070098" "1070098" "1070098" "BANQUE FEDERALE DES BANQUES POPULAIRES" "PARIS" "75015" "FR" "" "" "3500" "WPK028234" "" "WBDU" "126N00243" 168.49 1.00 168.49 01-Jun-2006 01-Jun-2006
"7245161" "3599145" "2052847" "2052847" "2052847" "OFFSETTRYCKAREN ACOPRINT" "LIDINGÖ" "181 35" "SWE" "ECLIC8" "509664" "C250" "3816460459" "509664" "ECPS" "013R00604" 14.60 2.00 29.19 01-Jun-2006 01-Jun-2006
"7245212" "" "1070098" "1070098" "1070098" "BANQUE FEDERALE DES BANQUES POPULAIRES" "PARIS" "75015" "FR" "" "" "3500" "WPK029728" "" "WBDU" "126N00243" 168.49 1.00 168.49 01-Jun-2006 01-Jun-2006
"7245213" "" "2099972" "2099972" "754491" "FRANCK SAUVAGE/GALERIE DES ARCHES" "GARGENVILLE" "78440" "FR" "PPACK8" "533209" "7750" "RTG410555" "533209" "CNDU" "115R00026" 121.70 1.00 121.70 01-Jun-2006 02-Jun-2006
"7245216" "3600289" "1376659" "1376659" "1376659" "CMAP ASBL" "MONTEGNEE" "4420" "BE" "PPACK8" "257454" "35V" "2232075239" "257454" "PPPS" "109R00634" 107.59 1.00 107.59 01-Jun-2006 01-Jun-2006
"7245216" "3600289" "1376659" "1376659" "1376659" "CMAP ASBL" "MONTEGNEE" "4420" "BE" "PPACK8" "257454" "35V" "2232075239" "257454" "PPPS" "006R01046" 29.44 1.00 29.44 01-Jun-2006 01-Jun-2006
"7245219" "" "1070098" "1070098" "1070098" "BANQUE FEDERALE DES BANQUES POPULAIRES" "PARIS" "75015" "FR" "EXTWARR" "" "3500" "WPK029735" "486742" "WBDU" "126N00243" 168.49 1.00 168.49 01-Jun-2006 01-Jun-2006
"7245226" "3600945" "2021582" "2021582" "1067082" "MTA SPA" "CODOGNO" "26845" "IT" "PPACK8" "490136" "5500" "REU772388" "490136" "PPPS" "113R00684" 45.77 1.00 45.77 01-Jun-2006 01-Jun-2006
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply