pandas/python script for converting to pipe ftext file

Question

Consider the below data in Excel (.xlsx) format as Input file.Assume the location is C:\Conversion\input.xlsx

The output file should be in the below pipe limited format as text file. the output should be in the below locations C:\Conversion\output\pip.txt

Please help by providing a Python/pandas script for the above output. Note: I am a new learner.

The output txt file should not contain any junk and bad character.
if the value is blank any cell (input file), it need to store as null value.

Thanks in advance

Please remember this is not a coding service website. While we are interested in helping and providing answers to questions, I suggest you understand stackoverflow.com/help/how-to-ask and stackoverflow.com/help/minimal-reproducible-example — Celius Stingher
– Celius Stingher, Commented Feb 6, 2020 at 14:42
What issues have you run into so far? can you show your code and please replace your images with text so that others can provide a solution for you. — Umar.H
– Umar.H, Commented Feb 6, 2020 at 14:42
Thanks datanovice, I was using ExcelMacro. answers.microsoft.com/en-us/msoffice/forum/all/… Not able to achieve in the Python I was following stackoverflow.com/questions/39314236/… — Rod
– Rod, Commented Feb 6, 2020 at 14:44

heavelock · Accepted Answer · 2020-02-06 15:24:34Z

0

I'm assuming you don't want index in your output but you want header.

df = pd.read_excel('test.xlsx')
df.to_csv('test.txt', sep='|', index=False)

It will produce a file like that:

col1|col2|col3|col4|col5
a|1|2.0|s|g
s|2|3.0|f|d
d|3||d|s
f|45|4.0|s|

If the header should not be present in the file, you have to adjust that call according to what does the documentation says: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html

edited Feb 6, 2020 at 15:24

answered Feb 6, 2020 at 14:47

heavelock

3844 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rod Over a year ago

Thanks , My input file as per my question is Excel file and i need the output as Pipe limited as Text file output which i mentioned in the question. I have edited my questions

SublimizeD · Accepted Answer · 2020-02-06 14:43:44Z

0

I may be wrong, but this is found in your computer settings. By default your computer is comma delimited. Go to the control panel and find additional settings. There you can change the list separator from commas to pipes.

answered Feb 6, 2020 at 14:43

SublimizeD

1341 gold badge1 silver badge10 bronze badges

1 Comment

Rod Over a year ago

Thanks sublimizeD, I have tried doing it. I am getting more issue as bad character during the load to database.moreover i have bulk operations which i am trying to achieve

Collectives™ on Stack Overflow

pandas/python script for converting to pipe ftext file

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related