This article is a follow-up to our previous article which deals with a file processing problem by Kibana. It ends with 3 available solutions to prepare a file for kibana.
In this article, I propose to explain the approach that worked for me and what I have done to solve the issue.
Opening the file through Open Office
I opened Open Office alone, via the general application.
I created a new binder there and then inserted my file. To do this, I went to the “insert” tab and selected “sheet from file”.
I have selected my csv. A dialog window opens.
I have chosen to tick only the separator of which my file is composed, in my case, the comma.
On the first line of the dialog box, I select UFT-8 unicode and choose the language of my file. (Here: English USA)
Then, in the fields section, please scroll to the rightto check if each column looks like well parsed.
Validate. The window closes to give way to the first window that is displayed when selecting the insertion from a file. It allows you to choose where you want your file to open.
Then, validate once again and import data into your file.
Prepare a file for kibana now in Open Office
Firstly, I will check the first lines of my file and verify that no special characters hinder Kibana’s understanding of the file.
To do this, use the search function in the toolbar. You enter your separator, the comma and press “Enter”.
After a few clicks, we see that several fields contain commas. And this is where we will act.
Replace problematic characters with others
To solve the problem of commas in our fields, we are going to replace all the commas that are not the separators of our fields. But we are not going to do this manually, which would take too much time over several thousand lines.
So we’re going to use the Open Office “search and replace” function:
We select the whole of our file, then we go to “Option” (in the toolbar), we click on “Search and replace”.
In search, you have to enter its separator or the special character that may prevent Kibana from processing the file properly. In our case, it is a comma. We click on “search all”. The operation can take a while.
When the search is finished, you indicate what you want to replace your character with. If you just want to delete what you have searched for and want to replace, just leave the “replace” field blank.
This is what we will do in our case. We press “replace” and wait again. This operation can also take a while.
And of course, replacing in this way will not affect the file separators.
Once replacement is completed, you can close the Find & Replace window and save your file. It is best to “save as” to be able to create a new one and differentiate it from the original one.
In “save as”, save it in .csv format. An “export to text file” window will open.
In “character set”, select “Unicode UTF-8” and then your field separator.
Save it now. You know now how to prepare a file for kibana.