importing fixed lengthed file problems.

I am trying to import an ASCII fixed length data file into mysql.  I have PHPmyAdmin that I am using the console to try and load the file. (I have also tried using the "Import" tab, but that also is not reading the file correctly.  I create a Database, then in the database I create a table using:

CREATE TABLE CountyCodeFile (
CountyCodeFile_County_Code CHAR(2) not null PRIMARY KEY,
CountyCodeFile_County_Name CHAR(25)
);
LOAD DATA INFILE '/var/lib/mysql-files/countycd.lst' INTO TABLE CountyCodeFile FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY '';

I get an error:

#1262 - Row 1 was truncated; it contained more data than there were input columns

The first two lines of the file to be inputed is:

01ALCONA                  
02ALGER                    

I am working on a Linux (Mint) computer but the files came from our State's Election Board in ASCII format.  I got it working 4-6 years ago but I had problems before it finally "worked" back then.

I have other similar tables and data that have the same problem.
Eric RothoffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
you are likely running into a content issue, comma separated but there are ""

Look at the rows, you may have
you have fields terminated by ','
enclosed by '"'
escaped by ''

the above should be right for comma separated values where you want to exclude the comma when it is within a "user, name" will not be separated on this comma as it is enclosed.....
Eric RothoffAuthor Commented:
My rows are fixed length, they do not have "," between the fields. I use ' TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''  ' to let MySQL to know it is a fixed length file, stating that there is NO " TERMINATED BY ", NO "  ENCLOSED BY  " and NO " ESCAPED BY ". I can not change the format of the files, because they are updated constantly in this format, and the main file is in the GBs.
slightwv (䄆 Netminder) Commented:
Take a look at the example from the docs:
https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Posted by Ryan Neve on July 18, 2008

To load a text file with fixed width columns, I used the form:
LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table>
(@var1)
SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'),
Time=SUBSTR(@var1,14,8),
WindVelocity=SUBSTR(@var1,26,5),
WindDirection=SUBSTR(@var1,33,3),
WindCompass=SUBSTR(@var1,38,3),
WindNorth=SUBSTR(@var1,43,6),
WindEast=SUBSTR(@var1,51,6),
WindSamples=SUBSTR(@var1,61,4);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

arnoldCommented:
What separates the fields, without specifying the length, space, tab, , any similar demarcation can be used to identify the separator of the fields.
Eric RothoffAuthor Commented:
Thanks slightwv, while I didn't need to use the SUBSTR like you suggested yet, the link that you provided got me back to the syntax where changing my "character set" for the file actually solved my problem.  Thanks again.
slightwv (䄆 Netminder) Commented:
No problem.  Happy to indirectly help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.