Icon

kn_​forum_​44692_​csv_​file_​comma

import messy CSV file with double quotes and unmatched quotes at beginning and end (forum 44692)

import messy CSV file with double quotes and unmatched quotes at beginning and end (forum 44692)
https://forum.knime.com/t/not-reading-all-commas-in-column-delimiter/44692/7?u=mlauber71

import messy CSV file with double quotes and unmatched quotes at beginning and end (forum 44692)https://forum.knime.com/t/not-reading-all-commas-in-column-delimiter/44692/7?u=mlauber71 library(readr)file_location <- knime.flow.in[["v_path_csv_file"]]# enter the correct delimiter like , comma ; semicolon, | pipe# , escape_double=TRUE does not seem to workknime.out <- as.data.frame(readr::read_delim(file_location, delim=",", escape_double=TRUE,col_names=TRUE, locale = locale(encoding = "UTF-8")))library(arrow)library(foreign)# export file as parquetvar_loc_parquet <- paste0(knime.flow.in[["var_path_data"]], "test_file.parquet")write_parquet(x=knime.out, sink=var_loc_parquet, compression = "gzip") re_export.csvknime://knime.workflow/data/abc.csvlastIndexOfChar($column1$, '\"')v_path_csv_fileread CSV filev_path*re_export2.tablefirst_characterstart_stringv_quotelength_stringlength_string2adapted legth accoring to leading and closing quotationsexport_columnexport_columnre_export.csvreplace($export_column$,"\"\"" , "\"")=> replace double quotes withsingle quotere_export.tabletest_file.parquetstrip($column1$)make sure last_quotationis at the end CSV Reader Fixed WidthFile Reader String Manipulation collect meta data Java EditVariable (simple) R Source (Table) String to Path(Variable) Table Writer String Manipulation Rule Engine Java EditVariable (simple) String Manipulation Java Snippet(simple) String Manipulation Column Filter CSV Writer String Manipulation Table Writer Parquet Reader String Manipulation Java Snippet(simple) import messy CSV file with double quotes and unmatched quotes at beginning and end (forum 44692)https://forum.knime.com/t/not-reading-all-commas-in-column-delimiter/44692/7?u=mlauber71 library(readr)file_location <- knime.flow.in[["v_path_csv_file"]]# enter the correct delimiter like , comma ; semicolon, | pipe# , escape_double=TRUE does not seem to workknime.out <- as.data.frame(readr::read_delim(file_location, delim=",", escape_double=TRUE,col_names=TRUE, locale = locale(encoding = "UTF-8")))library(arrow)library(foreign)# export file as parquetvar_loc_parquet <- paste0(knime.flow.in[["var_path_data"]], "test_file.parquet")write_parquet(x=knime.out, sink=var_loc_parquet, compression = "gzip") re_export.csvknime://knime.workflow/data/abc.csvlastIndexOfChar($column1$, '\"')v_path_csv_fileread CSV filev_path*re_export2.tablefirst_characterstart_stringv_quotelength_stringlength_string2adapted legth accoring to leading and closing quotationsexport_columnexport_columnre_export.csvreplace($export_column$,"\"\"" , "\"")=> replace double quotes withsingle quotere_export.tabletest_file.parquetstrip($column1$)make sure last_quotationis at the endCSV Reader Fixed WidthFile Reader String Manipulation collect meta data Java EditVariable (simple) R Source (Table) String to Path(Variable) Table Writer String Manipulation Rule Engine Java EditVariable (simple) String Manipulation Java Snippet(simple) String Manipulation Column Filter CSV Writer String Manipulation Table Writer Parquet Reader String Manipulation Java Snippet(simple)

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2022-07-01
On NodePit since: 2022-07-02
Last update: 2024-04-28
Created with KNIME version: v4.6.0
Tags: csvimportmessyquotesrreadr

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!