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?
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.....
0
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.
0
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);
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arnoldCommented:
What separates the fields, without specifying the length, space, tab, , any similar demarcation can be used to identify the separator of the fields.
0
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.
0
slightwv (䄆 Netminder) Commented:
No problem.  Happy to indirectly help!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.