July 11th, 2000, 10:11 AM
I need some serious help and fast. My Web Design company took on a job, that we assumed we knew how to do, only it turns out that I have had some big problems importing data into MySQL. The client has sent me the text files, but I can't get them to go into my database. This is what I am using:
LOAD DATA INFILE 'new_contacts.txt' REPLACE INTO TABLE GTContac FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY 'n'
It loads the file fine, but for some reason, the table ends up with only one entry. That entry is basically fine with the exception of the first field, which gets cut off for some reason. ALso, the last field retains its quotation marks for some reason. An example of the entries in the text file can be found below:
"16103","Bridget","Salantri","Hist Horticulturist","Friends of Washington Crossing Historic Park","215 493 4076","(215)493-4820","email@example.com","Friends of Washington Crossing Historic Park","PA Hist & Museum Comm, 1112 River Rd","Washington Crossing","PA","18977","USA"
The listing of what each field is, just in case it might be helpful is below.
recno - tinyint(6)
first - varchar(40)
last - varchar(40)
title - varchar(40)
dept - varchar(40)
combtel - varchar(20)
combfax - varchar(20)
combemail - varchar(60)
ctcmlgorg - varchar(60)
ctcmlgadd - varchar(60)
ctcmlgcity - varchar(40)
ctcmlgst - varchar(15)
ctcmlgzip - varchar(15)
ctcmlgctry - varchar(30)
I would really appreciate any help that anyone could provide. If you need more information let me know. Feel free to email me as well.
July 11th, 2000, 12:39 PM
One more thing that I forgot to add. I have gone through the MySQL reference, etc, but none of that is helping me so far.
July 11th, 2000, 12:49 PM
I can't help you with why the quotes aren't coming off the last field, but the reason the first field is dropped is because you've defined the first field as a TINYINT which can hold the values of -128 thru 127 (or, if declared UNSIGNED, 0 thru 255) and the data you are trying to input is outside of that range.
The possible reason the quotes aren't dropping may be related to the reason you are only getting one record. The line may be terminated by a carriage return as well as the new line. (/r/n)