Howdy Folks! First time here and have high hopes based on comments I've read about this forum surfing elsewhere. I'm a noob to MySQL but an old mainframe programmer from back in the '70s.

I've created a database on my host server using MySQL. The purpose of the database is to store information about high school classmates. The first table is a simple 'Names' table (key, firstname, middlename, lastname, suffix, marriedname, sex_indicator). Getting this table loaded correctly is key to my site management because I will use it to validate and allow users to access and update files. This way I hope to prevent prankster corruption of information.

I want to populate this table from a list that has been proofread and edited extensively. It goes to a little over 500 names; so rekeying all the data would be both time consuming and error prone. I've edited the list of names creating tab-separated fields with nulls (backslash capital N) where no data exists. I created 3-digit key values starting with '000' and incrementing by 1. I saved the file as MS Windows plain text with CR/LF for line breaks.

At first I tried to load the table from the local file using the LOAD DATA LOCAL INFILE '/path/file' INTO TABLE tablename LINES TERMINATED BY '\r\n'; statement (with appropriate path/file and table names). This gave me NO error AND failed to update a single record in the table. I did all kinds of searches on the data file looking for special characters and found none. I tried various options for the syntax of the '/path/file' element with no change in results.

I next ran a few records of this file through the PHPMyAdmin (PAM) Import files function and got a 1064 error which I understand to mean a parse error which I think means there is something incompatible between my data and my table data definitions. I can't see the error/inconsistency but I am perhaps too close to the forest to see it.

WHEW!! What a lot of typing to describe something so simple and basic.

Anyway, can anyone here help this old noob? I'll provide specific table definitions and data file samples if you'll tell me how to do so.

Thanks in advance.

Bob H - The Ancient Noob

Can you maybe post a few lines of sample data and your data types for your table fields? This might help us get to the source a bit faster.

OK. Thanks for the response!


Here's the table defs:
fieldname type attributes Null-Default extra

primary_key tinyint(3) UNSIGNED ZEROFILL No auto_increment
firstname varchar(15) No
middle varchar(15) Yes NULL
lastname varchar(20) No
suffix varchar(5) Yes NULL
marriedname varchar(20) Yes NULL
sex_ char(1) Yes F


Here's the first 10 data records:
000 James McKenzie Adams \N \N M
001 Jane Laverne Adams \N \N \N
002 John W Allen \N \N M
003 Joyce Lyvonne Allen \N \N \N
004 Margaret Jean Allen \N \N \N
005 Ellen Donna Allsbrook \N \N \N
006 Robert Chapman Anderson \N \N M
007 Azalee Truedell Archer \N \N \N
008 Donald Tex Bagwell \N \N M
009 Cynthia Anne Bailey \N \N \N


Although it doesn't show, the data (created in MS WORD) was saved as plain text (*.txt) as MS Windows with CR/LF inserted after each line. There are NO spaces; the area between data elements is actually tab characters.


Again, thanks for taking a look at this. I've looked until I can't see anything!

BobH - The Ancient Noob

Hmm - first thing I notice right off my head is this :

primary_key tinyint(3) UNSIGNED ZEROFILL No auto_increment

I know you want this field primary key, but is it called primary key?

Aside from that, do you have your table creation syntax handy to view? i.e :

 
CREATE TABLE bobstable (
primarykeyname tinyint(3) UNSIGNED ZEROFILL No auto_increment ,
PRIMARY KEY (primarykeyname)
);

I'll be in and out of here today, and if I can get that from you - I'll plug it into a DB on my server and see what I can come up with.

No, sorry. I don't have the table creation code because I created it using the GUI features of PAM instead of using coded SQL or PHP statements.

What's wrong with using the primary_key field name? I defined it because I'm looking ahead to having several more tables before I'm done, with data in each to be extracted based on the primary key value assigned to each classmate in the Names table. I might have used a composite key from names but for the fact that we have two class members with the exact same first, middle and last names and several who wind up with identical names when using married names.

Thanks again for your help.

Originally Posted by BobH - TAN

What's wrong with using the primary_key field name? I defined it because I'm looking ahead to having several more tables before I'm done, with data in each to be extracted based on the primary key value assigned to each classmate in the Names table. I might have used a composite key from names but for the fact that we have two class members with the exact same first, middle and last names and several who...

