https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12678#M7450 <P>So obvious. Thanks adding following option solve it</P><PRE><CODE>.option("escape","\"")</CODE></PRE><P></P><P></P> Thu, 12 Jan 2023 09:17:23 GMT alxsbn 2023-01-12T09:17:23Z https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12676#M7448 <P>Hello ! </P><P></P><P>I playing with autoloader schema inference on a big S3 repo with +300 tables and large CSV files. I'm looking at autoloader with great attention, as it can be a great time saver on our ingestion process (data comes from a transactional DB generated through a CDC feature).</P><P></P><P>My code is pretty standard:</P><PRE><CODE>(spark.readStream .format("cloudFiles") .option("cloudFiles.format", "csv") .option("cloudFiles.schemaLocation", target_table_path) .option("cloudFiles.inferColumnTypes", True) .load(source_table_path) .writeStream .option("checkpointLocation", target_table_path) .trigger(availableNow=True) .toTable(table) )</CODE></PRE><P>I have some CSV files using comma delimiters having some cells inside whom I have JSON data. A quick extract : </P><PRE><CODE>Op,TIMESTAMP,field_1,field_2,field_3,field_4,date_add,date_upd,percent,matches U,2023-01-10 16:14:26.000000,539775799,74793,688,+1.00000000e+02,2021-04-13 21:24:39,2023-01-10 16:14:26,78,"[{""name"":""age_40_50"",""value"":0},{""name"":""xxxx"",""value"":0},{""name"":""xxxx"",""value"":4},{""name"":""xxxx"",""value"":0},{""name"":""xxxx"",""value"":0},{""name"":""xxxx"",""value"":4},{""name"":""xxxx"",""value"":4},{""name"":""xxxx"",""value"":1},{""name"":""xxxx"",""value"":4},{""name"":""xxxx"",""value"":4},{""name"":""***"",""value"":4}]"</CODE></PRE><P>Autoloader recognize my last column as a string and didn't escape the comma inside this column.</P><P></P><P>Of course, I can regenerate my sources with another delimiter, but I look at autoloader options to do this on the more easy way. I know I can playing with data afterwards or using some select to flatten the JSON as a struct (since I want to infer a lot of tables, exceptions it's what I want to avoid).</P><P></P><P>Thanks for you help,</P><P></P><P></P> Wed, 11 Jan 2023 10:40:56 GMT https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12676#M7448 alxsbn 2023-01-11T10:40:56Z https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12677#M7449 <P>PySpark by default is using \ as an escape character. You can change it to "</P><P>Doc: <A href="https://docs.databricks.com/ingestion/auto-loader/options.html#csv-options" target="test_blank">https://docs.databricks.com/ingestion/auto-loader/options.html#csv-options</A></P> Wed, 11 Jan 2023 11:43:05 GMT https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12677#M7449 daniel_sahal 2023-01-11T11:43:05Z https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12678#M7450 <P>So obvious. Thanks adding following option solve it</P><PRE><CODE>.option("escape","\"")</CODE></PRE><P></P><P></P> Thu, 12 Jan 2023 09:17:23 GMT https://community.databricks.com/t5/data-engineering/autloader-on-csv-file-didn-t-infer-well-cell-with-json-data/m-p/12678#M7450 alxsbn 2023-01-12T09:17:23Z