Need Help with LOAD DATA LOCAL INFILE in MySQL
Posted: Sat May 06, 2006 5:24 pm
I'm trying to load a new, empty table from a tab separated fields file created in MSWord and saved as plain text with carriage return and line feed (CR/LF) after each line of data. Where there is no data in a field '\N' is placed to indicate a null value and is properly followed by either a tab character or a CR/LF The file is located in the root directory of my C: drive and is named 'names2.txt'. My database is hosted on GoDaddy which means that I can access it only through the PHPMyAdmn (PAM) interface. The table is named 'Names'.
The statement executes without returning any error, but the table is not updated. I have scoured my data looking for anomalies and can find none (doesn't mean there aren't any). I have also tried using comma separated values (CSV) with appropriate change to the LOAD statement. I changed the backslash in the path/filename statement to a forward slash based on reading the MySQL forum. I also tried using a double backslash (first one escapes the second). I get no difference in result; however, if I make a typo in the filename field or otherwise create a syntax error, I get a MySQL error. I also tried to run it without the final "LINES TERMINATED BY '\r\n'' clause; same result - no errors, no records updated.
What am I doing wrong? I've had help from someone else on a database forum and he was able to upload the data using the exact same file and MySQL statements. He offered to load it for me and send it to me as a backup file to use to replace the existing file, but I need to learn what I'm doing wrong as much as I need to load the data. Can anyone help? I'd be much obliged for suggestions about other things to try.
Here is the statement I put in the SQL panel of PAM:
The statement executes without returning any error, but the table is not updated. I have scoured my data looking for anomalies and can find none (doesn't mean there aren't any). I have also tried using comma separated values (CSV) with appropriate change to the LOAD statement. I changed the backslash in the path/filename statement to a forward slash based on reading the MySQL forum. I also tried using a double backslash (first one escapes the second). I get no difference in result; however, if I make a typo in the filename field or otherwise create a syntax error, I get a MySQL error. I also tried to run it without the final "LINES TERMINATED BY '\r\n'' clause; same result - no errors, no records updated.
What am I doing wrong? I've had help from someone else on a database forum and he was able to upload the data using the exact same file and MySQL statements. He offered to load it for me and send it to me as a backup file to use to replace the existing file, but I need to learn what I'm doing wrong as much as I need to load the data. Can anyone help? I'd be much obliged for suggestions about other things to try.
Here is the statement I put in the SQL panel of PAM:
Here is the table definition copied from the PAM EXPORT function:LOAD DATA LOCAL INFILE 'C:/names2.txt' INTO TABLE Names FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';
Here are the first few records from the input file (don't know why the CR/LF don't appear or don't seem to work in a 'copy' function):-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: 10.0.11.64
-- Generation Time: May 06, 2006 at 03:10 PM
-- Server version: 4.0.24
-- PHP Version: 4.3.2
--
-- Database: `broughton`
--
-- --------------------------------------------------------
--
-- Table structure for table `Names`
--
CREATE TABLE `Names` (
`student_id` 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,
`male_female` char(1) default 'F',
UNIQUE KEY `primary_key` (`student_id`)
) TYPE=MyISAM COMMENT='Broughton HS Class of 1960 Members' AUTO_INCREMENT=2 ;
--
-- Dumping data for table `Names`
--
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 010 Nancy Ruth Bailey \N \N \N 011 Robert Lander Baird \N \N M 012 Benjamin Whiteley Baker \N \N M 013 Walter Wray Baker \N \N M 014 Frances Lewis Barbour \N \N \N 015 Max O \'Brien Barbour \N \N M 016 James Grover Barefoot \N \N M 017 Rebecca Sue Barnes \N \N \N 018 Sarah Hill Barnhardt \N \N \N 019 Thomas Jefferson Bashford \N \N M 020 John Alsgood Bassler \N \N M 021 Donna Rae Beaird \N \N \N 022 Baker Guy Beattie Jr \N M 023 George Williams Bensch \N \N M 024 Clifton Linwood Benson Jr \N M 025 William Allen Benton \N \N M