cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VictorV Regular Visitor
Regular Visitor

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

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @VictorV ,

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

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

Hi @VictorV ,

 

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)

 

2.PNG

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
VictorV Regular Visitor
Regular Visitor

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

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..

Community Support Team
Community Support Team

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

Hi @VictorV ,

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)