Prevent Power Query from deleting data after comma in data files

[RaMa87]

Hi all,

can someone please help me with this issue:

I am importing multiple .txt files from a folder (Get Data --> From File --> From Folder).

Each of these files contain hundreds of lines. A lot of these lines (but not all) look like this:

[2019-09-11 15:51:54] Tests: test finished, Passed (Test Information)

In my query I basically want the content of the files as is (in one cell) so I can sort specific lines individually.

My problem now is:

I select "Combine & Edit" but no matter which kind of Delimiter I use (Fixed width and "0" or Custom and "blank") the query always deletes all characters after the comma, i.e. look like this

[2019-09-11 15:51:54] Tests: test finished

Does someone know how I can prevent this? I could import the files as text file but this is not really on option as I need to load several thousand of such files into the query and as far as I know the option "From Text" only allows loading of single files - not complete folders.

Thanks upfront

[#2]

Can you please attach a sample file with 1 or 2 lines (anonymized) of data?

[#3]

@Jan Karel Pieterse 

Sure. I have problems in uploading them though.

I pasted an example below. By simply copy/paste this into a text editor and save the file as a *.log file will be basically the same as the original file.

[2019-09-11 15:50:58] Diagnostics: preparing for tests 101, 201, 203, 901
[2019-09-11 15:51:00] Diagnostics: test finished, Passed (101 | Preparation)
[2019-09-11 15:51:01] Diagnostics: test started (201 | Camera Movement)
Instruction was not accepted: CAMERA:IMAGETAKEN 
[2019-09-11 15:51:19] Diagnostics: test finished, Passed (201 | Camera Movement)
[2019-09-11 15:51:21] Diagnostics: test started (203 | Barcode Read Test)
Instruction was not accepted: CAMERA:IMAGETAKEN 
[2019-09-11 15:51:27] Diagnostics: test finished, Passed (203 | Barcode Read Test)
[2019-09-11 15:51:28] Diagnostics: test started (901 | Reset Instrument)
Instruction was not accepted: CAMERA:IMAGETAKEN 
Instruction was not accepted: DIAG:LIST "*"
[2019-09-11 15:51:38] Diagnostics: test finished, Passed (901 | Reset Instrument)
[2019-09-11 15:51:48] Diagnostics: test started (101 | Preparation)
[2019-09-11 15:51:48] Diagnostics: preparing for tests 101, 201, 203, 901
[2019-09-11 15:51:54] Diagnostics: test finished, Passed (101 | Preparation)
[2019-09-11 15:51:55] Diagnostics: test started (201 | Camera Movement)
[2019-09-11 15:52:13] Diagnostics: test finished, Passed (201 | Camera Movement)
[2019-09-11 15:52:15] Diagnostics: test started (203 | Barcode Read Test)
[2019-09-11 15:52:20] Diagnostics: test finished, Passed (203 | Barcode Read Test)
[2019-09-11 15:52:22] Diagnostics: test started (901 | Reset Instrument)
Instruction was not accepted: DIAG:LIST "*"
[2019-09-11 15:52:32] Diagnostics: test finished, Passed (901 | Reset Instrument)
[2019-09-11 15:52:53] Diagnostics: test started (101 | Preparation)
[2019-09-11 15:52:53] Diagnostics: preparing for tests 101, 201, 203, 901
[2019-09-11 15:52:55] Diagnostics: test finished, Passed (101 | Preparation)
[2019-09-11 15:52:56] Diagnostics: test started (201 | Camera Movement)
[2019-09-11 15:53:13] Diagnostics: test finished, Passed (201 | Camera Movement)
[2019-09-11 15:53:15] Diagnostics: test started (203 | Barcode Read Test)
[2019-09-11 15:53:20] Diagnostics: test finished, Passed (203 | Barcode Read Test)
[2019-09-11 15:53:22] Diagnostics: test started (901 | Reset Instrument)

Thanks for the help!

[#4]

@RaMa87 I copied your sample data into a new textfile named "New Text Document.log".

Then I clicked Get Data, From FIle, From Text/CSV and pointed at that file. I accepted the selected  defaults and clicked "Close and Load to" and chose to load it to a table. See attached.

If you refresh the query will fail, but if you go into the query and edit the first step you can point it to the correct file and location.

[#5 · Accepted solution]

@RaMa87 

Based on your sample data, Fixed Width with 0 worked for me and here is the output in query editor.

[#6]

Yes, that works. However, this is not necessarily feasible since no batch loading is possible then (which would be kind of tedious to load >40.000 files).
However, a solution was already found. Thanks anyways for your efforts!

[#7]

Thanks! For whatever reason that works now?!

[#8]

@RaMa87 

No problem! Glad your issue got resolved.

[#9]

I just used this simple aproach to show I did not experience a problem with your file.