Hello everyone,
I am new to Power Automate and currently very lost.
I am trying to create a flow that will fetch data from an Oracle database and transform it into a clean CSV file.
I was able to create a simple flow that fetches the data and transform it into csv file. However, because one of the fields will sometimes be written in two rows in our system, it will also be written in two rows in the database, causing the whole thing to include carriage returns, which makes the file not so clean.
My flow currently looks like this:
The ResultSet of this query is for example as follows:
{
"Table1": [
{
"NR": "123456789",
"SERVICE": "Company Name",
"SHIPPER_ADDRESS": "Company PLZ 11111 City",
"CONSIGNEE_ADDRESS": "Customername\r\n DK 8530 Hjortshøj",
"REVEICING_COUNTRY": "DK",
"DELIVERY_NOTE_NUMBER": "1111111111",
"CONSIGNEE_REFERENCE": "1111111111",
"DELIVERY_ROUTE": "123456",
"CREATION_DATE": "2024-05-14T00:10:20",
"PICKUP_DATE": "2024-05-14T00:00:00",
"DELIVERY_DATE": "2024-05-15T00:00:00",
"SHIPMENT_WEIGHT": 25.73,
"SHIPMENT_VOLUME": 0.228,
"NUMBER_OF_TIRES": 2
}
The characters \r\n will be given already at the beginning because the database already includes those characters.
The ResultSet for Parse JSON is as follows:
{
"type": "object",
"properties": {
"Table1": {
"type": "array",
"items": {
"type": "object",
"properties": {
"NR": {
"type": "string"
},
"SERVICE": {
"type": "string"
},
"SHIPPER_ADDRESS": {
"type": "string"
},
"CONSIGNEE_ADDRESS": {
"type": "string"
},
"REVEICING_COUNTRY": {
"type": "string"
},
"DELIVERY_NOTE_NUMBER": {
"type": "string"
},
"CONSIGNEE_REFERENCE": {
"type": "string"
},
"DELIVERY_ROUTE": {
"type": [
"string",
"null"
]
},
"CREATION_DATE": {
"type": "string"
},
"PICKUP_DATE": {
"type": "string"
},
"DELIVERY_DATE": {
"type": "string"
},
"SHIPMENT_WEIGHT": {
"type": "number"
},
"SHIPMENT_VOLUME": {
"type": "number"
},
"NUMBER_OF_TIRES": {
"type": "integer"
}
},
"required": [
"NR",
"SERVICE",
"SHIPPER_ADDRESS",
"CONSIGNEE_ADDRESS",
"REVEICING_COUNTRY",
"DELIVERY_NOTE_NUMBER",
"CONSIGNEE_REFERENCE",
"DELIVERY_ROUTE",
"CREATION_DATE",
"PICKUP_DATE",
"DELIVERY_DATE",
"SHIPMENT_WEIGHT",
"SHIPMENT_VOLUME",
"NUMBER_OF_TIRES"
]
}
}
}
}
To remove double quotes, I used the following expression:
replace(body('Create_CSV_table'), '"', '')
The output of the "removeDoubleQuotes" action is then as follows:
NR,SERVICE,SHIPPER_ADDRESS,CONSIGNEE_ADDRESS,REVEICING_COUNTRY,DELIVERY_NOTE_NUMBER,CONSIGNEE_REFERENCE,DELIVERY_ROUTE,CREATION_DATE,PICKUP_DATE,DELIVERY_DATE,SHIPMENT_WEIGHT,SHIPMENT_VOLUME,NUMBER_OF_TIRES
123456789,Company Name,Company PLZ 11111 City,Customername
DK, 8530 Hjortshøj,DK,1111111111,1111111111,123456,2024-05-14T00:10:20,2024-05-14T00:00:00,2024-05-15T00:00:00,25.73,0.228,2
The column names are written in one line, but due to those characters, the output is giving me a carriage return.
I want those records to be written like this:
123456789,Company Name,Company PLZ 11111 City,Customername DK 8530 Hjortshøj,DK,1111111111,1111111111,123456,2024-05-14T00:10:20,2024-05-14T00:00:00,2024-05-15T00:00:00,25.73,0.228,2
I thought at the beginning that the double quotes were the problem, but then I saw those characters.
These characters are occurring in the column CONSIGNEE_ADDRESS for each new line that the database returns.
Could you please help me get rid of those breaks? I hope I provided all of the needed info.
Thank you in advance.
