title: converting text file to comma delimited csv file or json file
body: <p>I have a text file that I want to convert to a comma-delimited CSV file, where the first row(headers) are the MySQL table fields. My text file looks like this</p>
  <pre><code>      id                   purchase-date               last-updated-date        status,         name   
       305-0847312-2761164      2022-04-11T22:23:27+00:00   2022-04-11T22:23:31+00:00  Pending    DE  DE Core Free Shipping
       028-3270261-2897162      2022-04-11T22:17:27+00:00   2022-04-11T22:17:30+00:00  Pending    
       028-8245400-1649940      2022-04-11T22:15:29+00:00   2022-04-11T22:15:32+00:00  Pending    
       028-2661715-2120359      2022-04-11T21:57:24+00:00   2022-04-11T21:57:28+00:00  Pending    
       303-9076983-4225163      2022-04-11T21:53:52+00:00   2022-04-11T21:53:55+00:00  Pending    
       304-7440363-0208337      2022-04-11T21:49:14+00:00   2022-04-11T21:49:17+00:00  Pending    
       302-2070657-8345128      2022-04-11T21:30:12+00:00   2022-04-12T01:32:20+00:00  Shipped     some names
  </code></pre>
  <p>What I want to achieve is a file like this</p>
  <pre><code>   id,                   purchase-date,               last-updated-date,        status   
       305-0847312-2761164,      2022-04-11T22:23:27+00:00,   2022-04-11T22:23:31+00:00,  Pending    
       028-3270261-2897162,      2022-04-11T22:17:27+00:00,   2022-04-11T22:17:30+00:00,  Pending    
      
  </code></pre>
  <p>I need this file to save in the database where the first row are the column names</p>
  <p>I tried pandas</p>
  <pre><code>   read_file = pd.read_csv(&quot;reports/report.txt&quot;)
     read_file.to_csv(&quot;reports/report.csv&quot;, index=None, sep=&quot;\n&quot;)
  exit()
  </code></pre>
  <p>BUt I got error</p>
  <pre><code>    pandas.errors.ParserError: Error tokenizing data. C error: Expected 2 fields in line 194, saw 4
  </code></pre>
  <p>Questions</p>
  <ol>
  <li><p>How do you convert this text file to a comma delimited csv file?</p>
  </li>
  <li><p>Or a more preferable way, to convert this txt file to json file, saving this to db is much easier if its in json format, something like:</p>
  <pre><code>[
    {
       id: 305-0847312-2761164,
       purchase_date: 2022-04-11T22:23:27+00:00
    },
    {
       id: 305-0847312-2761165,
       purchase_date: 2022-05-11T22:23:27+00:00,
       name: null( some names is empty)
    },
     {
       id: 305-0847312-2761165,
       purchase_date: 2022-05-11T22:23:27+00:00,
       name: 'some name'
    },
    .......................
  ]
  
  with open(file) as f:
       for i in f:
           myTable.objects.create(id=id,**i)
  </code></pre>
  </li>
  </ol>
  <p>I tried to convert to json format and I think its pretty close</p>
  <pre><code>text = &quot;reports/download/sellingpartner/report.txt&quot;
  
  l = 1
  dict1 = []
  with open(text) as fh:
      first_line = fh.readline()
      fields = [e.replace(&quot;-&quot;, &quot;_&quot;) for e in first_line.split()]
      l = 1
      lines = fh.readlines()[1:]
      for line in lines:
          description = line
          print(description)
          i = 0
          dict2 = {}
          while i &lt; len(fields):
              dict2[fields[i]] = description[i]
              i = i + 1
          dict1.append(dict2)
          l = l + 1
  output = open(&quot;reports/download/sellingpartner/file.json&quot;, &quot;w&quot;)
  json.dump(dict1, output, indent=4)
  output.close()
  </code></pre>
  <p>result</p>
  <pre><code>[
  {
      &quot;amazon_order_id&quot;: &quot;3&quot;,
      &quot;merchant_order_id&quot;: &quot;0&quot;,
      &quot;purchase_date&quot;: &quot;4&quot;,
      &quot;last_updated_date&quot;: &quot;-&quot;,
      &quot;order_status&quot;: &quot;8&quot;,
      &quot;fulfillment_channel&quot;: &quot;5&quot;,
      &quot;sales_channel&quot;: &quot;9&quot;,
      &quot;order_channel&quot;: &quot;2&quot;,
      &quot;ship_service_level&quot;: &quot;7&quot;,
      &quot;product_name&quot;: &quot;7&quot;,
      &quot;sku&quot;: &quot;5&quot;,
      &quot;asin&quot;: &quot;-&quot;,
      &quot;item_status&quot;: &quot;1&quot;,
      &quot;quantity&quot;: &quot;5&quot;,
      &quot;currency&quot;: &quot;3&quot;,
      &quot;item_price&quot;: &quot;1&quot;,
      &quot;item_tax&quot;: &quot;5&quot;,
      &quot;shipping_price&quot;: &quot;3&quot;,
      &quot;shipping_tax&quot;: &quot;1&quot;,
      &quot;gift_wrap_price&quot;: &quot;\t&quot;,
      &quot;gift_wrap_tax&quot;: &quot;3&quot;,
      &quot;item_promotion_discount&quot;: &quot;0&quot;,
      &quot;ship_promotion_discount&quot;: &quot;4&quot;,
      &quot;ship_city&quot;: &quot;-&quot;,
      &quot;ship_state&quot;: &quot;8&quot;,
      &quot;ship_postal_code&quot;: &quot;5&quot;,
      &quot;ship_country&quot;: &quot;9&quot;,
      &quot;promotion_ids&quot;: &quot;2&quot;,
      &quot;is_business_order&quot;: &quot;7&quot;,
      &quot;purchase_order_number&quot;: &quot;7&quot;,
      &quot;price_designation&quot;: &quot;5&quot;,
      &quot;is_iba&quot;: &quot;-&quot;,
      &quot;order_invoice_type&quot;: &quot;1&quot;
  },
  {
      &quot;amazon_order_id&quot;: &quot;3&quot;,
      &quot;merchant_order_id&quot;: &quot;0&quot;,
      &quot;purchase_date&quot;: &quot;5&quot;,
      &quot;last_updated_date&quot;: &quot;-&quot;,
      &quot;order_status&quot;: &quot;5&quot;,
      &quot;fulfillment_channel&quot;: &quot;4&quot;,
      &quot;sales_channel&quot;: &quot;1&quot;,
      &quot;order_channel&quot;: &quot;2&quot;,
      &quot;ship_service_level&quot;: &quot;3&quot;,
      &quot;product_name&quot;: &quot;9&quot;,
      &quot;sku&quot;: &quot;1&quot;,
      &quot;asin&quot;: &quot;-&quot;,
      &quot;item_status&quot;: &quot;1&quot;,
      &quot;quantity&quot;: &quot;7&quot;,
      &quot;currency&quot;: &quot;2&quot;,
      &quot;item_price&quot;: &quot;2&quot;,
      &quot;item_tax&quot;: &quot;7&quot;,
      &quot;shipping_price&quot;: &quot;1&quot;,
      &quot;shipping_tax&quot;: &quot;2&quot;,
      &quot;gift_wrap_price&quot;: &quot;\t&quot;,
      &quot;gift_wrap_tax&quot;: &quot;3&quot;,
      &quot;item_promotion_discount&quot;: &quot;0&quot;,
      &quot;ship_promotion_discount&quot;: &quot;5&quot;,
      &quot;ship_city&quot;: &quot;-&quot;,
      &quot;ship_state&quot;: &quot;5&quot;,
      &quot;ship_postal_code&quot;: &quot;4&quot;,
      &quot;ship_country&quot;: &quot;1&quot;,
      &quot;promotion_ids&quot;: &quot;2&quot;,
      &quot;is_business_order&quot;: &quot;3&quot;,
      &quot;purchase_order_number&quot;: &quot;9&quot;,
      &quot;price_designation&quot;: &quot;1&quot;,
      &quot;is_iba&quot;: &quot;-&quot;,
      &quot;order_invoice_type&quot;: &quot;1&quot;
  },
  {
      &quot;amazon_order_id&quot;: &quot;3&quot;,
      &quot;merchant_order_id&quot;: &quot;0&quot;,
      &quot;purchase_date&quot;: &quot;5&quot;,
      &quot;last_updated_date&quot;: &quot;-&quot;,
      &quot;order_status&quot;: &quot;6&quot;,
      &quot;fulfillment_channel&quot;: &quot;5&quot;,
      &quot;sales_channel&quot;: &quot;4&quot;,
      &quot;order_channel&quot;: &quot;7&quot;,
      &quot;ship_service_level&quot;: &quot;1&quot;,
      &quot;product_name&quot;: &quot;4&quot;,
      &quot;sku&quot;: &quot;7&quot;,
      &quot;asin&quot;: &quot;-&quot;,
      &quot;item_status&quot;: &quot;8&quot;,
      &quot;quantity&quot;: &quot;7&quot;,
      &quot;currency&quot;: &quot;4&quot;,
      &quot;item_price&quot;: &quot;8&quot;,
      &quot;item_tax&quot;: &quot;3&quot;,
      &quot;shipping_price&quot;: &quot;6&quot;,
      &quot;shipping_tax&quot;: &quot;3&quot;,
      &quot;gift_wrap_price&quot;: &quot;\t&quot;,
      &quot;gift_wrap_tax&quot;: &quot;3&quot;,
      &quot;item_promotion_discount&quot;: &quot;0&quot;,
      &quot;ship_promotion_discount&quot;: &quot;5&quot;,
      &quot;ship_city&quot;: &quot;-&quot;,
      &quot;ship_state&quot;: &quot;6&quot;,
      &quot;ship_postal_code&quot;: &quot;5&quot;,
      &quot;ship_country&quot;: &quot;4&quot;,
      &quot;promotion_ids&quot;: &quot;7&quot;,
      &quot;is_business_order&quot;: &quot;1&quot;,
      &quot;purchase_order_number&quot;: &quot;4&quot;,
      &quot;price_designation&quot;: &quot;7&quot;,
      &quot;is_iba&quot;: &quot;-&quot;,
      &quot;order_invoice_type&quot;: &quot;8&quot;
  },
  {
      &quot;amazon_order_id&quot;: &quot;3&quot;,
      &quot;merchant_order_id&quot;: &quot;0&quot;,
      &quot;purchase_date&quot;: &quot;5&quot;,
      &quot;last_updated_date&quot;: &quot;-&quot;,
      &quot;order_status&quot;: &quot;1&quot;,
      &quot;fulfillment_channel&quot;: &quot;9&quot;,
      &quot;sales_channel&quot;: &quot;4&quot;,
      &quot;order_channel&quot;: &quot;5&quot;,
      &quot;ship_service_level&quot;: &quot;9&quot;,
      &quot;product_name&quot;: &quot;1&quot;,
      &quot;sku&quot;: &quot;5&quot;,
      &quot;asin&quot;: &quot;-&quot;,
      &quot;item_status&quot;: &quot;3&quot;,
      &quot;quantity&quot;: &quot;8&quot;,
      &quot;currency&quot;: &quot;1&quot;,
      &quot;item_price&quot;: &quot;5&quot;,
      &quot;item_tax&quot;: &quot;5&quot;,
      &quot;shipping_price&quot;: &quot;2&quot;,
      &quot;shipping_tax&quot;: &quot;4&quot;,
      &quot;gift_wrap_price&quot;: &quot;\t&quot;,
      &quot;gift_wrap_tax&quot;: &quot;3&quot;,
      &quot;item_promotion_discount&quot;: &quot;0&quot;,
      &quot;ship_promotion_discount&quot;: &quot;5&quot;,
      &quot;ship_city&quot;: &quot;-&quot;,
      &quot;ship_state&quot;: &quot;1&quot;,
      &quot;ship_postal_code&quot;: &quot;9&quot;,
      &quot;ship_country&quot;: &quot;4&quot;,
      &quot;promotion_ids&quot;: &quot;5&quot;,
      &quot;is_business_order&quot;: &quot;9&quot;,
      &quot;purchase_order_number&quot;: &quot;1&quot;,
      &quot;price_designation&quot;: &quot;5&quot;,
      &quot;is_iba&quot;: &quot;-&quot;,
      &quot;order_invoice_type&quot;: &quot;3&quot;
  },
       
  </code></pre>
  <p>One thing I need to solve is how to separate the <code>line</code> by commas, because each values contains spaces, and commas too.Values are separated by multiple spaces, see below</p>
  <pre><code> 406-4733989-6345153    406-4733989-6345153 2022-04-12T05:37:01+00:00   2022-04-12T05:37:05+00:00   Pending Amazon  Amazon.it       Expedited   Impact Fixy - Supporto per cellulare a 360Â° per bicicletta e moto, estremamente stabile, con giunto sferico a 360Â° e gomma di sicurezza, supporto pe  4Z-LVBE-IGD9    B08MXYS4S3  Unshipped   1   EUR 16.95       milano  mi  20161   IT  PAWS-V2-27105377117-91  false           false   
       
         
  </code></pre>
  <p>I converted the .tx extension to csv file and opened it with libre office, see screenshot<a href="https://i.stack.imgur.com/l0zqb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l0zqb.png" alt="enter image description here" /></a></p>
  <p>I tried another approach,</p>
  <pre><code>jsonArray = []
  output = &quot;reports/download/sellingpartner/file.json&quot;
  text = &quot;reports/download/sellingpartner/report.txt&quot;
  # read csv file
  with open(text, encoding=&quot;utf-8&quot;) as csvf:
      # load csv file data using csv library's dictionary reader
      csvReader = csv.DictReader(csvf)
  
      # convert each csv row into python dict
      for row in csvReader:
          # add this python dict to json array
          jsonArray.append(row)
  
  # convert python jsonArray to JSON String and write to file
  with open(output, &quot;w&quot;, encoding=&quot;utf-8&quot;) as jsonf:
      jsonString = json.dumps(jsonArray, indent=4)
      jsonf.write(jsonString)
  </code></pre>
  <p>Here's the result:</p>
  <pre><code> [
      {
         &quot;amazon-order-id\tmerchant-order-id\tpurchase-date\tlast-updated-date\torder-status\tfulfillment-channel\tsales-channel\torder-channel\tship-service-level\tproduct-name\tsku\tasin\titem-status\tquantity\tcurrency\titem-price\titem-tax\tshipping-price\tshipping-tax\tgift-wrap-price\tgift-wrap-tax\titem-promotion-discount\tship-promotion-discount\tship-city\tship-state\tship-postal-code\tship-country\tpromotion-ids\tis-business-order\tpurchase-order-number\tprice-designation\tis-iba\torder-invoice-type&quot;: &quot;304-3907519-7764337\t304-3907519-7764337\t2022-04-12T17:10:44+00:00\t2022-04-12T17:10:47+00:00\tPending\tAmazon\tAmazon.de\t\tExpedited\tImpact Ketten\u00c3\u00b6l Fahrrad 120ml - Einzigartiges 8 Komponenten Fahrradketten \u00c3\u0096l als Alternative zu Kettenfett &amp; Kettenspray f\u00c3\u00bcr E-Bike&quot;,
        &quot;null&quot;: [
            &quot; Mountainbike\tL9-3CSN-J1GL\tB08DVJ95DS\tUnshipped\t1\tEUR\t9.95\t\t\t\t\t\t\t\tLichtenfels\t\t96215\tDE\t\tfalse\t\t\tfalse\t&quot;
         ]
        },
        { 
           ........................
        }
    ]
  </code></pre>