How to convert csv file from UNIX (LF) format to DOS/Windows (CR-LF) format?

King Java 665

I am having an issue with using CSV files that were generated from HTTP (URI) using GET method and saved as csv file.

I am creating csv file using Azure Logic App.

Below is diagram of my current Logic App.

It just gets data from URI and creates into csv file.

User's image

The issue with generated CSV file is that it is created as UNIX format (so it creates extra " and period ( , )).

So, when I tried to use Python code to extract data from each daily csv file, it creates a problem.

It only works when I open that csv file and save again in a Windows environment.

Is there anything in Azure environment (in Logic App possibly) that specify file format as DOS/Windows (CR-LF) format instead of UNIX (LF) when I create a file or possibly convert from UNIX to DOS/Windows?

Also, the generated CSV file is not a general tabular data, but it contains data in random places in csv file.

So I cannot just import directly into SQL.

This is reference (I posted this question to Stackoverflow):

https://stackoverflow.com/questions/77519494/csv-file-not-processing-in-concatenation-if-not-saved-manually

I have been trying to resolve this issue for last three weeks, so if you can guide me (how to fix in Azure environment), that would be great.

Ben Gimblett 4,545 Reputation points Microsoft Employee

2023-11-24T12:43:25.42+00:00

Hi - thanks for the question

So in simple terms you're asking the Logic App Trigger to reach to a downstream system requesting some CSV content via a GET , then taking the response body from that (the response to that request the trigger makes) and saving it to a file through the create file action

Although the http spec says that "body parts" must be delimited by "CRLF" - I cant see anything that suggests text in the body [part] itself (line endings) need to be CRLF. Generally line endings are OS dependent.

If this is the case here (for example the web host is linux) and it's the web server you're requesting which is responding with data that's using LF not CRLF line endings... its not totally clear in your question and I think you need to be sure of that.... and / or the formatting is not correct then my best suggestion would be offloading the body content to a function and fixing the format in code

There is a text/csv MIME type - but whether this works depends on the host for the web server you're calling

From the question, I don't think this is anything logic apps or the connectors are doing, but analysis of the content being returned from the server (outside of logic apps) would be useful
Ben Gimblett 4,545 Reputation points Microsoft Employee

2023-11-24T12:51:31.3133333+00:00

PS
I should mention you can run custom JS script inline in your logic app as well as dotnet code

JS: https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-add-run-inline-code?tabs=consumption
Dot net code: https://techcommunity.microsoft.com/t5/azure-integration-services-blog/net-framework-custom-code-for-azure-logic-apps-standard-reaches/ba-p/3954619#
King Java 665 Reputation points

2023-11-24T18:09:47.0566667+00:00

@Ben Gimblett Thank you for your response and feedback. I guess I am trying to see if there is any configuration to adjust in Logic App to save in CR-LF (Windows) format. Is there anything I could adjust such as '"body parts" or using "Post" (other than "Get") to make it work? And what do you mean by 'there is a text/csv MIME type'? I tried little bit with Powershell to convert from LF to CR-LF, but it does not seem to work, so I am trying to see if there is anything that can be done from pipeline (Azure).
Ben Gimblett 4,545 Reputation points Microsoft Employee

2023-11-27T12:55:59.5666667+00:00

Hi -

"text/csv" is a mime type (so content type) used to inform the receiver of an http message that the intent is for the body to be text and csv format

For the line endings : I'm not aware of anything from an expression/function perspective in logic apps - as whatever you used would also need to accept the LF line endings

In theory you could roundtrip string through base 64 - because base64 encoding routines typically ignore line breaks.
String =>encode to base64 => decode back to string

Functions exist for this that can be used in Logic Apps - REF https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#base64 and https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#base64ToString

On a windows host the decode should restore line endings with CR+ LF

BUT (1) I didn't try it but more importantly (2) as per the discussion here https://github.com/dotnet/runtime/issues/32452 I don't know if you could rely on it longer term (note the correspondents in that discussion also call out that http SHOULD be CR+LF on all platforms according to the RFC/spec - which is not the case that you observe )

With all that in mind the fastest / easiest way to do this would be to pass each character in the string and replace any single LF with CR+LF -you can do it easily enough in dot net (and I am sure there's some example of doing same in JavaScript)

Both dot net code and JS can be used in an expression in Logic Apps (the dot net capability is very recent)

Does that help?
King Java 665 Reputation points

2023-11-28T19:32:08.2633333+00:00

@Ben Gimblett Thank you for your feedback. I am currently leaning toward using a Powershell to open a csv file (that was formatted in UNIX) and automatically force to save (in Windows). If I open the file and save, my Python query works (because it automatically saves in Windows format. Do you think that is a good idea?
King Java 665 Reputation points

2023-11-28T22:54:46.72+00:00

@Ben Gimblett I think I found a solution using Powershell:

https://stackoverflow.com/questions/57678154/powershell-foreach-loop-for-csv-files-and-save-to-different-folder
Ben Gimblett 4,545 Reputation points Microsoft Employee

2023-11-29T09:10:44.54+00:00

Hi @King Java - I think the advantage of converting the body (after you receive it , but before you save it) is that you dont need to worry about file locking etc, as you've not actually created a file yet. In addition there's no additional process. The input, conversion, output is done in one place and at the same time (in the logic app)

If you use a scripted process against the saved file then you may face additional complications with error handling and file locking semantics- which is typically a bit more painful

So i think your solution would work, but if you can, I'd still replace the LF with CRLF in code (before you save the file) as this would be simpler , more robust and less "brittle" :)

Share via

How to convert csv file from UNIX (LF) format to DOS/Windows (CR-LF) format?

Your answer