0

I have the below code in a batch file.
Here MYDB is the database, Activity_Inc.sql is the script file and ST_Activity- is the csv file generated with the data. | is used as the delimiter. Now, the problem is with few records where the data is also having the | in it. Due to this the generated file is failing to be integrated with the target system.
I need to enclose the field with the double quotes where ever the data is having | in it.
Please suggest a way for this.

sqlcmd -S . -d MYDB -E -s"|" -W -i D:\Activity_Inc.sql | findstr /V /C:"-" /B > D:\ST_Activity-%date:~6,4%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%.csv
Thom A
  • 88,727
  • 11
  • 45
  • 75
Meen
  • 119
  • 3
  • 15
  • I would recommend using something else other than `sqlcmd` to do this then, something that support quote identifiers, or choosing a delimiter that doesn't appear in your data. – Thom A Jun 06 '22 at 11:20
  • Assuming (for lack of info otherwise) the separator is provided by the `-s` parameter - is there a character that will not appear in your data? If not - can `-s` be s string?. Batch can be sensitive to certain characters like `!`,`%`,`&`,`=` and others. will any of these (or other symbols) appear in the data? – Magoo Jun 06 '22 at 11:56
  • Yes, the data can have any of these. That was the reason business opted for pipe ( | ) as delimiter but now the data is having that also. – Meen Jun 06 '22 at 12:05
  • What features does the target system import support? Can you simply escape the | with slash? that would be a bit easier. You can add that to your SELECt statement – Nick.Mc Jun 06 '22 at 12:12
  • 1
    Inside sql query, enclose "offending" strings in double quotes? Like `case when txt like '%|%' then '"' + txt + '"' else txt end as txt`. You need to duplicate double quotes either. – Arvo Jun 06 '22 at 12:25
  • 2
    As a side note, `%DATE%` and `%TIME%` are dynamic variables, and as such it is not wise to use them multiple times in sequence. It is much better to `Set "Dt=%DATE:~-10%" & Set "Tm=%TIME:~,5%"`. Then you could use `%Dt:~-4%%Dt:~3,2%%Dt:~,2%%Tm:~,2%%Tm:~-2%` instead of `%date:~6,4%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%`. – Compo Jun 06 '22 at 13:16
  • Can't you simply enclose all records in double-quotes? – aschipfl Jun 06 '22 at 13:16
  • Yes, I tried enclosing everything in double quotes, but there still is an issue with the data like **"L'escape Game" de la paie** How can we manage this data in the csv file while generating? Any suggestions. – Meen Jun 21 '22 at 12:39

0 Answers0