0

I am trying to remove SQL comments /**/ and -- from text file. Below code works and removes comments, if I store hard-coded SQL comment data in filedata variable. but doesn't work on file if I store it with read(). Also, tried regular expression bit doesn't work.

import re
import os
from os import path
import shutil
from pathlib import Path
import pyparsing
 
dst = "C:\\Users\\user1\\Desktop\\temp\\XYZ\\"
src = "C:\\Users\\user1\\Desktop\\temp\\ABC\\"

with open("C:\\Users\\user1\\Desktop\\SPs\\sample.txt", "r") as a_file:
    for line in a_file:
        #reading filename from text file
        stripped_line = line.strip()
        try:
            Sfile = open(src + stripped_line, "r")
            Ofile = open(dst + stripped_line, 'w', newline='')

            filedata = str(Sfile.read())
            #here, if I put hard-coded SQL commented string in filedata, it works and removes comments but it doesn't work if I read data from file as above and put in filedata variable.
            filedata1 = pyparsing.nestedExpr("/*", "*/").suppress()
            Ofile.write(filedata1.transformString(filedata))
            Ofile.close()
            Sfile.close()        
        except Exception:
             print('Error:'+stripped_line+' not found')

print('Successfully completed!')
PaulMcG
  • 62,419
  • 16
  • 94
  • 130
annonymous 007
  • 21
  • 2
  • Have you tried this solution https://stackoverflow.com/a/51614654/15826727? – Gábor Pálovics Sep 20 '21 at 18:28
  • 1
    You're doing this a line at a time, and SQL comments can span many lines. Why don't you just read the entire file (using `a_file.read()`, then eliminate all the comments and write it back out? You don't really care about lines for this operation. – Tim Roberts Sep 20 '21 at 18:30
  • 1
    I am reading entire file with read(). Please check the code. I have list of files which are stored in sample.txt. im traversing this file and reading each file and removing comments but that is not working. – annonymous 007 Sep 20 '21 at 18:38
  • I tested your code on a file and it seems to work for me although it only removes `/*...*/`-style comments. Can you please show an example what you meant by it's not working? – Gábor Pálovics Sep 20 '21 at 19:00
  • I am writing to a file 'dst + stripped_line'. When I open this file, comments are not removed. /**/ In this example. – annonymous 007 Sep 20 '21 at 19:04
  • I don't see your example file content. But it's an interesting issue, it works properly for me with pyparsing 2.4.6 – Gábor Pálovics Sep 20 '21 at 20:51
  • Same issue with sqlparse, doesn't work on file contents. It doesn't remove comments on file contents. – annonymous 007 Sep 21 '21 at 05:42
  • Do your source files contain _just_ SQL statements? Or are they code with SQL embedded in quotes? By default, `nestedExpr()` will skip over quoted strings. – PaulMcG Sep 22 '21 at 06:36
  • It contains SQL commands and comments – annonymous 007 Sep 25 '21 at 04:21

0 Answers0