topic Re: Reading a csv file in Data Engineering

Reading a csv file

JissMathew — Thu, 14 Nov 2024 12:03:25 GMT

while try to read a csv file using data frame , read csv using a file format , but fail in case of formatting and column error while loading how the data in databricks ,the code i used for

df = spark.read.format("csv") \

.option("header", "true") \

.option("quote", '"') \

.option("delimiter", ",") \

.option("nullValue", "") \

.option("emptyValue", "NULL") \

.schema(schema) \

.load(f"{bronze_folder_path}/Test.csv")

this is actually data format

Re: Reading a csv file

MuthuLakshmi — Thu, 14 Nov 2024 13:12:37 GMT

@JissMathew What is the error that you are getting when trying to load?

Re: Reading a csv file

JissMathew — Fri, 15 Nov 2024 10:03:29 GMT

@MuthuLakshmi actually, In "adreess" column we need "kochi", and column miss match and get into "name" column , that is the error

Re: Reading a csv file

szymon_dybczak — Fri, 15 Nov 2024 18:01:48 GMT

Hi @JissMathew ,

Could you also provide sample csv file?

Re: Reading a csv file

JissMathew — Mon, 18 Nov 2024 06:06:15 GMT

Hi @szymon_dybczak have only option to send in png, jpg formats

Re: Reading a csv file

holly — Mon, 18 Nov 2024 11:12:24 GMT

Hey, what's the schema you're referencing? The dates are very inconsistent and unlikely to be loaded in as anything useful. It also looks like the delimiter of a comma is causing you issues as it's also within the body of the text without quotes each time. If this is a csv you want to use for a one off instance, you could export it to a tab delimited file (or other delimiter of your choice) and that should go some way to fixing the issue.

Re: Reading a csv file

JissMathew — Mon, 18 Nov 2024 11:33:08 GMT

hey @holly

actually this .option("quote", '"') option in code should have to fix the error but its not working !, is there any standard file format for csv files ?

Re: Reading a csv file

Lakshay — Mon, 18 Nov 2024 16:16:29 GMT

As the "kochi" is in new line, that is causing the issue. Ideally, I would suggest to avoid generating a csv file that has line breaks in a column data. But if you want to handle this scenario, you probably need to put exclusive quotes in your file for each column data so that the line break in a column data are not identified as new row.

Re: Reading a csv file

JissMathew — Tue, 19 Nov 2024 08:43:20 GMT

if there is a option for handle this scenario using a file format for this ? or we have to manually edit in our source file ?

Re: Reading a csv file

gilt — Tue, 19 Nov 2024 09:16:59 GMT

test

Re: Reading a csv file

JissMathew — Tue, 19 Nov 2024 13:35:49 GMT

@gilt test ????

Re: Reading a csv file

Mike_Szklarczyk — Thu, 21 Nov 2024 07:31:19 GMT

You can try add multiline option:

df = ( spark.read.format("csv") .option("header", "true") .option("quote", '"') .option("delimiter", ",") .option("nullValue", "") .option("emptyValue", "NULL") .option("multiline", True) .schema(schema) .load(f"{bronze_folder_path}/Test.csv" )

https://spark.apache.org/docs/3.5.1/sql-data-sources-csv.html

I also encourage you to use the syntax

df = ( spark.read .some_transformation ) rather than df=spark.read \ .some_transformation \

it improves readability and allows you to comment out selected lines

Re: Reading a csv file

JissMathew — Thu, 21 Nov 2024 10:14:40 GMT

@Mike_Szklarczyk Thank you! The issue has been successfully resolved. I sincerely appreciate your guidance and support throughout this process. Your assistance was invaluable. 😊