Skip to main content
To homepage To homepage
Solved

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

E
Contributor
Forum|alt.badge.img
  • ebay
  • Contributor
  • 26 replies

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

Best answer by Thomas Lind

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.

 

View original
Did this topic help you find an answer to your question?

9 replies

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

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

E
Contributor
Forum|alt.badge.img
  • ebay
  • Author
  • Contributor
  • 26 replies
  • April 24, 2023

Hi @Christian Hauggaard 

Here you can find my data source settings

Best Regards

 

 

E
Contributor
Forum|alt.badge.img
  • ebay
  • Author
  • Contributor
  • 26 replies
  • April 24, 2023

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
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1035 replies
  • Answer
  • April 24, 2023

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
Community Manager
Forum|alt.badge.img+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
Contributor
Forum|alt.badge.img
  • ebay
  • Author
  • Contributor
  • 26 replies
  • April 28, 2023

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
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1035 replies
  • April 28, 2023

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
Contributor
Forum|alt.badge.img
  • ebay
  • Author
  • Contributor
  • 26 replies
  • April 28, 2023

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
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1035 replies
  • May 1, 2023

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


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings