Professional Web Applications Themes

import Text file in MySQL table - PHP Programming

I've a text file I've downloaded wich contains the datas I must use to fill a table. (postal code and cities). Here is the structure of my text file: 1000 10 1753 00 Matran Matran FR 1003 20 1754 00 Rosé Rosé FR 1004 10 1756 00 Onnens FR Onnens FR FR 1005 10 1757 00 Noréaz Noréaz FR 1007 10 1690 00 Villaz-St-Pierre Villaz-St-Pierre FR 1009 10 1762 00 Givisiez Givisiez FR 1011 10 1772 00 Grolley Grolley FR 1014 10 1773 00 Léchelles Léchelles FR 1015 10 1774 00 Cousset Cousset FR 1016 20 1775 00 Mannens-Grandsivaz Mannens-Grandsivaz ...

  1. #1

    Default import Text file in MySQL table

    I've a text file I've downloaded wich contains the datas I must use to fill
    a table. (postal code and cities).

    Here is the structure of my text file:
    1000 10 1753 00 Matran Matran FR
    1003 20 1754 00 Rosé Rosé FR
    1004 10 1756 00 Onnens FR Onnens FR FR
    1005 10 1757 00 Noréaz Noréaz FR
    1007 10 1690 00 Villaz-St-Pierre Villaz-St-Pierre FR
    1009 10 1762 00 Givisiez Givisiez FR
    1011 10 1772 00 Grolley Grolley FR
    1014 10 1773 00 Léchelles Léchelles FR
    1015 10 1774 00 Cousset Cousset FR
    1016 20 1775 00 Mannens-Grandsivaz Mannens-Grandsivaz FR
    1017 20 1776 00 Montagny-la-Ville Montagny-la-Ville FR
    1020 20 1795 00 Courlevon Courlevon FR
    1021 10 1796 00 Courgevaux Courgevaux FR
    1022 20 1797 00 Münchenwiler Münchenwiler BE
    1023 20 1794 00 Salvenach Salvenach FR
    (separation are done by TAB)...

    I need to insert every record in my table City, but only the 3rd and the 6th
    column:
    destination's Table structure is:
    NPA (postal code)
    City (name of the city)
    for having:
    1753 Matran
    1754 Rosé
    1756 Onnens FR
    1757 Noréaz.....

    How can I do so (I've no idea on how to do so). I've installed PhpMyAdmin,
    but can't find a way to do so using it.

    Newbie level.

    BoB


    Bob Bedford Guest

  2. #2

    Default Re: import Text file in MySQL table

    This should fix your problem if you speak php =)

    [url]http://de3.php.net/fgetcsv[/url]


    Lars Raube Guest

  3. #3

    Default Re: import Text file in MySQL table

    Thanks for the answer, but somewhere I've seen it can be done by a simple
    query. As I'm testing my site, sometimes I must reinitialize my db, so it
    could be interesting to do so in one line.

    Does somebody know how to do so in PHPMyAdmin ?

    "Lars Raube" <rauberzr.de> a écrit dans le message de
    news:br77cr$vj4$06$1news.t-online.com...
    > This should fix your problem if you speak php =)
    >
    > [url]http://de3.php.net/fgetcsv[/url]
    >
    >

    Bob Bedford Guest

  4. #4

    Default Re: import Text file in MySQL table

    you can import a hole csv file, i am not sure if you can import only a part
    of a file. But then it´s not php problem, it´s a mysql problem, so check
    mysql.com and read the doentation...



    "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
    news:3fd72fa8$0$785$5402220fnews.sunrise.ch...
    > Thanks for the answer, but somewhere I've seen it can be done by a simple
    > query. As I'm testing my site, sometimes I must reinitialize my db, so it
    > could be interesting to do so in one line.
    >
    > Does somebody know how to do so in PHPMyAdmin ?
    >
    > "Lars Raube" <rauberzr.de> a écrit dans le message de
    > news:br77cr$vj4$06$1news.t-online.com...
    > > This should fix your problem if you speak php =)
    > >
    > > [url]http://de3.php.net/fgetcsv[/url]
    > >
    > >
    >
    >

    Lars Raube Guest

  5. #5

    Default Re: import Text file in MySQL table

    I've tried with
    LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\r\n'('col3', 'col7')

    but I can't get it to work:

    I've added the first line "col1 \t col2 \t col3 \t ...."

    And it's where I'm having problems. (error on SQL syntax)

    "Lars Raube" <rauberzr.de> a écrit dans le message de
    news:br7b7j$kuc$01$1news.t-online.com...
    > you can import a hole csv file, i am not sure if you can import only a
    part
    > of a file. But then it´s not php problem, it´s a mysql problem, so check
    > mysql.com and read the doentation...
    >
    >
    >
    > "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
    > news:3fd72fa8$0$785$5402220fnews.sunrise.ch...
    > > Thanks for the answer, but somewhere I've seen it can be done by a
    simple
    > > query. As I'm testing my site, sometimes I must reinitialize my db, so
    it
    > > could be interesting to do so in one line.
    > >
    > > Does somebody know how to do so in PHPMyAdmin ?
    > >
    > > "Lars Raube" <rauberzr.de> a écrit dans le message de
    > > news:br77cr$vj4$06$1news.t-online.com...
    > > > This should fix your problem if you speak php =)
    > > >
    > > > [url]http://de3.php.net/fgetcsv[/url]
    > > >
    > > >
    > >
    > >
    >
    >

    Bob Bedford Guest

  6. #6

    Default Re: import Text file in MySQL table

    "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
    news:3fd738c3$0$781$5402220fnews.sunrise.ch...
    > I've tried with
    > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY
    '\t'
    > LINES TERMINATED BY '\r\n'('col3', 'col7')
    try it with...

    LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\r\n'(col3, col7)


    Lars Raube Guest

  7. #7

    Default Re: import Text file in MySQL table

    Unknown column 'col3' in 'field list'

    "Lars Raube" <rauberzr.de> a écrit dans le message de
    news:br7er4$9nq$06$1news.t-online.com...
    > "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
    > news:3fd738c3$0$781$5402220fnews.sunrise.ch...
    > > I've tried with
    > > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY
    > '\t'
    > > LINES TERMINATED BY '\r\n'('col3', 'col7')
    >
    > try it with...
    >
    > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY
    '\t'
    > LINES TERMINATED BY '\r\n'(col3, col7)
    >
    >

    Bob Bedford Guest

  8. #8

    Default Re: import Text file in MySQL table

    give it a try in the newsgroup mailing.database.mysql


    "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> wrote in message
    news:3fd748bc$0$770$5402220fnews.sunrise.ch...
    > Unknown column 'col3' in 'field list'
    >
    > "Lars Raube" <rauberzr.de> a écrit dans le message de
    > news:br7er4$9nq$06$1news.t-online.com...
    > > "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> schrieb im
    Newsbeitrag
    > > news:3fd738c3$0$781$5402220fnews.sunrise.ch...
    > > > I've tried with
    > > > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY
    > > '\t'
    > > > LINES TERMINATED BY '\r\n'('col3', 'col7')
    > >
    > > try it with...
    > >
    > > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY
    > '\t'
    > > LINES TERMINATED BY '\r\n'(col3, col7)
    > >
    > >
    >
    >

    Sjoerd Mulder Guest

  9. #9

    Default Re: import Text file in MySQL table

    "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> wrote:
    > "Lars Raube" <rauberzr.de> a écrit:
    >> "Bob Bedford" <bedford1YouKnowWhatToDohotmail.com> schrieb:
    >> > I've tried with
    >> > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED
    >> > BY
    >> '\t'
    >> > LINES TERMINATED BY '\r\n'('col3', 'col7')
    >>
    >> try it with...
    >>
    >> LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED
    >> BY
    > '\t'
    >> LINES TERMINATED BY '\r\n'(col3, col7)
    >
    > Unknown column 'col3' in 'field list'
    Hi Bob,

    The list of columns in the LOAD DATA syntax refers to fields in the
    table ('city'), not to fields in your data file. There is no MySQL
    command to achieve what you want - you have to extract the relevant
    fields first. You could do this by using fgetcsv() to load the file in a
    PHP script and write a new file, containing only the fields you want to
    load into your table. Alternatively you could write a shell script which
    does the same.

    HTH;
    JOn
    Jon Kraft Guest

Similar Threads

  1. Question Sql Sript to Import Data from a XML file into a oracle table
    By Denzil in forum Brainstorming Area
    Replies: 0
    Last Post: August 11th, 10:00 AM
  2. Replies: 0
    Last Post: June 16th, 11:22 PM
  3. Import text file into database table
    By Marty in forum ASP Database
    Replies: 1
    Last Post: October 27th, 08:09 PM
  4. Import MYSQL table information from .sql file
    By Brad Brevet in forum PHP Development
    Replies: 16
    Last Post: October 14th, 01:03 PM
  5. Import .sql file into MySQL DB ?
    By Nikolai Chuvakhin in forum PHP Programming
    Replies: 0
    Last Post: August 13th, 06:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139