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:

# '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?

 

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,

     

3 Replies

  • 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,