To homepage To homepage
Solved

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

  • 20 April 2023
  • 9 replies
  • 420 views
E
Rank
Badge

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

icon

Best answer by Thomas Lind 24 April 2023, 13:22

View original

9 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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

E
Rank
Badge

Hi @Christian Hauggaard 

Here you can find my data source settings

Best Regards

 

 

E
Rank
Badge

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.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

 

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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

E
Rank
Badge

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?

 

Thomas Lind
Rank
Userlevel 6
Badge +5

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?

E
Rank
Badge

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?
 

 

Thomas Lind
Rank
Userlevel 6
Badge +5

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