Skip to main content
K
kashmirap9699
April 10, 2024
Question

Read text file and convert the data into excel

  • April 10, 2024
  • 8 replies
  • 0 views

HI All, 

We need to read a formatted text file and then convert it to Excel sheet with headers and data. 

We need to create a single row for each supplier details in excel sheet along with the data per headers.

Has anyone got such a requirement? 

Can you please suggest a way?

Sample file is below

see attachment/cfs-file/__key/communityserver-discussions-components-files/11/ExpenseReport.txt

    8 replies

    harshitb6843
    harshitb6843
    April 10, 2024

    That text file looks very different from a usual text file. 

    What is the output you get from the function that you are using to read the file? 

    Also, for Excel, there is a plugin on AppMarket that can let you write yoru JSON to excel so if you are able to create a JSON - community.appian.com/.../export-json-to-excel

      K
      kashmirap9699Author
      April 10, 2024

      Yes the text file is formatted. and there is no single delimiter which can be used to parse it and create a json kind of format. 

      if we use the plugin to read the text file. it is reading as plain text and returning a single string. 

      as it is reading it line wise, we are unable to get the header and value mapping. Any thought now we can read it or any plugin that would help. 

      Please find the attached text which is returned after reading the text. 

      see attachment/cfs-file/__key/communityserver-discussions-components-files/11/ReadTextFromFile.txt

      harshitb6843
      harshitb6843
      April 10, 2024

      Though there are ways of doing it, like identifying the headers and splitting on it but that will always have several edge cases. 
      Where are you getting this file from? Can you change this file from its initial source? 

      stefanhelzle0001
      stefanhelzle0001
      Brainy
      April 10, 2024

      This file seems to be created for human consumption. Not to import it again.

      Did you consider to use an LLM to extract the individual data values?

      K
      kashmirap9699Author
      April 11, 2024

      ohk. Will we have to write a Java Plugin or use Open AI services for it? Is there anything available in appian for the same?

      stefanhelzle0001
      stefanhelzle0001
      Brainy
      April 11, 2024

      The latest Appian version has the built-in Prompt Builder AI skill. But this might need additional licensing. But you could use any LLM AI service. I am not sure whether this works, but LLMs are pretty good in extracting structured data out of such a mess. Keep in mind that, even if it works, is no 100% solution. That's a risk you will have to discuss in your team.

      Terms & ConditionsAccessibility statement