-1

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

enter image description here

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

enter image description here

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

Rod
  • 75
  • 1
  • 8
  • 1
    Please remember this is not a coding service website. While we are interested in helping and providing answers to questions, I suggest you understand https://stackoverflow.com/help/how-to-ask and https://stackoverflow.com/help/minimal-reproducible-example – Celius Stingher Feb 06 '20 at 14:42
  • 1
    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 Feb 06 '20 at 14:42
  • Thanks datanovice, I was using ExcelMacro. https://answers.microsoft.com/en-us/msoffice/forum/all/macro-to-export-data-from-excel-file-to-text-file/80e55844-6e5b-40a1-b3d9-fcc7095ec970 Not able to achieve in the Python I was following https://stackoverflow.com/questions/39314236/convert-xls-or-xlsx-file-to-pipe-separated-csv-file-using-command-line – Rod Feb 06 '20 at 14:44

2 Answers2

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.

SublimizeD
  • 134
  • 1
  • 10
  • 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 – Rod Feb 06 '20 at 14:53
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

heavelock
  • 364
  • 4
  • 9
  • 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 – Rod Feb 06 '20 at 14:50