I’m using an ADF copy activity to copy data from an API (HTTP linked service) to a CSV file in a Blob Storage.
The response from the API looks like this :
{"results":"Customer Name,Cloud Account Name,Product Name,SKU Name,Term And Billing Cycle,Subscription ID,Domain Name,Price book,Seller Cost (EUR),Customer Cost (EUR),Margin (EUR),Usage Quantity,Unit,Region,Seat Count,Subscription StartDate,Subscription EndDate,Charge StartDate,Charge EndDate,Invoice Number,Customer Companyn"TEST COMPANY","abcd","Test","Test","Test","abcd","Test","",0.0000000000000,0.0000000000000,0.0000000000000,0.0000000000000,"Test","",1,2024-01-01,2024-01-01,2024-01-01,2024-01-01,"Test","TEST COMPANY","abcd","Test","Test","Test","abcd","Test","",0.0000000000000,0.0000000000000,0.0000000000000,0.0000000000000,"Test","",1,2024-01-01,2024-01-01,2024-01-01,2024-01-01,"Test"","resultCount":"24"}
I’m trying to extract data from the “results” object, but CSV data is written in string. How can I extract CSV string data into a CSV file in a blob storage ?
I tried to copy HTTP response as JSON format to JSON file in a blob storage and then to copy JSON file into a CSV file thanks to a complex mapping (using “results” as collection reference), but CSV file was written in a one line way and was unreadable from ADF (to copy CSV file to SQL table for example).
2
Issue may be with your Json format, that may be the reason to get your CSV file was written in a one-line way. Modify your API response that means Json into below format:
{
"results": [
{
"Customer Name": "TEST COMPANY",
"Cloud Account Name": "abcd",
"Product Name": "Test",
"SKU Name": "Test",
"Term And Billing Cycle": "Test",
"Subscription ID": "abcd",
"Domain Name": "Test",
"Price book": "",
"Seller Cost (EUR)": 0.0,
"Customer Cost (EUR)": 0.0,
"Margin (EUR)": 0.0,
"Usage Quantity": 0.0,
"Unit": "Test",
"Region": "",
"Seat Count": 1,
"Subscription StartDate": "2024-01-01",
"Subscription EndDate": "2024-01-01",
"Charge StartDate": "2024-01-01",
"Charge EndDate": "2024-01-01",
"Invoice Number": "Test",
"Customer Company": "TEST COMPANY"
},
{
"Customer Name": "abcd",
"Cloud Account Name": "Test",
"Product Name": "Test",
"SKU Name": "abcd",
"Term And Billing Cycle": "Test",
"Subscription ID": "",
"Domain Name": "0.0",
"Price book": "0.0",
"Seller Cost (EUR)": 0.0,
"Customer Cost (EUR)": 0.0,
"Margin (EUR)": 0.0,
"Usage Quantity": 0.0,
"Unit": "Test",
"Region": "",
"Seat Count": 1,
"Subscription StartDate": "2024-01-01",
"Subscription EndDate": "2024-01-01",
"Charge StartDate": "2024-01-01",
"Charge EndDate": "2024-01-01",
"Invoice Number": "Test"
}
],
"resultCount": 24
}
After Json modification you can use copy activity to copy Json into csv file as follows:
Create Json dataset with above Json file provide it as source provide csv dataset for sink, go to mapping enable Advanced editor and provide $['results'] as Collection reference, then you will get the columns as follows:
Debug the pipeline after successful pipeline debug you will get the csv file as shown below:
Results
| Customer Name | Cloud Account Name | Product Name | SKU Name | Term And Billing Cycle | Subscription ID | Domain Name | Price book | Seller Cost (EUR) | Customer Cost (EUR) | Margin (EUR) | Usage Quantity | Unit | Region | Seat Count | Subscription StartDate | Subscription EndDate | Charge StartDate | Charge EndDate | Invoice Number | Customer Company |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| TEST COMPANY | abcd | Test | Test | Test | abcd | Test | 0 | 0 | 0 | 0 | Test | 1 | 2024-01-01 | 2024-01-01 | 2024-01-01 | 2024-01-01 | Test | TEST COMPANY | ||
| abcd | Test | Test | abcd | Test | 0.0 | 0.0 | 0 | 0 | Test | 1 | 2024-01-01 | 2024-01-01 | 2024-01-01 | 2024-01-01 | Test |
1