Hello,
i have a csv data with newline in some of the column data.
Whenever the data start with ", it contain newline.
testing2.csv
date,name,address
5/23/2024,"Lee123
Hello3",Tanjung789
5/24/2024,Lee124,Tanjung790
5/25/2024,Lee125,Tanjung791
5/26/2024,Lee126,Tanjung792
5/27/2024,Lee127,Tanjung793
5/28/2024,"Lee128
Joke",Tanjung794
i tried multiple method but still fail to output the correct data.
Method 1
this method, the script is stuck at
input {
file {
path => "C:\Users\ABCD\Desktop\ELK\logstash-8.6.2\testing2.csv"
start_position => "beginning"
sincedb_path => "NUL"
codec => multiline {
pattern => "^\\d{1,2}/\\d{1,2}/\\d{4}," # Example pattern for matching records
negate => "true"
what => "previous"
}
}
}
filter {
csv {
separator => ","
quote_char => '"'
columns => ["date", "name", "address"]
}
}
output {
file{
path => "./test.json"
}
}
Method 2
This method show Error parsing csv.
[Error parsing csv {:field=>"message", :source=>"Thong",Tanjung813\r", :exception=>#<CSV::MalformedCSVError: Illegal quoting in line 1.>} ]
input {
file {
path => "C:/Users/ABCD/Downloads/testing2.csv"
start_position => "beginning"
sincedb_path => "NUL"
}
}
filter {
csv {
separator => ","
skip_header => "true"
columns => ["date","name","address"]
quote_char => '"'
#autodetect_column_names => "true"
}
mutate {
remove_field => ["message"] # Remove the original message field if not needed
}
}
output {
file{
path => "./test.json"
}
}
Method 3
The method 3 is a very simple approach.
input {
file {
path => "C:\Users\ABCD\Desktop\ELK\logstash-8.6.2\testing2.csv"
start_position => "beginning"
sincedb_path => "NUL"
}
}
filter {
csv {
separator => ","
skip_header => "true"
columns => ["date","name","address"]
#autodetect_column_names => "true"
}
mutate {
remove_field => ["message"] # Remove the original message field if not needed
}
}
output {
file{
path => "./test.json"
}
}
Below output is from the method 3.
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/5/2024,Lee136,Tanjung802\r"},"name":"Lee136","address":"Tanjung802","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.082263Z","date":"6/5/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/19/2024,Lee150,Tanjung816\r"},"name":"Lee150","address":"Tanjung816","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.086262Z","date":"6/19/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"5/27/2024,Lee127,Tanjung793\r"},"name":"Lee127","address":"Tanjung793","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.081263400Z","date":"5/27/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/10/2024,Lee141,Tanjung807\r"},"name":"Lee141","address":"Tanjung807","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.083262500Z","date":"6/10/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"5/24/2024,Lee124,Tanjung790\r"},"name":"Lee124","address":"Tanjung790","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.080264100Z","date":"5/24/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/8/2024,Lee139,Tanjung805\r"},"name":"Lee139","address":"Tanjung805","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.083262500Z","date":"6/8/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/22/2024,Lee153,Tanjung819\r"},"name":"Lee153","address":"Tanjung819","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.086262Z","date":"6/22/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/4/2024,Lee135,Tanjung801\r"},"name":"Lee135","address":"Tanjung801","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.082263Z","date":"6/4/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/18/2024,Lee149,Tanjung815\r"},"name":"Lee149","address":"Tanjung815","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.084262700Z","date":"6/18/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"5/29/2024,Lee129,Tanjung795\r"},"name":"Lee129","address":"Tanjung795","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.081263400Z","date":"5/29/2024"}
{"host":{"name":"SUPER_PC"},"@version":"1","event":{"original":"6/13/2024,Lee144,Tanjung810\r"},"name":"Lee144","address":"Tanjung810","log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.084262700Z","date":"6/13/2024"}
{"host":{"name":"SUPER_PC"},"tags":["_csvparsefailure"],"@version":"1","event":{"original":"5/28/2024,\"Lee128"},"log":{"file":{"path":"C:/Users/LEL2PG/Downloads/testing2.csv"}},"@timestamp":"2024-09-10T11:31:25.081263400Z"}
The task i need to perform is to digest daily generated csv file and upload it to elastic. Please help me on this, or if there is other elastic tools that able to handle this "daily automated upload" please do let me know. Thanks so much in advance.