Skip to main content
To homepage To homepage

Hi all,
 

I create a datatable from Json file with REST data source but got an error as below.

 

‘The bcp client received an invalid column length for column ID 1.’

 

Since the the column is zero based I look for the data in second column in my json file. I have ensured that the first and second columns are not read in the RSD file. However, the problem still persists. I believe the issue may be due to the data in this column being too large.

 

This is API: https://archive-api.open-meteo.com/v1/archive?latitude=51.51&longitude=-0.13&start_date=2018-01-01&end_date=2023-04-15&models=best_match&daily=weathercode,temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,sunrise,sunset,shortwave_radiation_sum,precipitation_sum,rain_sum,snowfall_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant,et0_fao_evapotranspiration&timezone=Europe%2FBerlin

 

How can I solve this issue? 

 

Thank you

Hi @ebay can you please share your data source settings, so I can try recreating the data source connection on my end?

Hi @Christian Hauggaard 

Here you can find my data source settings

Best Regards

 

 

Hi @Christian Hauggaard 

I actually solved the connection problem just now. I added the 'columnsize=10000' parameter for each column in the RSD file. Now I can see the values only in one column. I think I should change flatten arrays settings. I'm attaching a screenshot. I would be glad to share your idea.

 

Hi Erdem

If I add this as the JSONPath $.daily.time It gives me a time table that looks like this.

If you also change the format to Relational it will add a _id field to each of the tables so you can join them together in one table.

 

Hi @ebay  Does Thomas’ answer resolve your issue?

Did you try setting the flatten array property? For more information regarding this property please see this documentation

Hello @Christian Hauggaard  and @Thomas Lind ,

 

instead of the time path section of JSON, I obtained all parameters from "daily" in Views. Then, I merged this data into a column in MDW.  

Is there a shortcut you can suggest instead of using Conditional Lookup in MDW? How can I merge the data coming from the View into one table?

 

Hi Erdem

You made a conversion on the original result and used some view to give it the shared appearance.

You want to add the view to a table or not do it in a view?

Can you share the view you used to do this?

Hi @Thomas Lind 

I created each parameter in the "Views" section. Then, I moved all of these created views to MDW. In MDW, I merged them all in one table using "conditional Lookup". I used dates as a kind of primary key. My question is, was there a shortcut for this process?
 

 

Hi Erdem

Thanks for sharing. I don’t think it would be less work doing this with a custom view or in a table with a custom table insert, so no I don’t think so.

Reply


Terms and ConditionsCookie settings