dataset to csv with python return extra blank row at the end

[Anonymous]

dear all,

Using python in power query to export my dataset to csv creates an extra blank row at the end of the exported dataset while this is not in my dataset in power query.

im using this code:

# 'dataset' holds the input data for this script
import pandas
import os
import matplotlib

file_path = "x"
dataset.to_csv(file_path, sep=',', encoding='utf-8-sig', index=False)

cant this be solved?

[v-lid-msft]

Hi @Anonymous ,

We can try to remove the last character in this csv file

import pandas
import os
import matplotlib

filepath = r"D:\test.csv"

dataset.to_csv(filepath,sep=',', encoding='utf-8-sig', index=False)

with open(filepath, 'r+') as f:
    f.seek(0,2)                    
    size=f.tell()               
    f.truncate(size-1)

All the Queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}}),
    #"Run Python script" = Python.Execute("import pandas#(lf)import os#(lf)import matplotlib#(lf)#(lf)filepath = r""D:\test.csv""#(lf)#(lf)dataset.to_csv(filepath,sep=',', encoding='utf-8-sig', index=False)#(lf)#(lf)with open(filepath, 'r+') as f:#(lf)    f.seek(0,2)                    #(lf)    size=f.tell()               #(lf)    f.truncate(size-1)          ",[dataset=#"Changed Type"])
in
    #"Run Python script"

Best regards,

[Anonymous]

Thank you, but somehow i still get the extra row when opening my csv in the text editor... Also when im copying your code, and only change the filepath..

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Could you please try to change the size -1 to size -2? We may need to delete \r\n in the end (it mean delete 2 char in size).

import pandas
import os
import matplotlib

filepath = r"D:\test.csv"

dataset.to_csv(filepath,sep=',', encoding='utf-8-sig', index=False)

with open(filepath, 'r+') as f:
    f.seek(0,2)                    
    size=f.tell()               
    f.truncate(size-2)

Best regards,