I'm trying to create a Spark table using a CSV as source. The CSV file has row delimiter of \r\n, however, the last field in the file is returning a \r when an empty string. According to documentation, \r\n should be handled by default lineSep (default covers all \r , \r\n , and \n ): defines the line separator that should be used for parsing. Maximum length is 1 character. This is clearly not the case but I am getting no change in the result should I specify the lineSep within the options (regardless of whether I try \r, \r\n or \n) which suggests it is something we can't override (or lineSep isn't the correct syntax), any other suggestions? Thanks

Spark Table using CSV - Line Separator

Ryan Abbey 1,171

I'm trying to create a Spark table using a CSV as source. The CSV file has row delimiter of \r\n, however, the last field in the file is returning a \r when an empty string.

Result

Source value

According to documentation, \r\n should be handled by default

lineSep (default covers all \r, \r\n, and \n): defines the line separator that should be used for parsing. Maximum length is 1 character.

This is clearly not the case but I am getting no change in the result should I specify the lineSep within the options (regardless of whether I try \r, \r\n or \n) which suggests it is something we can't override (or lineSep isn't the correct syntax), any other suggestions?

Thanks

AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2023-02-09T10:18:24.9933333+00:00

Hi Ryan Abbey ,

Thanks for using Microsoft Q&A platform and thanks for posting your query here.

As I understand your issue, you are trying to create spark table using CSV as source where last column of last row doesn't contain any data , however, when you are reading that in notebook, it is showing some garbage data. Please let me know if that is not the issue here.

Could you please share the full snippet code you are running so that it would be easier for us to repro from our end. Thanks in advance.
AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2023-02-09T15:58:43.2866667+00:00

Hi Ryan Abbey ,

I tried to reproduce your issue by creating a sample data file having last cell as empty string. For me, it's not getting overwritten with any other value. Neither the dataframe, nor the table is getting populated with any value other than empty string.

It would be great if you could share your sample data and the code you have tried so far in order to repro your case. Thankyou.
Ryan Abbey 1,171 Reputation points

2023-02-09T23:25:23.72+00:00

Thanks for the time, the issue isn't related to creating a table from a CSV file but rather an external table with the CSV as its source

As a little extension on what we're doing, we are trying to process files generated via D365 Synapse Link. This process creates a series of CSV files based on the CDM format. By taking the definition of the files, we should be able to create an external table in spark that reads these CSV files - this is mostly fine but issues like above are showing and can be better demonstrated with latest file not being processed correctly

In this file, the last field should be an integer

However, if we hex the values, we can see it is including "0D" (the carriage return)

If we attempt to declare that columns as an int, the parsing does not work and ALL columns become null
AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2023-02-15T11:54:52.37+00:00
Hi Ryan Abbey ,

Thankyou for the follow up query. I didn't clearly understand , how you are trying to hex the values. If I see, hexadecimal value of 500 should be equivalent to '1F4' . As I requested above, kindly share your full code snippet for us to be able to repro your issue.

Meanwhile, I would suggest you to go for the below workarounds to either use TRIM/REPLACE function to remove the unwanted characters.

Since the last field is not being parsed correctly as an integer because it includes a carriage return (0D). To resolve this issue, you can try the following steps:

Use the TRIM / REPLACE function to remove the trailing whitespaces from the last field.

Use the CAST function to convert the last field to an integer.

Thankyou. Kindly hit on 'Accept answer' button if it was helpful. Thanks.
AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2023-02-17T06:49:57.2966667+00:00

Hi Ryan Abbey ,

Did you get a chance to visit the previous response and try to see if it helps?
Ryan Abbey 1,171 Reputation points

2023-02-20T22:26:35.3933333+00:00

We already have workarounds, we are looking for a proper solution that allows the file to be correctly parsed

PS I'm hexing by using the hex function, those values you're looking at might look numeric but because of the carriage return, they're treated as string