Forum Discussion
Need to convert a text file to Excel - not the typical comma delineated file
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.
SergeiBaklan and @TheAntony - thank you for the assistance on my first request for help. I was able to get the data per Sergei Baklan's instructions & files.
I have another file that needs to be extracted from one column to multiple columns - for my yammer users which I've attached here.
I tried dissecting your files to understand the queries but that was a fail. I would be grateful for your assistance again.
Thank you
That's bit another logic of this file. What to do
- since now you have commas within texts, ignore them as default delimiter. That could be done as
= Csv.Document(File.Contents(filePathName),{"Column1"},{0})which says we split on one Column1 starting from position 0. Other words, no split.
- transforming column we select as names not only field starts by _, but also equal to name
Table.AddColumn(Source, "Names", each if Text.StartsWith([Column1.1],"_") or [Column1.1] = "name" then [Column1.2] else null)and filter them as other fields as
= Table.SelectRows(#"Filled Down", each not ( Text.StartsWith([Column1.1], "_") or [Column1.1]="name"))
- since field role could be repeated for same person, we created in the middle intermediate table with this field only
group it by names and field (Column1.1), extract list with roles and convert it to text:
after filter field role from main table and append to it above one
- datetime text here is without separation of date and time in the text. Thus we remove comma on the end and insert T between date and time
= Table.ReplaceValue(#"Extracted Text Before Delimiter",each [last_date_accessed], each Text.Insert([last_date_accessed],10,"T"),Replacer.ReplaceText,{"last_date_accessed"})convert now this text on actual datetime.
- if in another file there is no field last_date_accessed (as in initial one) error appears. If so correct on proper field name or remove the step. That could be handled automatically, but better to know all possible datetime field names.
Please check in attached file.
SergeiBaklan YOU are a life saver! Thank you so much for the detailed explanation and the file. I'll work with this to better understand these queries.
I greatly appreciate your time & expertise!
Maria Baker , you are welcome. Please ask if there are more questions.
SergeiBaklan Hello! I have been reading up and down this thread since the past two days, as I too have a similar file that I am continuously failing to format. I tried reading into the query steps of the files you guys have provided and apply to my files failing to do so. Could you please help me/ guide me in any way? I am attaching the format of my files below.
I copied the same data multiple times just as an example but this is how the information is going to be laid out for me in a text file. Initially it was the equals to sign '=' instead of the ';' but i replaced it to see if it worked lol. I also tried removing the white spaces. Any kind of help would be highly appreciated!Linkset index ; 14
Linkset name ; GP National
Link selection mask ; B1111
Adjacent DSP index ; 14
DSP name ; GP National
International network DPC ; 000000H
International reserved network DPC ; 000000H
National network DPC ; 0011D8H
National reserved network DPC ; 000000H
OPC ; 001278H
STP flag ; False
Linkset selection mask ; B1111
Master/Slave type ; Master
Linkset index ; 14
Linkset name ; GP National
Link selection mask ; B1111
Adjacent DSP index ; 14
DSP name ; GP National
International network DPC ; 000000H
International reserved network DPC ; 000000H
National network DPC ; 0011D8H
National reserved network DPC ; 000000H
OPC ; 001278H
STP flag ; False
Linkset selection mask ; B1111
Master/Slave type ; Master
Linkset index ; 14
Linkset name ; GP National
Link selection mask ; B1111
Adjacent DSP index ; 14
DSP name ; GP National
International network DPC ; 000000H
International reserved network DPC ; 000000H
National network DPC ; 0011D8H
National reserved network DPC ; 000000H
OPC ; 001278H
STP flag ; False
Linkset selection mask ; B1111
Master/Slave type ; Master
