0

I need to hit a database with basic select queries and write the result output in a file. using sqlcmd I execute the queries and write the results to the output csv file.

While trying to open this file as excel the records with comma in between are displayed as new column

How do I overcome this issue? Can we handle this in shell scripting?

Improve this question
5
  • Can you provide sample input? Are the commas inside fields double quoted? – jesse_b Jan 11 '20 at 18:14
  • No no records are or the comma inside fields are doubt quoted. Example when I select a personal details table the name columns contains name like: Vivaan, Sai After writing this record to a csv file, If i try to open this data in excel vivaan is in a column and sai in the next column – Venkat Jan 11 '20 at 18:18
  • Fields with separators in should be quoted - it's going to be very difficult to fix after the fact. See for example How to get export output in “real” CSV format in SQL Server Management Studio? (I'm guessing you mean MSSQL based on the sqlserver tag) – steeldriver Jan 11 '20 at 18:18
  • The database is hosted in an Azure sql managed instance – Venkat Jan 11 '20 at 18:22
  • You should research how to export data to CSV from SQL Server, it'll be too hard to try to fix it after the fact. – glenn jackman Jan 11 '20 at 18:24
1

You can at least quote every field from the database, whether it needs it or not -- it is still valid CSV. Don't know about Azure: in Oracle, you defined special chars with CHR(decimal_code) and concatenated separators and data with || so your field separator in the SQL looked like || CHR(34) CHR(44) CHR(34) || (which is why I used to build my queries in awk).

The downer is when data can contain double quotes. You need those doubled inside quoted fields, so :Said "Hello".: becomes :"Said ""Hello"".": and that is harder to do.

You can fix at most one field that can contain commas in awk. Suppose field 4 is the one that can have commas. You can cut off fields 1-3 and 5-NF, fully quote field 4, and reassemble the line as proper CSV. If that would help, comment me.

You can also have newlines inside CSV fields, which messes up columns per line too. This is also fixable in awk. Because newline is only valid in quoted fields, any line that has an odd number of quotes must be halfway through a data field, so you just read-ahead until you get an even number of quotes again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.