'txt to csv with python pandas
I have a txt file that I would like to read and export in csv format, but i have one problem.
Data in txt file looks like this |
Sales Organization|Distribution Channel|Sold-To #|Sold-To Name |Ship-To #|Ship-To Name |Mark-For #|Mark-For Name|Z1 : Sales Rep|Z1 : Sales Rep (Name)|Order Number|Sales Doc Type|Order Reason|PO Number|PO Type|Header Department|Delivery Block (H)|Billing Block (H)|Doc Date |RDD (H) |Cancel Date (H)|RDD (L) |Cancel date (L)|Division|Plant|Material |Sales Doc Item|Size |Schedule Line|Size Confirm Date|Item Category|Rej.Reason (SL)|Order Qty (SL)|Confirmed Qty (SL)|Unconfirmed Qty (SL)|Cancelled Qty (SL)|Open Qty (SL)|Reserved Qty (SL)|Fixed Qty (SL)|% Allocation (SL)|Delivered Qty (SL)|PGI Qty (SL)|Invoiced Qty (SL)|Net Unit Price|Confirmed Net Value (SL)|Dollars Shipped (SL)|Currency|% Shipped/Allocated (SL)|Delivery Block (SL)|Sales UOM|Credit Limit Status Text |EAN/UPC |Customer Material|
| EU01 |10 |10026276 | EU SARL|20056417 |Fulfillmemt Poland| | | | |1805338693 |ZOR |ZST |86LRD5JM |EDI | | | |14.02.2022|14.02.2022|03.03.2022 |14.02.2022|03.03.2022 |20 |3045 |35524-0004| 410|36 32| 1|14.02.2022 |ZTAN | | 1,000 | 1,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 1,000 | 0,000 | 0,000 | 41,600 | 41,600 | 0,000 |EUR | 100,000 | |EA |Credit check was executed, document OK|5400898540995| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | | | |1805338693 |ZOR |ZST |86LRD5JM |EDI | | | |14.02.2022|14.02.2022|03.03.2022 |14.02.2022|03.03.2022 |20 |3045 |35524-0004| 410|33 34| 2|14.02.2022 |ZTAN | | 1,000 | 1,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 1,000 | 0,000 | 0,000 | 41,600 | 41,600 | 0,000 |EUR | 100,000 | |EA |Credit check was executed, document OK|5400898540926| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | | | |1805338693 |ZOR |ZST |86LRD5JM |EDI | | | |14.02.2022|14.02.2022|03.03.2022 |14.02.2022|03.03.2022 |20 |3045 |35524-0004| 410|32 32| 3|14.02.2022 |ZTAN |P6 | 2,000 | 0,000 | 0,000 | 2,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 41,600 | 0,000 | 0,000 |EUR | 0,000 | |EA |Credit check was executed, document OK|5400898508124| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | | | |1805338693 |ZOR |ZST |86LRD5JM |EDI | | | |14.02.2022|14.02.2022|03.03.2022 |14.02.2022|03.03.2022 |20 |3045 |85862-0041| 530|29 - | 1|14.02.2022 |ZTAN |P6 | 1,000 | 0,000 | 0,000 | 1,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 21,100 | 0,000 | 0,000 |EUR | 0,000 | |EA |Credit check was executed, document OK|5400970111273| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | |72646 |John, Smith|1805339436 |ZOR |ZST |4QRNXHPH |EDI | | | |14.02.2022|14.02.2022|04.03.2022 |14.02.2022|04.03.2022 |10 |3045 |00501-3199| 10|36 34| 1|14.02.2022 |ZTAN |X9 | 17,000 | 0,000 | 0,000 | 17,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 47,800 | 0,000 | 0,000 |EUR | 0,000 | |EA |Credit check was executed, document OK|5400970332180| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | |72646 |John, Smith |1805339436 |ZOR |ZST |4QRNXHPH |EDI | | | |14.02.2022|14.02.2022|04.03.2022 |14.02.2022|04.03.2022 |10 |3045 |04511-4432| 20|40 32| 1|14.02.2022 |ZTAN |J2 | 2,000 | 0,000 | 0,000 | 2,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 41,300 | 0,000 | 0,000 |EUR | 0,000 | |EA |Credit check was executed, document OK|5400898076951| |
| EU01 |10 |10026276 |EU SARL|20056417 | Fulfillmemt Poland| | |72646 |John, Smith |1805339436 |ZOR |ZST |4QRNXHPH |EDI | | | |14.02.2022|14.02.2022|04.03.2022 |14.02.2022|04.03.2022 |10 |3045 |04511-5115| 30|36 32| 1|14.02.2022 |ZTAN |P6 | 5,000 | 0,000 | 0,000 | 5,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 47,800 | 0,000 | 0,000 |EUR | 0,000 | |EA |Credit check was executed, document OK|5400970262012| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | |72646 |John, Smith |1805339436 |ZOR |ZST |4QRNXHPH |EDI | | | |14.02.2022|14.02.2022|04.03.2022 |14.02.2022|04.03.2022 |10 |3045 |04511-5155| 40|28 30| 1|14.02.2022 |ZTAN |X9 | 1,000 | 0,000 | 0,000 | 1,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 0,000 | 56,500 | 0,000 | 0,000 |EUR | 0,000 | |EA |Credit check was executed, document OK|5400970254963| |
| EU01 |10 |10026276 | EU SARL|20056417 | Fulfillmemt Poland| | |72646
and I would like to show each columns separately in csv file. Now as you see columns are separated by | . As an example - Sales Organization should be a header and EU01 should be its value and so on.
df =pd.read_csv('1.txt', sep='delimiter', header= None, engine='python')
df =df.iloc[3:]
df.to_csv(path + '123.csv', index=False, header=True)
Solution 1:[1]
In your provided sample txt file. You have an | at the start and end of every row. So you need remove that before you read csv. Otherwise, it will give you ParseError. Once this is fixed, you can use sep='|' Like this:
df =pd.read_csv('1.txt', sep='|', header= None)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | gajendragarg |