topic Re: JSON to CSV and csv to BigQuery in Talend Studio

JSON to CSV and csv to BigQuery

Anonymous — Sat, 16 Nov 2024 11:09:48 GMT

Hi team,
First of all ingesting csv to BigQuery is due to appending option is available only with csv and cannot merge number of json files into single JSON.
Once tFileFetch is complete, it downloads JSON. I am converting it to csv file inorder to append iterative data. I believe appending not allowed in JSON. My target would be Big Query and Json converted csv has record like this

1086,134,2015-05-21T19:16:45Z,10,Email Test.Subject Line Test,{"attributes":{"name":"Device","value":"iPhone"}}

6th column {"attributes":{"name":"Device","value":"iPhone"}} it being taken as 6th column {"attributes":{"name":"Device" and 7th column "value":"iPhone"}}. Also since it has "" , big query couldnt ingest data which has "", how to remove "" in CSV file? and how to apply escape char to ""

Re: JSON to CSV and csv to BigQuery

Anonymous — Wed, 01 Jul 2015 17:39:01 GMT

If the record:

1086,134,2015-05-21T19:16:45Z,10,Email Test.Subject Line Test,{"attributes":{"name":"Device","value":"iPhone"}}

If the record is somewhere in the flow, you can store it for later usage into globalMap:

globalMap.put("myCSVWithJSONFile",myCSVFileOrLine);

Even without that or later you can issue simply in some tJava or tJavaRow:

String newstring = String.replaceAll("\"","\\\"");

If the there is file on file system you can issue this:

Path path = Paths.get("myCSVFileWithJSON");
Charset charset = StandardCharsets.UTF_8;
String content = new String(Files.readAllBytes(path), charset);
content = content.replaceAll("\"", "\\\"");
Files.write(path, content.getBytes(charset));

You are still in Java world so you can use "anything".
Best regards,
Ladislav