Loading Tab Separated CSV file into Delta Tables

[FabricRooky]

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 😞

[Anonymous · Accepted solution]

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!

[Anonymous]

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!

[FabricRookyµ007]

This is exactly what I wanted to avoid 😑

[frithjof_v]

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

[frithjof_v]

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

[FabricRookyµ007]

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

[frithjof_v]

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.