Forum Discussion

mattmorgan1983's avatar
mattmorgan1983
Copper Contributor
Dec 27, 2022

How to import raw .txt data into Excel

I have files of raw testing data in .txt form that I need to import into Excel spreadsheets. 

 

The .txt files are formatted as follows: 

 

X47312804237ACDBBADCADBCDBDCCBDAACCDACBA

 

(The letter plus number on the left indicates student code, and the subsequent letter (ABC or D) indicates option selected on a test). 

 

Is there a way to convert this to Excel so that each answer (ABC or D) gets its own column? I'm struggling to find a solution. Thanks! 

excel
    • MY's avatar
      MY
      Brass Contributor
      Dec 28, 2022
      Great Lambda function! I'd suggest replacing the '12' in the drop function with a dynamic solution IF the student's ID is not 12 letters/digits long.
  • mattmorgan1983's avatar
    mattmorgan1983
    Copper Contributor
    Dec 27, 2022
    Thanks everyone for your help! I was able to get this sorted using the data tab, then insert data from CSV/TXT. I then did the split column feature to separate out user ID and individual responses. But next time I’ll try the methods you described, as I’m sure there’s a number of ways to do this.

    Thanks again!
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 28, 2022

      mattmorgan1983 

      Forgot to attach the Power Query option yesterday (apologies) - post edited

       

      Another 365 option:

      in C1:

       

       

      =LET(
  SplitStudAnsw, LAMBDA(string,array,
    LET(
      x,       MIN(SEARCH(array,string,2))-1,
      answers, MID(string,x+1,LEN(string)-x),
      HSTACK(LEFT(string,x), MID(answers,SEQUENCE(,LEN(answers)),1))
    )
  ),
  Answers, CHAR(SEQUENCE(4,,97)),
  SplitEach, REDUCE("",Strings,
    LAMBDA(seed,str, VSTACK(seed,SplitStudAnsw(str,Answers)))
  ),
  VSTACK(
    HSTACK("Student", "A" & SEQUENCE(,COLUMNS(SplitEach)-1)),
    IFNA(DROP(SplitEach,1),"")
  )
)
  • MY's avatar
    MY
    Brass Contributor
    Dec 28, 2022

    mattmorgan1983

    Assuming that all the numbers will always be in a sequence to the left and there are no stray numbers within the multiple choice area -

    I am also assuming that your .txt converts each student to a single cell of data (e.g. A1), then this should do the trick (though Quadruple_Pawn's response is much better if you have Lambda):

    =LET(DATA,A1,
    SPREAD,MID(DATA,SEQUENCE(LEN(DATA)),1),
    NTHPOS,IFERROR(MATCH(1E+100,INDEX(--SPREAD,)),0),
    TRANSPOSE(INDEX(SPREAD,SEQUENCE(LEN(DATA)-NTHPOS,1,NTHPOS+1,1))))
     

Resources