b1b12153630874f63ec80e6f0d000504921a4719
braney
  Tue Apr 28 18:13:17 2026 -0700
clinVarToBed: strip trailing backslash before tab in submission_summary so MySQL LOAD DATA doesn't eat the field separator, refs #37477

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

diff --git src/hg/utils/otto/clinvar/clinVarToBed src/hg/utils/otto/clinvar/clinVarToBed
index b113e75386f..bf327004cff 100755
--- src/hg/utils/otto/clinvar/clinVarToBed
+++ src/hg/utils/otto/clinvar/clinVarToBed
@@ -577,31 +577,33 @@
     htmlStr = "".join(htmlList)
     htmlStr += "&nbsp;&nbsp;<small>based on: %s</small>" % reviewStatus
 
     if starCount==1:
         asciiDesc = "%d star" % starCount
     else:
         asciiDesc = "%d stars" % starCount
     return htmlStr, asciiDesc, starCount
 
 def loadSubmissions(summFname):
     " load the submissions into the Mysql table for the lollipop track details page "
     # not doing the load+rename strategy yet, I don't think it makes a difference here
     logging.info("Loading submissions table %s into mysql, hg19 and also hg38" % summFname)
     fh = tempfile.NamedTemporaryFile(prefix="clinVarToBed", suffix=".tsv")
     tmpFname = fh.name
-    cmd= "zgrep -v '^#' %s > %s" % (summFname, tmpFname)
+    # strip trailing backslashes inside fields so MySQL's LOAD DATA escape
+    # interpretation doesn't eat the tab separator (Warning 1261, refs #37477)
+    cmd= r"zgrep -v '^#' %s | perl -pe 's/\\(?=\t|$)//g' > %s" % (summFname, tmpFname)
     assert(os.system(cmd)==0)
 
     cmd = "chmod a+r %s" % (tmpFname) # otherwise mysqld can't read the file
     assert(os.system(cmd)==0)
 
     cmd = "hgLoadSqlTab hg19 clinvarSub clinvarSub.sql %s" % (tmpFname)
     print(cmd)
     assert(os.system(cmd)==0)
 
     cmd = "hgLoadSqlTab hg38 clinvarSub clinvarSub.sql %s" % (tmpFname)
     assert(os.system(cmd)==0)
 
 def mostCurrentVersionName():
     " return name of version on FTP site "
     cmd = 'curl -s https://ftp.ncbi.nlm.nih.gov/pub/clinvar/xml/ClinVarVCVRelease_00-latest.xml.gz | zcat | head -n2 | tail -n1'