Convert csv file pipe delimited to xlsx compressed format using python script-CodePudding

I have a CSV file that is separated by pipe-delimiter.I need to convert this file to xlsx format using python script.

|||"012345.000000000000000"|"test.at.albt"|"ALB"|"ALBT"|"AL"|"Error occurred"|"ALB"|"ALBT"|||
|||"012346.000000000000000"|"test.at.albt"|"ALB"|"ALBT"|"AL"|"Error occurred"|"ALB"|"ALBT"|||

I have the source file added in winscp.I tried the below python script. It is working fine. When I open the final excel attachment, I could see the records as pipe separated.

source /opt/infa/MDW-env/bin/activate
import os
os.chdir("/opt/alb_test/alb/albt1/Source/alb/al/conversion/scripts")

# Reading the csv file

import pandas as pd
print(pd.__file__)
df_new = pd.read_csv('sourcefile.csv')

# saving xlsx file

GFG = pd.ExcelWriter('sourcefile.xlsx')
df_new.to_excel(GFG, index=False)
GFG.save()

I need to send the converted file as an attachment which I tried using mailx command

\#!/bin/bash
cd /opt/alb_test/alb/albt1/Source/alb/al/conversion/scripts
ls \*.xls -1 \> test.txt
while read line
do
mailx -s "Test" -a ${line} -s "files" [email protected]\<\< EOM

Hi, Testing Conversion
EOM
done\<test.txt

I want the records to be displayed in separate columns.

CodePudding user response：

You need to set sep="|" as a keyword when calling pandas.read_csv.

Try this :

df_new  = pd.read_csv("sourcefile.csv", sep="|", header=None).dropna(axis=1, how="all")
df_new.to_excel("sourcefile.xlsx", index=False)

Or chained :

(
    pd.read_csv("sourcefile.csv", sep="|", header=None)
        .dropna(axis=1, how="all")
        .to_excel("sourcefile.xlsx", index=False)
)

# Output :

        3             4    5     6   7               8    9     10
0  12345.0  test.at.albt  ALB  ALBT  AL  Error occurred  ALB  ALBT
1  12346.0  test.at.albt  ALB  ALBT  AL  Error occurred  ALB  ALBT