CSV File Load Error - Data Too Long or Value Too Long

  You are trying to load a CSV file into a database, and almost inevitably, you run into an ERROR: value too long for type character varying 64 if you are using PostgreSQL.  Or SQL Error [1406] [22001]: Data truncation: Data too long for column 'Name' at row 1 if you are using MariaDB or MySQL.

It doesn't matter if you are using the DBeaver application or the native database import tools. Newbies will look at the first line of the CSV file to examine the very first line and scratch their heads, as there is no error in the first line.

The database is ingesting the data one line at a time, so each line in the file is the first line. Your line in the CSV file is hiding deep in the file. And there may be many of them in there. 

The error message you receive is helpful, but it DOES NOT tell you which line in the CSV file is causing you this grief. You could use an 'eyeball inspection' of the data, which quickly becomes tedious. And if the CSV file has thousands of lines, your odds of finding the offending line grow longer.

Splitting up the file into smaller chunks is labor-intensive.

Example

I was loading data from the HMS Titanic data set when I received the following error:

Error occurred during batch insert
(you can disable batch insert to skip particular rows).

Reason:

SQL Error [22001]: Batch entry 0 INSERT INTO public.titanic ("PassengerId","Survived","Pclass","Name","Sex","Age","SibSp","Parch","Ticket","Fare","Cabin","Embarked")

VALUES (('308'::int8),('1'::int8),('1'::int8),('Penasco y Castellana, Mrs. Victor de Satode (Maria Josefa Perez de Soto y Vallejo)'),('female'),('17'::int8),('1'::int8),('0'::int8),('PC 17758'),('108.9'::real),('C65'),('C')) was aborted: ERROR: value too long for type character varying(64)  Call getNextException to see other errors in the batch.

A Difference Between PostgreSQL and MariaDB/MySQL

PostgreSQL will return the offending line in the error message, as shown above. MariaDB and MySQL do not. They provide only SQL Error [1406] [22001]: Data truncation: Data too long for column 'Name' at row 1.

DBeaver's import tool selected a VARCHAR(64) for the name field after examining some of the records. The unfortunate Penasco y Castellana, Mrs. Victor de Satode (Maria Josefa Perez de Soto y Vallejo needs more space. And there may be other records with names as long, or longer.

Solution

You can use Google Sheets to examine the CSV file before attempting to load it into the database. 

We can use =max(arrayformula(len(D1:D891))) on the columns with the names to determine the maximum length of a name. The longest name in this data is eighty-two characters long.  

If you read the linked blog post, you will see that I defined the Name column as a TEXT field to solve the issue. But what if you are trying to save space? Knowing the width of that column is valuable.

So now you have a tool to determine the width of a column when you get the dreaded 'value too long' 

Comments

Post a Comment

Popular posts from this blog

Migrating From MySQL To PostgreSQL in Five Simple Steps

Image
There are many reasons you may want to migrate from MySQL to PostgreSQL, which we will skip over for brevity.  I see many questions about moving tables and data, and sadly, the answers range from sloppy to incredibly complicated.  Each database migration is unique, as issues range from heavily relying on a vendor's feature to extremely complex schemas that require attention to minute detail. Step 1 This may seem obvious, but the first step is access to the original 'source' database. You will need FULL admin access to the database.  If you do not have full access for security, administrative, or other reasons, you will most likely not receive all the desired data.  We will use DBeaver Enterprise to connect to the source database. We need to choose a MySQL database driver Configure the connection We will be porting the World database. In this example, we will port the MySQL World database. This dataset has been used for decades in MySQL documentation, training, and ex...
Read more

Does Artificial Intelligence Query MySQL Better Than You?

Image
 The signal-to-noise ratio in the hype for Artificial Intelligence is incredibly high. There are many claims that AI will replace software developers. Many years ago, developers were told to find other jobs as a new programming language was designed to be written by nonprogrammers, replacing expensive coders. The Common Business Orientated Language, or COBOL, was a big hit and is still very popular in some sectors. But it did not replace programmers. But can an AI write Structured Query Language (SQL) as well for use with MySQL, or better than you?  I decided to use the MySQL World and Sakila databases because they have been around for decades. Nearly everyone who has had tangential exposure to MySQL has used them, and their tables are easy to understand. These tried and true schemas have been used for documentation, blogs, webinars, and demos for years, so I had little doubt that some of that had been scraped and fed into the AI. I randomly picked Grok as the AI and will try ...
Read more

Database Administration Query Repository

 I have started a repository of handy SQL Queries for Database Administration, which I have collected over the years. Finding unused or duplicate indexes or queries without indexes and other information can speed up response times. Knowing statistics like cache hit rates can aid in planning upgrades.  I am currently populating this repo as I run across the queries as part of my 'spring cleaning. ' I welcome your participation if you have a handy bit of SQL for an open-source database you'd like to share. The repo is at https://github.com/davestokes/HandySQL , and I will be adding to it over the next few months. If you have a question that you would like to see addressed, please don't hesitate to let me know. I will try to accommodate you. 
Read more