Python Pandas Dataframe.to_csv behaving differently than Saving as CSV within Excel

Question

A strange one really...Trying to load a csv onto an IBMi, just a standard csv file, when I use python pandas .to_csv, the file won't load, but if I import the same file into excel (using import), then save as .csv (used the UTF-8, csv and MSDOS csv - all work); the file loads; which suggests the df.to_csv is different to the standard excel .csv file.

Tried different encoding, but all the same problem, which suggests something else.

python code

if output_dir == '': output_dir = 'migration_output'
if not os.path.exists(output_dir) or not os.path.isdir(output_dir):
    print('Output directory /' + output_dir + ' created')
    os.makedirs(output_dir) 
filename_timestamp = '_' + str(datetime.now().strftime("%Y%m%d%H%M%S")) + '.csv'
IND_csv_df = pd.DataFrame(ind_output)
IND_csv_df.to_csv(os.path.join(output_dir + 'IND_' + str(filename_timestamp)),index=False, header=False)

csv file looks OK, loads OK in excel and notepad++

any ideas???

question from:https://stackoverflow.com/questions/65844134/python-pandas-dataframe-to-csv-behaving-differently-than-saving-as-csv-within-ex

Answer 1

OK...sorted...its the line terminator, mac defaults to CR, windows to CRLF, and excel uses CRLF...so updated .to_csv to write CRLF and hey presto...worked.

line_terminator=' '