topic Re: Loading Tab Separated CSV file into Delta Tables in Data Engineering

Loading Tab Separated CSV file into Delta Tables

FabricRooky — Wed, 31 Jul 2024 10:08:43 GMT

Hello,

Is there a way to load a Tab Separated CSV file into Delta Tables from the lakehouse?

The image / error below explains well my paint point :

I guess it is not possible? unless we use spark, or pipeline? can someone confirm?

This is not ideal as less user friendly 😞

Re: Loading Tab Separated CSV file into Delta Tables

frithjof_v — Wed, 31 Jul 2024 15:54:13 GMT

Are the column names in the csv wrapped inside quotation marks?

Re: Loading Tab Separated CSV file into Delta Tables

Anonymous — Thu, 01 Aug 2024 02:52:55 GMT

Hi @FabricRooky

Based on my test, I have the same error when using the GUI Load to Tables feature and defining "\t" as separator. It seems the "\t" cannot be recognized as an effective separator for the CSV file so it reports this error. I'll do some more test to look into whether we can replace "\t" with another character.

As an alternative, you can use Notebook to load data from CSV to Delta table.

Open your current lakehouse, open or create a notebook. Paste below code into a code cell. Modify the file path and table name accordingly. Then run the code cell. This will read data from the CSV file and write it into a new table or overwrite an existing table which has the same name.

df = spark.read.format("csv").option("header","true").option("delimiter", "\t").load("Files/testfile.csv")
table_name = "testTable"
df.write.mode("overwrite").format("delta").save("Tables/" + table_name)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Re: Loading Tab Separated CSV file into Delta Tables

FabricRookyµ007 — Fri, 02 Aug 2024 09:52:52 GMT

no, they are not in qutation marks... (no need, right)

Re: Loading Tab Separated CSV file into Delta Tables

FabricRookyµ007 — Fri, 02 Aug 2024 09:53:23 GMT

This is exactly what I wanted to avoid 😑

Re: Loading Tab Separated CSV file into Delta Tables

frithjof_v — Fri, 02 Aug 2024 11:45:05 GMT

I see. I don't have experience with tab delimited csv.

I tested both with and without quotation marks now. Both got errors.

With quotation marks the error is "Bad CSV header line (...)".

Without quotation marks the error I get is the same as the one you show.

I also tested loading a comma-delimited CSV into table and it works.

So the problem seems to occur when trying to load tab-delimited csv into table.

Re: Loading Tab Separated CSV file into Delta Tables

frithjof_v — Mon, 05 Aug 2024 22:09:29 GMT

Please vote for this idea in order to highlight the need: https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=94e5b20e-9baf-ee11-92bd-6045bd7d9d7c

Re: Loading Tab Separated CSV file into Delta Tables

Anonymous — Tue, 06 Aug 2024 09:01:55 GMT

Hi @FabricRooky

We have escalated it internally and got a confirmation that Tab is treated as a whitespace in Spark which we use to convert the data to a delta table. It is not supported currently. Please vote up the Microsoft Idea. It is in “Under Review” status which means this idea is under PG’s review.

@frithjof_v Thank you for paying attention to this and providing the link to the idea.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!