Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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: ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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,