Forum Discussion

Maria Baker's avatar
Maria Baker
Copper Contributor
Jul 16, 2020
Solved

Need to convert a text file to Excel - not the typical comma delineated file

I have a text file that needs to be converted to Excel however the text is grouped in a fashion that Excel doesn't not convert into columns but exactly as it appears in the text file.  The text conte...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 21, 2020

    Maria Baker 

    IMHO, it's not necessary to change delimiters, in general it's not necessary to make any changes in your text file. For the delimiters you may use split only on left most delimiter, that won't affect the dates. 

     

    Variant of layout as TheAntony  suggested is in TextTransformTwo query attached.

TheAntony's avatar
TheAntony
Iron Contributor
Jul 16, 2020

Maria Baker , I used Power Query to accomplish this. I copied your sample into a text file (attached). I changed the delimiter from ":" to ";" since there were dates in the data that used ":". To use this change the location of the source text file in the source table. The processed data will be in the Output tab.

Maria Baker's avatar
Maria Baker
Copper Contributor
Jul 17, 2020

TheAntony Thank you.  I'm not familiar with power queries...so I'm stuck on this part, "change the location of the source text file in the source table."  Where do I change this?

Thank you

MB

  • TheAntony's avatar
    TheAntony
    Iron Contributor
    Jul 18, 2020

    Maria Baker 

    •  Download the 2 files I uploaded to your local drive
    • Open the Excel file - "Test Power Query.xlsx"
    • It will open up in the tab named Source
    • Change the File Path in the cell A2 from "C:\Users\dgant\Downloads\test.csv" to the file path of the test.csv file that you down loaded. 
    • Go to the tab named "Output" that shows you the output of PQ
    • Save the xlsx file
    • Open test.csv and add more records (making the appropriate changes I mentioned - using ; as the delimiter instead of :_
    • Save test.csv
    • Hit Data->Refresh All in the Excel file to see the output table updated with the info you put in the csv file

     

    Or you can use SergeiBaklan 's solution. Let us know how it goes.

     

     

     

    • Maria Baker's avatar
      Maria Baker
      Copper Contributor
      Jul 21, 2020

      TheAntony First thank you very much for the assistance.  My apologies for being so thick......how did you change the delimiter from : to ; in the text file? My original file has 38000 rows.

       

      Best Regards

      MB

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 21, 2020

        Maria Baker 

        IMHO, it's not necessary to change delimiters, in general it's not necessary to make any changes in your text file. For the delimiters you may use split only on left most delimiter, that won't affect the dates. 

         

        Variant of layout as TheAntony  suggested is in TextTransformTwo query attached.

Resources