I understand why you want to use the primary key, but naming the primary key - 'primary key' can cause problems for you in the future. Think of it sort of along the same lines of using any language specific syntax as a variable name. In this case, primary_key is

Anyway - Sorry for going off topic a bit. I wanted to look at your table creation syntax to see for certain how you have it setup. For example, you have your primary key to auto increment, which means your data will automatically be assigned a unique incremented value for the first field, so you wouldn't need your record numbers unless you have some 'offline' relation to using them.

Did you know you can dump your table structure in PAM for us to see?

1) Open up PAM and click on the database name (then STRUCTURE if needed) you are using.

2) Then click export.

3) Select all the fields for export. Unclick the data tick box. We don't need the data since there is none anyway.

4) Click Go. You should get your table creation syntax.

Let me know how it goes -

Cory

Hi Cory!

Can't thank you enough for all the help!

I see your point about the key field name and reserved words. Guess I'm still too new to spot the keywords I'm using. Is "key" a better name? Or "index"? Would appreciate any pointers here.

I didn't know about the PAM structure export function (see, I AM a very new noob!) until now.

Here's the result:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: 10.0.11.64
-- Generation Time: May 04, 2006 at 02:22 PM
-- Server version: 4.0.24
-- PHP Version: 4.3.2
--
-- Database: `broughton`
--

-- --------------------------------------------------------

--
-- Table structure for table `Names`
--

CREATE TABLE `Names` (
`primary_key` tinyint(3) unsigned zerofill NOT NULL auto_increment,
`firstname` varchar(15) NOT NULL default '',
`middle` varchar(15) default NULL,
`lastname` varchar(20) NOT NULL default '',
`suffix` varchar(5) default NULL,
`marriedname` varchar(20) default NULL,
`sex_marital_status` char(1) default 'F',
UNIQUE KEY `primary_key` (`primary_key`)
) TYPE=MyISAM COMMENT='Broughton HS Class of 1960 Members' AUTO_INCREMENT=2 ;

--
-- Dumping data for table `Names`
--

Bob,

I just renamed it student_id - seems logical and easier. Do that to yours and then make sure it's still the primary key.

I did get it to work - I think you just may have left out the fields terminated by the tab - which is \t

Try this first command below if using command line or follow the next steps to import via PHPMyAdmin. You'll need to put a tab between the first last and middle names if you haven't already, and all corresponding fields. I didn't and ended up with the full name in the first name on my first attempt.


LOAD DATA LOCAL INFILE 'your file path' INTO TABLE `Names` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'

OR

In PAM, click on SQL. Then near the bottom you should see a link that says something like this "Insert Data From Text File.... etc....". Click that link.

Then fill the required areas :

Fields terminated by will be : \t
Fields Enclosed by will be : nothing - leave empty
Fields Escaped by will be : nothing - leave empty
Lines terminated by will be : \r\n

Then click submit - you should be all set. Let me know how it goes again -

Cory

Hi Cory!

I'm still having trouble. Is there something wrong with this syntax?

LOAD DATA LOCAL INFILE 'C:/names2.txt' INTO TABLE Names FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';

This is copied from the SQL panel in PAM. It's my understanding that the backslash is an escape character in MySQL and that the forward slash is substituted in its place; however, nothing happens when I submit the command string for execution (click the "GO" button in PAM). No errors; no records loaded to the table. Do you think I should delete the table and redefine it? I can't see what I'm doing wrong.

As a point of information, I thought that the tab character was a default field delimiter and that declaration was not required except when another field separation value, such as a comma, was used. Am I missing something more?

Thanks again for all your kind help.

Did you try the PAM interface using the second method and uploading the file? I was able to get it to work.

You are correct, the default should be a tab, but for good measure, it was worth a shot.

If you want and you have no data that you are worried about sharing, you can shoot me the file, and I'll clean it up, create a DB for it, and give you the dump with the data and structure already loaded. Then you can just input the whole thing via PAM. Let me know if this is an option for you - sorry I couldn't be more help.

Cory

Hi Cory!

Yes, I tried the other approach and it didn't work. Again no error message.

Did you find anything wrong with the syntax of my LOAD DATA statement?

Nothing that I can see right away - no. Hmmm. Sending you a PM.