Jump to content

load a text file into table mysql


morrism35

Recommended Posts

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.

 

 

                                                                                 

 

Link to comment
Share on other sites

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  |
+------+------+

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

   



 

 


 

Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.