Python Forum

I am having issues with the following export from sql table to csv. the output file is creating a extra line between each row, i need to resolve this. to include the headers and rows without extra line

import pyodbc
import csv
conn = pyodbc. connect("Driver ={sql native client};"
"server =myserver;
"Database = testdb;"
"trusted connection = yes")]
cursor = conn.cursor
sql1 = """select * from mytable"
cursor.execute(sql1)

with open('c:/test/myfile.csv', 'w')as output
    writer = csv.writer(output)
    writer.writerow(col[0] for col in cursor.description)
      for row in cursor:
          writer.writerow(row)
cursor.close()

csv.writer.writerow() adds a newline at the end of the row. If the cursor is also doing that, then that's where the extra line is coming from. Does the csv output contain any commas on the "blank" lines? Or is it just an extra newline?

For the second case, try opening the file like this:

with open("c:/test/myfile.csv", "w", newline="") as output:

Reference: https://docs.python.org/3/library/csv.html#csv.writer

thanks, however the problem i am now having is that i have a table with over 200 columns some are text coulmns max length up to 1200 char. when i run the above my output is overlapping, on some rows, how do i avoid this using python, I was having the same issue using sqlcmd to export to csv

Example? I don't know what overlapping rows, in a text file, means.

I have a table employee that has over 300 columns, some columns (HTML_COMMENT, REASON, DESCRIPTION) are text columns and the length could be over 1200 characters in length. what is happening is that when i run python or sqlcmd to extract the data from sql server table to csv file, because i am not specifying the length of the columns the exported values are not under the respective column headings.
example
SHOULD BE
ID,NAME,HTML_COMMENT(text field in sql server up to 1300 characters) ,....................(...... REPRESENTS ANOTHER 200 COLUMNS)
12,JOE,<YTF TESTETCWTCETC

ITS SHOWING UP AFTER EXPORT AS
ID,NAME,HTML_COMMENT(THE HTML_COMMENT value IS WRAPPING INTO next ROW INSTEAD OF STAYING UNDER THE HTML_COMMENT HEADER now its under ID)
<YTF TESTETCWTCETC