morrism35 Posted November 15, 2015 Share Posted November 15, 2015 My table in my database has two columns: Year Name My text file has data for the year and name: 1884 Pig 2004 Dog My code to load my year and data into my database columns are: load data infile 'path to file' into table zodiacyears fields terminated by ' '; This is not working for me. I can't find much information on using this. it looks pretty straightforward by its not working. I'm getting either null values, no data, or only the name column. Thank you. Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/ Share on other sites More sharing options...
ginerjm Posted November 15, 2015 Share Posted November 15, 2015 Are you checking for a MySQL error message? How are your table fields defined? Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526428 Share on other sites More sharing options...
morrism35 Posted November 15, 2015 Author Share Posted November 15, 2015 no error messages year smallint(10) name varchar(10) Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526429 Share on other sites More sharing options...
Barand Posted November 15, 2015 Share Posted November 15, 2015 Make sure your "path_to_file" is the full system path to the file on your server Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526431 Share on other sites More sharing options...
morrism35 Posted November 15, 2015 Author Share Posted November 15, 2015 its picking up the file but only giving me the first column which would be the years Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526432 Share on other sites More sharing options...
ginerjm Posted November 15, 2015 Share Posted November 15, 2015 When one says "terminated by (space)" what will it do at the last field in the line which does not have a space at its end? Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526433 Share on other sites More sharing options...
Barand Posted November 15, 2015 Share Posted November 15, 2015 This worked for me Data (zodiac.txt) 1884 Pig 2004 Dog 2005 Rat CREATE TABLE zodiacyears ( year smallint primary key, name varchar(10) ); load data infile 'C:/inetpub/wwwroot/. . ./zodiac.txt' into table zodiacyears fields terminated by ' '; mysql> select * from zodiacyears; +------+------+ | year | name | +------+------+ | 1884 | Pig | | 2004 | Dog | | 2005 | Rat | +------+------+ Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526435 Share on other sites More sharing options...
morrism35 Posted November 15, 2015 Author Share Posted November 15, 2015 i've ran that code over and over. let me investigate again Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526440 Share on other sites More sharing options...
morrism35 Posted November 15, 2015 Author Share Posted November 15, 2015 This is what I have done so far but the output is still missing characters 8978 dfdafdff 6565 aaaaaaa 5656 ddddd create table zodiac( year smallint, name varchar(10) ); load data infile 'C:/xampp/mysql/data/chinese_zodiac/testText.txt' into table zodiac fields terminated by ' '; | 5656 | ddddd | |565 | aaaaaaa |8978 | dfdafdff Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526444 Share on other sites More sharing options...
ginerjm Posted November 15, 2015 Share Posted November 15, 2015 Assuming that you posted your intput followed by the code followed by some output you generated - this doesn't make sense at all. The output is not in the same order as the input. The output is different than the input. You are not showing us something. How about doing it again and breaking it up so it makes sense to us ordinary people? And show all the code involved! Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526449 Share on other sites More sharing options...
morrism35 Posted November 16, 2015 Author Share Posted November 16, 2015 i just re-checked my text file and output and that is what it is outputting. Its exactly as written. Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526454 Share on other sites More sharing options...
benanamen Posted November 16, 2015 Share Posted November 16, 2015 (edited) Is your data separated by tabs or spaces? Edited November 16, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526455 Share on other sites More sharing options...
morrism35 Posted November 16, 2015 Author Share Posted November 16, 2015 spaces Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526456 Share on other sites More sharing options...
morrism35 Posted November 16, 2015 Author Share Posted November 16, 2015 actually i created another table and used the same text file. that was just a test i have another one. it must be in my query somewere. Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526457 Share on other sites More sharing options...
morrism35 Posted November 16, 2015 Author Share Posted November 16, 2015 sorry after i created a new table and loaded those three lines in it worked correctly Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526458 Share on other sites More sharing options...
morrism35 Posted November 16, 2015 Author Share Posted November 16, 2015 below is my assignment: input, then table code, then output. If I just query the year all the years show correctly, if I query the name then I get missing values for the name. 1900 Rat 1912 Rat 1924 Rat 1936 Rat 1948 Rat 1960 Rat 1972 Rat 1984 Rat 1996 Rat 2008 Rat 2020 Rat 1901 Ox 1913 Ox 1925 Ox 1937 Ox 1949 Ox 1961 Ox 1973 Ox 1985 Ox 1997 Ox 2009 Ox 2021 Ox create table zodiac1( year smallint, name varchar(10) ); load data infile 'C:/xampp/mysql/data/chinese_zodiac/testText.txt' into table zodiac fields terminated by ' '; mysql> select * from zodiac1; +------+----------+ | year | name | +------+----------+ | | Rat | | Rat | | Rat | | Rat | | Rat | | Rat | | Rat | | Rat | | Rat | | Rat | | Rat || Ox || Ox || Ox || Ox || Ox || Ox || Ox || Ox || Ox || Ox || Ox Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526459 Share on other sites More sharing options...
benanamen Posted November 16, 2015 Share Posted November 16, 2015 (edited) I am not having any problems. first you load into zodiacyears, then zodiac but are selecting from zodiac1 You are all over the place with tables. Delete all but ONE table, empty it and load fresh Then use a mysql gui like phpmyadmin and look at your table as soon as you do the import and make sure the data is getting imported. This is so not a complicated situation. Edited November 16, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526460 Share on other sites More sharing options...
morrism35 Posted November 16, 2015 Author Share Posted November 16, 2015 it was just a typo and my code was correct. My command line isn't displaying it properly for some reason. i do have phpmyadmin and I checked my code in the gui and it was right the whole time. I wasted all this time when I was doing it right in the get go. Same code that I posted above but reading the output in the gui as opposed to the command line(900 just a pasting mistake) 900 Rat 1912 Rat 1924 Rat 1936 Rat 1948 Rat 1960 Rat 1972 Rat 1984 Rat 1996 Rat 2008 Rat 2020 Rat 1901 Ox 1913 Ox 1925 Ox 1937 Ox 1949 Ox 1961 Ox 1973 Ox 1985 Ox 1997 Ox 2009 Ox 2021 Ox Quote Link to comment https://forums.phpfreaks.com/topic/299485-load-a-text-file-into-table-mysql/#findComment-1526464 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.