Limited number of columns when using import to a table?

Data Service
1

While importing a csv to a table (existing or not) it seems there is a limit of 37 columns? Because each time I’m trying to import more than 37 columns an error is returned…

2

Hello @Jean-Marc_Lebourg

Welcome to our community and thank you for trying out Backendless.

What error do you get?

3

Hello Sergey,
I get this in the log:
“IMPORT_DATA Import of tables failed: java.sql.SQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs”
I tried with different fields and data types, it’s always the same, if I try to import more than 37 fields, the import fails

4

@Jean-Marc_Lebourg

change some column types from String to TEXT

5

It doesn’t change anything, I get always the same error…
Here is my CSV:

“siret”,“naf”,“tvaIntra”,“civility”,“firstName”,“lastName”,“birthday”,“estCreated”,“nicRecto”,“nicVerso”,“corpMandate”,“estName”,“estTradeName”,“estType1”,“estType2”,“estType3”,“estType4”,“qrCode”,“description”,“estLogo”,“storeFrontPic”,“addrLocation”,“addrBuilding”,“addrNumber”,“addrCompNum”,“addrTypeOfTrack”,“addrTrackName”,“addrZip”,“addrCity”,“addrCountry”,“phoneLandLine”,“phoneMobile1”,“phoneMobile2”,“priceCateg”,“eMail1”,“eMail2”,“webSite”,“avatar”,“facebook”,“twitter”,“instagram”,“youtube”,“tiktok”,“otherURL1”,“otherURL2”,“plan”,“rem”,“status”
“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“01/01/1900 00:00:00”,“01/01/1900 00:00:00”,“/files/estFiles/estPics/nics/missingFile.png”,“/files/estFiles/estPics/nics/missingFile.png”,“n/a”,“n/a”,“n/a”,“0”,“0”,“0”,“0”,“n/a”,“n/a”,“/files/estFiles/estPics/logos/missingFile.png”,“/files/estFiles/estPics/ests/missingFile.png”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“0”,“n/a”,“n/a”,“n/a”,“/files/estFiles/estPics/avatars/anonymous.png”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”,“n/a”

Knowing that for the import column type mapping I set:

  • STRING for all the columns where value is “n/a”
  • FILE_REF for the columns with a path value
  • DATETIME for the columns with a date value
    INT for the columns with “0”
6

Hello @Jean-Marc_Lebourg ,

Could you please share your CSV file, which will contain column mapping and at least 1 object?

Regards,
Inna

7

Hello Inna,

Here is the file…
Ests.csv (1.1 KB)

Best,
Jean-Marc

8

I’ll try your example.
But want to ask, didn’t you thing about some sort of normalization of your data? The amount of columns decreases in such an approach, and you also receive a flexibility when you work with the data (select/update requests).

9

You mean remove the “n/a” by default? and set empty values instead? like “”,“”,“”…

10

No. Empty values are still columns.
I was talking about normalization: Database normalization - Wikipedia

11

I’ve checked your example.
Yes, the problem is in the total length of data for one row (each DBMS has such limitation).
Each STRING column reserves particular amount of bytes (regardless of whether data is actually available or not).
So, you need to divide the data into parts that will be organized into related tables.

12

Thank you Oleg,
I’ve read too quickly this morning, I know for normalization, indeed I can surely do something about that!
Actually our database is not well built because I wanted to ease the work of my coworker who works with Excel, but you’re right I have to optimize all of this.
I’ll let you know if the import is successful after that.
Best,
Jean-Marc