Ask a Question related to PHP Programming, Design and Development.
-
Bob Bedford #1
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
-
MySQL import gets SQL errors because of text in fields
I'm using PHPMyAdmin on an Apache2Triad install (latest version; 5.x.x, which installs PHP5 and PHPMyAdmin5 as well). In Access, I exported a table... -
Import text file into database table
I need to build a web page that allows the user to select a text file and click a button to import the file into a database table. Does anyone... -
Import MYSQL table information from .sql file
If I have created a .sql file in my notepad named tables.sql and laved it in my inetpub folder what commands do I have to enter at the mySQL command... -
Import .sql file into MySQL DB ?
Jerry T <Jerry@ nothanks.com> wrote in message news:<drgkjv03hrpc69p2tt5v0pjmfdspqlnesf@4ax.com>... phpMyAdmin has a functionality to execute... -
Import text file
l am using a dts package to import a text file with delimited colums the problem is that the dts package fails to convert a number value like 12,00... -
Lars Raube #2 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
-
Bob Bedford #3 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" <raube@rzr.de> a écrit dans le message de
news:br77cr$vj4$06$1@news.t-online.com...> This should fix your problem if you speak php =)
>
> [url]http://de3.php.net/fgetcsv[/url]
>
>
Bob Bedford Guest
-
Lars Raube #4 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 documentation...
"Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
news:3fd72fa8$0$785$5402220f@news.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" <raube@rzr.de> a écrit dans le message de
> news:br77cr$vj4$06$1@news.t-online.com...>> > This should fix your problem if you speak php =)
> >
> > [url]http://de3.php.net/fgetcsv[/url]
> >
> >
>
Lars Raube Guest
-
Bob Bedford #5 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" <raube@rzr.de> a écrit dans le message de
news:br7b7j$kuc$01$1@news.t-online.com...part> you can import a hole csv file, i am not sure if you can import only asimple> of a file. But then it´s not php problem, it´s a mysql problem, so check
> mysql.com and read the documentation...
>
>
>
> "Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
> news:3fd72fa8$0$785$5402220f@news.sunrise.ch...> > Thanks for the answer, but somewhere I've seen it can be done by ait> > query. As I'm testing my site, sometimes I must reinitialize my db, so>> > could be interesting to do so in one line.
> >
> > Does somebody know how to do so in PHPMyAdmin ?
> >
> > "Lars Raube" <raube@rzr.de> a écrit dans le message de
> > news:br77cr$vj4$06$1@news.t-online.com...> >> > > This should fix your problem if you speak php =)
> > >
> > > [url]http://de3.php.net/fgetcsv[/url]
> > >
> > >
> >
>
Bob Bedford Guest
-
Lars Raube #6 Re: import Text file in MySQL table
"Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
news:3fd738c3$0$781$5402220f@news.sunrise.ch...'\t'> I've tried with
> LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BYtry it with...> LINES TERMINATED BY '\r\n'('col3', 'col7')
LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'(col3, col7)
Lars Raube Guest
-
Bob Bedford #7 Re: import Text file in MySQL table
Unknown column 'col3' in 'field list'
"Lars Raube" <raube@rzr.de> a écrit dans le message de
news:br7er4$9nq$06$1@news.t-online.com...'\t'> "Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> schrieb im Newsbeitrag
> news:3fd738c3$0$781$5402220f@news.sunrise.ch...> '\t'> > I've tried with
> > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY>> > LINES TERMINATED BY '\r\n'('col3', 'col7')
> try it with...
>
> LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY> LINES TERMINATED BY '\r\n'(col3, col7)
>
>
Bob Bedford Guest
-
Sjoerd Mulder #8 Re: import Text file in MySQL table
give it a try in the newsgroup mailing.database.mysql
"Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> wrote in message
news:3fd748bc$0$770$5402220f@news.sunrise.ch...Newsbeitrag> Unknown column 'col3' in 'field list'
>
> "Lars Raube" <raube@rzr.de> a écrit dans le message de
> news:br7er4$9nq$06$1@news.t-online.com...> > "Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> schrieb im> '\t'> > news:3fd738c3$0$781$5402220f@news.sunrise.ch...> > '\t'> > > I've tried with
> > > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY> >> > > LINES TERMINATED BY '\r\n'('col3', 'col7')
> > try it with...
> >
> > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED BY>> > LINES TERMINATED BY '\r\n'(col3, col7)
> >
> >
>
Sjoerd Mulder Guest
-
Jon Kraft #9 Re: import Text file in MySQL table
"Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> wrote:
Hi Bob,> "Lars Raube" <raube@rzr.de> a écrit:> '\t'>> "Bob Bedford" <bedford1@YouKnowWhatToDohotmail.com> schrieb:>> '\t'>> > I've tried with
>> > LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED
>> > BY>>>> > LINES TERMINATED BY '\r\n'('col3', 'col7')
>> try it with...
>>
>> LOAD DATA INFILE 'C:\\temp.txt' INTO TABLE `city` FIELDS TERMINATED
>> BY>>> LINES TERMINATED BY '\r\n'(col3, col7)
> Unknown column 'col3' in 'field list'
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
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
