SOLVED

Grouped text conversion to columns in Excel

Copper Contributor

I have report data in notepad .txt format that is grouped by employee. I need to convert to columns in an Excel spreadsheet. There are 12477 lines...is there a way to do it without having to perform text to columns, transpose, copy/paste, across the sheet for all 1000+ employees?

20 Replies

@CPSR13 

 

Possibly.

 

Much will depend on the level of consistency of how the data are organized.

 

Your description doesn't give enough details, however.  Is it possible to post a few representative sets of the textual data, after first changing any personal data so that no real person can be identified.

@mathetes Thank you for your reply! I have attached sample data showing how it pulled as txt. I need to separate into columns, i.e. A=Employee, B=REG, C=OT, etc. for all possible row labels

@CPSR13

 

My, oh, my, what a mess. I'm retired now, but had a long career in HR, and for some of that time was director of the HR and Payroll database for a major corporation. So I recognize these fields.

 

Just out of curiosity, is it possible to go back to the source of that text file--which pretty obviously was a database of some kind--and get it delivered as a CSV file (as it should have been in the first place)?

 

I'm sure it would be possible to write some kind of routine that could go through this text file and parse it into its many fields, but there's not a consistent set of columns (it's finite, but varies from person to person).......so it would be a stripping routine that would be beyond my abilities. 

@CPSR13 

You can do this with Power Query (under data tab, "From Text/CSV").

If you provide an example of the text document (as .txt), I can see if I can figure it out.

if you share your sample file on notepad format i can import in excel and tell you a possible measure but sample measure is required 

@CPSR13 

Uunfortunately, the program we are pulling the data from only allows for export to txt or pdf. Beyond my capabilities as well. @mathetes 

Attached!  I wasn't able to pull into individual columns, but am grateful for another set of eyes! @DhaniCole 

Attached! I wasn't able to pull into individual columns, but am grateful for another set of eyes! @Khizar_Hayat 

@CPSR13 

 

I'm not @Khizar_Hayyat but think he'll come back to you with a renewed request for an actual text file. What you sent is in a Word document, but actually just contains an image of your text file embedded in a word doc; it is neither editable nor amenable to parsing into separate characters. So at best there'd still be a need to use OCR to get it to text. Make it easier for @Khizar_Hayyat by uploading the actual text--and yes, turn the names into fictitious ones first.

 

For myself, back to the question of the source being available in CSV instead of text, it's hard to imagine a service provider not being willing to do that service. (Although I will acknowledge having been frustrated when Apple first released their charge card, in conjunction with Goldman, that they provided monthly statements only in PDF form. It took months before they rectified what should never have happened in the first place.)

It's an internal program and this forum doesn't allow for a txt upload

@CPSR13 

It's an internal program and this forum doesn't allow for a txt upload

 

But you can copy a portion of the text itself, which you do get, to Word, and then upload that Word docx containing editable text content.

@CPSR13 

IMHO, two main points to clarify

- logic of input file. Is it with fixed width columns or not. If so, how many characters in each column. If not, what the logic.  What are flexible -why employee ID/name sometimes in first position, sometimes not, what are the options.

 

- logic of output. Are employees ID and name in first two columns and all few dozens of other ID:s in other columns or not.  Shall they be separated on 4 groups (Pay, Deduct, etc) or not. If yes, when how.

@CPSR13 

The message says:

"The file type (.txt) is not supported. Valid file types are: jpg, gif, png, mp4, doc, docx, ppt, xls, csv, ics, pdf, mp3, oft, zip, pptx, xlsx, .cqdx, accdb, xlsm, xlsb."

Rename you file.txt to file.csv.

 

All data was in one column when converted or opened in Excel. All possible field identifiers needed to be in columns. Someone was able to use IF, sumifs formulas & pivot tables to convert the data for me. I appreciate your reply!
Someone was able to use IF, sumifs formulas & pivot tables to convert the data for me. I appreciate your reply!
oho my friend, it is a jpeg file you have to share a text file so i may be converted your date use advanced filter format but the requirement is text file
you can simply copy and paste the text in msg if you dont have a file
Hurry up!, i am waiting

@CPSR13 

Thanks, but I'm asking not about how it looks if to open in Excel - how data are separated in text file, is that fixed number of characters for each column or what.

best response confirmed by CPSR13 (Copper Contributor)
Solution
Someone was able to use IF, sumifs formulas & pivot tables to convert the data for me. I appreciate your reply!
it can be easily done by flash fill
1 best response

Accepted Solutions
best response confirmed by CPSR13 (Copper Contributor)
Solution
Someone was able to use IF, sumifs formulas & pivot tables to convert the data for me. I appreciate your reply!

View solution in original post