Re: Importing data from excel sheet
Hi, sam You can try to export table to file from Excel in CSV format. And then import data from this file to mysql. Something like this should help you: LOAD DATA INFILE 'yourtabledata.txt' INTO TABLE yourtable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; sam rumaizan wrote: I have created table in mysql with 12 fields Field1Field2 Field3 Field4 ……… Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends. -- Andrew Dashin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data from excel sheet
I have created table in mysql with 12 fields Field1Field2 Field3 Field4 Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
Handling of NULL values when importing data from SQL Server BCP files
In the category of terrible, horrible, no good, very bad (but at least documented) software behavior, I bumped into this today: http://bugs.mysql.com/bug.php?id=14770 where the LOAD DATA INFILE command does not respect the default value of a column if no value is supplied in the file. Instead, it assigns zero to numeric columns, empty string to character columns, etc., per http://dev.mysql.com/doc/refman/5.0/en/load-data.html This is awful! I mean, it's documented behavior and all, but it's still just bloody awful! Has anyone else found a graceful solution to this problem? The thing is, my data files may have "real" zeros in numeric columns, whereas NULL of course is used to indicate an unknown value. When I migrate this application from SQL Server to MySQL in the next couple of weeks, suddenly I'll have a lot of zeros where I previously had NULLs. Dan
Re: Importing data from MS Access
I found SQLyog Enterprise (www.webyog.com) to be the best when importing data from ODBC to MySQL. Karam --- Peter Brawley <[EMAIL PROTECTED]> wrote: > Using a data modelling tool (like Dezign from > Datanamic) or a scripting > tool (like DbScripter), all you need do, I think, is > load the Access > database into the tool, configure the tool to create > an export script > for MySQL (say, using InnoDB to enable FKs), touch > up as desired, and > generate the script. > > PB > > Paun wrote: > > >I use MySQL-Front 2.5 (freeware) for importing data > from MSAccess databaase > >in MySQL 4.1.7. > > > >Of course, there is no possibility to import > "foreign keys" from MSAccess. > > > >Question: If I read properly documentation in MYSql > is possible to use only > >one "foreing key" per table, and fields with > foreign key must be on ordered > >on the same way in booth tables, and only in InnoDB > type of tables. > > > > > > > > > >I have tables with many foreign key connectons > (e.g. members / streets, > >members/occupations, members/state of membership) > between tables, and it was > >easier way to keep data in database in proper order > with less programing. Is > >that is not possible in MYSQL? > > > >Of course, I am very new in MYSQL. > > > >Thanks. > >-- > >No virus found in this outgoing message. > >Checked by AVG Anti-Virus. > >Version: 7.0.296 / Virus Database: 265.6.9 - > Release Date: 1/6/2005 > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing data from MS Access
Using a data modelling tool (like Dezign from Datanamic) or a scripting tool (like DbScripter), all you need do, I think, is load the Access database into the tool, configure the tool to create an export script for MySQL (say, using InnoDB to enable FKs), touch up as desired, and generate the script. PB Paun wrote: I use MySQL-Front 2.5 (freeware) for importing data from MSAccess databaase in MySQL 4.1.7. Of course, there is no possibility to import "foreign keys" from MSAccess. Question: If I read properly documentation in MYSql is possible to use only one "foreing key" per table, and fields with foreign key must be on ordered on the same way in booth tables, and only in InnoDB type of tables. I have tables with many foreign key connectons (e.g. members / streets, members/occupations, members/state of membership) between tables, and it was easier way to keep data in database in proper order with less programing. Is that is not possible in MYSQL? Of course, I am very new in MYSQL. Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.9 - Release Date: 1/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing data from MS Access
Its my understanding you need InnoDB table types, but I do not know of the single foreign key per table. I have a DB with multiple foreign keys per table and its seems to work fine. Bob -Original Message- From: Paun [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 11, 2005 5:19 PM To: mysql@lists.mysql.com Subject: Importing data from MS Access I use MySQL-Front 2.5 (freeware) for importing data from MSAccess databaase in MySQL 4.1.7. Of course, there is no possibility to import "foreign keys" from MSAccess. Question: If I read properly documentation in MYSql is possible to use only one "foreing key" per table, and fields with foreign key must be on ordered on the same way in booth tables, and only in InnoDB type of tables. I have tables with many foreign key connectons (e.g. members / streets, members/occupations, members/state of membership) between tables, and it was easier way to keep data in database in proper order with less programing. Is that is not possible in MYSQL? Of course, I am very new in MYSQL. Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.9 - Release Date: 1/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data from MS Access
I use MySQL-Front 2.5 (freeware) for importing data from MSAccess databaase in MySQL 4.1.7. Of course, there is no possibility to import "foreign keys" from MSAccess. Question: If I read properly documentation in MYSql is possible to use only one "foreing key" per table, and fields with foreign key must be on ordered on the same way in booth tables, and only in InnoDB type of tables. I have tables with many foreign key connectons (e.g. members / streets, members/occupations, members/state of membership) between tables, and it was easier way to keep data in database in proper order with less programing. Is that is not possible in MYSQL? Of course, I am very new in MYSQL. Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.9 - Release Date: 1/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
One reason may be due to your ON condition. ON table2.ID=table2.ID_table1 You reference table 2 twice and I doubt that on the table2 table id equals id_table1 very often. Because table2 is LEFT JOINed to table1, you will only be able to update the rows that match your ON condition. Again, that would mean that you have nothing to update which would cause no changes to table2 so it would appear that the IF() is always false. You can LEFT JOIN only those records you want to change. That way you can avoid the IF() and not worry about updating any other records. I think it should look something like this: UPDATE table1 LEFT JOIN table2 ON table1.ID=table2.ID_table1 AND table1.condition=7 SET table2.value=table2.value-1, table1.condition=8 WHERE table1.ID=$id Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 04:22:34 AM: > Hi, > > An SQL-statement that is conceptually equal to the following caused some > astonishment. > > UPDATE table1 > LEFT JOIN table2 ON table2.ID=table2.ID_table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID=$id > > My problem is the IF condition, which alway evaluates to false no matter > what conditions value was before the query. My explanation is, that > MySQL first updates table1 and uses the changed values to update table2. > > In the meantime I solved it on application-level, but up to now I > consindered a multi table update an atomic operation. > > Is this behavior correct? > > Greetings > Kai > > -- > This signature is left as an exercise for the reader. > Unsatz des Jahres: > $POLITIKER ruft $PARTEI zur Geschlossenheit. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: importing data
OK, now I really feel stupid. It helps to change the ownership of the files to mysql:mysqlduh. Curtis Curtis Maurand wrote: I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Hi, An SQL-statement that is conceptually equal to the following caused some astonishment. UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id My problem is the IF condition, which alway evaluates to false no matter what conditions value was before the query. My explanation is, that MySQL first updates table1 and uses the changed values to update table2. In the meantime I solved it on application-level, but up to now I consindered a multi table update an atomic operation. Is this behavior correct? Greetings Kai -- This signature is left as an exercise for the reader. Unsatz des Jahres: $POLITIKER ruft $PARTEI zur Geschlossenheit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data
Hi, I have done an incredibly stupid thing. I have a server that suffered a bit of a failure. It wasn't a hard failure, but it was enough of one to have to rebuild the server from scratch. I was and am running mysql 4.0.22 on Gentoo with a 2.6 kernel. It rocks. However, The dumb thing that I did was fail to perform a mysqldump -a on the machine before I finished killing it. I rebuilt mysql from scratch. I then copied over the my.cnf files and I copied over the old /var/lib/mysql directory in tact. mysql wouldn't start at that point. I then deleted all the files in /var/lib/mysql and ran mysql_install_db. mysql starts automatically. If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem importing data
At the end of your csv file, are you including ,\n ?
If you are using mysql on windows and LOAD DATA IN FILE you need to
include unix line breaks at the end of each line, else the data will
be loaded wacky...so if each line looks like this:
"","value1","value2","value3","value4"
change it to this: (on each line, search replace maybe?)
"","value1","value2","value3","value4",\n
phpninja
-Original Message-
From: Leonardo Javier Belén [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 09, 2004 10:02 AM
To: [EMAIL PROTECTED]
Subject: Problem importing data
Hi all,
I am experiencing a fairly bizarre problem: in MySQL 4.1.7 (Windoze
version), when importing data from a file with semicolon-separated data, I
get random default values for the datetime fields. This turns the data
obviosly unusable.
The same import on the same machine but with the 4.0.12 version of the
server is totally bug free.
Does anyone have experience on this sort of problems?
Leonardo J. Belén.
mysql - query - sql
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem importing data
Hi all, I am experiencing a fairly bizarre problem: in MySQL 4.1.7 (Windoze version), when importing data from a file with semicolon-separated data, I get random default values for the datetime fields. This turns the data obviosly unusable. The same import on the same machine but with the 4.0.12 version of the server is totally bug free. Does anyone have experience on this sort of problems? Leonardo J. Belén. mysql - query - sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing data from a file
I've used the 'load data infile' command to import data from a file and it worked well for my purposes. See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html for the full syntax. Rhino - Original Message - From: "Manuel J. Contreras Maya" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, October 25, 2004 1:59 PM Subject: Importing data from a file > Hello, > > I am new in mysql and I woul like to ask what is the best way to import > data from a file. > > Cheers, > > Manuel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing data from a file
I managed to do it using mysql> load data infile '/home/manuel/databases/import.txt' -> into table countries -> fields terminated by ';'; Query OK, 240 rows affected (0.01 sec) Records: 240 Deleted: 0 Skipped: 0 Warnings: 18 (I will check the warnings...) I guess my system do not have mysqlimport, [EMAIL PROTECTED] manuel]$ man mysqlimport No manual entry for mysqlimport Thanks a lot! Manuel Brian Abbott wrote: There's a tool called "mysqlimport". I recommend using that. Or, if you have to extract data from a larger set, you could write a program. But, mysqlimport is the easiest way. If you're on a UNIX machine, run 'man mysqlimport' for more information. Good luck, Brian -Original Message- From: Manuel J. Contreras Maya [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 10:00 AM To: [EMAIL PROTECTED] Subject: Importing data from a file Hello, I am new in mysql and I woul like to ask what is the best way to import data from a file. Cheers, Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing data from a file
There's a tool called "mysqlimport". I recommend using that. Or, if you have to extract data from a larger set, you could write a program. But, mysqlimport is the easiest way. If you're on a UNIX machine, run 'man mysqlimport' for more information. Good luck, Brian -Original Message- From: Manuel J. Contreras Maya [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 10:00 AM To: [EMAIL PROTECTED] Subject: Importing data from a file Hello, I am new in mysql and I woul like to ask what is the best way to import data from a file. Cheers, Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data from a file
Hello, I am new in mysql and I woul like to ask what is the best way to import data from a file. Cheers, Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data into mysql from oracle using a text file
In article <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> writes: > Thanks, it is working for the mentioned format. > Is there any method for importing directly the spooled file from oracle without > changing the file format into the required format like using tab and newline. I don't know Oracle, but maybe two named pipes might work: mkfifo pipe1 mkfifo pipe2 oracleexport >pipe1 & mysql db -e "load data infile 'pipe2'" & sed -e whatever pipe1 >pipe2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: importing data into mysql from oracle using a text file
Thanks, it is working for the mentioned format. Is there any method for importing directly the spooled file from oracle without changing the file format into the required format like using tab and newline. thanks, narasimha -Original Message- From: Andrey Hristov [mailto:[EMAIL PROTECTED] Sent: Thu 9/30/2004 2:01 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED] Subject: Re: importing data into mysql from oracle using a text file Hi, I did imported your data im my server but I had to do some changes to sample.txt. I have replaced in a text editor "\t" with empty string. is a real tab while \t is just a text. Additional change was to replace "\n" (which is text but not newline with empty string). The I did the following (before that I have created the table) : mysql> load data local infile "/home/andrey/Desktop/sample2.txt" into table ALARM FIELDS ESCAPED BY '\\'; Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql> show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1261 | Row 1 doesn't contain data for all columns | +-+--++ 1 row in set (0.00 sec) mysql> select * FROM ALARM; ++++--++--+---+--+-++ | ARRIVED| DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON | ++++--++--+---+--+-++ | 2004-09-27 | 2004-09-27 | alaram |0 | 0 | NULL | 0 | 2 | 1 | NULL | ++++--++--+---+--+-++ 1 row in set (0.02 sec) You can see the warning since the number of fields was less the needed. Or maybe you wanted by having \n to express NULL? Last thing to do over sample.text is to mark all places where NULL should appear with \N . Hope this helps, Andrey [EMAIL PROTECTED] wrote: > Tha sample lines are like this in alarm.txt > > ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM > EVENTID > - - - -- - - > - > REASON > > > > 27-SEP-04 27-SEP-04 alaram 0 0 N 0 2 > 1 > > > > > 27-SEP-04 27-SEP-04 MiTel0 0 N 0 2 > 2 > > > The above lines i am not able to insert into mysql. > > After changing the format as below(as in the sample.txt) i am able to insert into mysql but not correclty. Couls you please help me in this. > > > 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n > > thanks, > narasimha > Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: importing data into mysql from oracle using a text file
Hi, I did imported your data im my server but I had to do some changes to sample.txt. I have replaced in a text editor "\t" with empty string. is a real tab while \t is just a text. Additional change was to replace "\n" (which is text but not newline with empty string). The I did the following (before that I have created the table) : mysql> load data local infile "/home/andrey/Desktop/sample2.txt" into table ALARM FIELDS ESCAPED BY '\\'; Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql> show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1261 | Row 1 doesn't contain data for all columns | +-+--++ 1 row in set (0.00 sec) mysql> select * FROM ALARM; ++++--++--+---+--+-++ | ARRIVED| DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON | ++++--++--+---+--+-++ | 2004-09-27 | 2004-09-27 | alaram |0 | 0 | NULL | 0 | 2 | 1 | NULL | ++++--++--+---+--+-++ 1 row in set (0.02 sec) You can see the warning since the number of fields was less the needed. Or maybe you wanted by having \n to express NULL? Last thing to do over sample.text is to mark all places where NULL should appear with \N . Hope this helps, Andrey [EMAIL PROTECTED] wrote: Tha sample lines are like this in alarm.txt ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 02 1 27-SEP-04 27-SEP-04 MiTel0 0 N 02 2 The above lines i am not able to insert into mysql. After changing the format as below(as in the sample.txt) i am able to insert into mysql but not correclty. Couls you please help me in this. 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n thanks, narasimha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: importing data into mysql from oracle using a text file
Title: Re: importing data into mysql from oracle using a text file The sample lines are like this in alarm.txt ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 0 2 1 27-SEP-04 27-SEP-04 MiTel 0 0 N 0 2 2 The above lines i am not able to insert into mysql. After changing the format as below(as in the sample.txt) i am able to insert into mysql but not correclty. Couls you please help me in this. 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n thanks, narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intendedfor the exclusive use of the addressee(s) and may contain confidential or privileged information. Ifyou are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediatelyand destroy all copies of this message and any attachments. ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 02 1 27-SEP-04 27-SEP-04 MiTel0 0 N 02 2 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data into mysql from oracle using a text file
Can you post one or 2 sample lines from alarm.txt ? Regards, Andrey P.S. (i am not on [EMAIL PROTECTED] so add me to the CC:) [EMAIL PROTECTED] wrote: Hi, I tried with the spool option to get the data from the tables in the oracle. For this go to pl/sql editor, go to file menu, select spool, asks for a file name give the file name you want, later type the select command from which you want the data. and select spool of option from the file menu. eg; select * from alarm; After spooling i got the file alarm.LST as the attached (it will be there in C:/orant/bin). Save that file as .txt file. Now go to mysql prompt as a root user. 1) choose any of the default database by using the command eg: use test 2) create the table alarm using the same columns, data types (here data types may differ) as in oracle eg; mysql> CREATE TABLE ALARM ( ->ARRIVED DATE NULL, ->DETECTED DATE NULL, ->NAME VARCHAR(20) NULL, ->TYPE INTEGER(1) NULL, ->ALARMLEVEL INTEGER(1) NULL, ->VERIFIED INTEGER(1) NULL, ->DISCLOSEDINTEGER(1) NULL, ->CATEGORY_NUM INTEGER(1) NULL, ->EVENTID INTEGER(5) NOT NULL, ->REASON VARCHAR(60) NULL -> ); 3) Use the LOAD DAT INFILE command for data to import from .txt file (copy the alarm.txt file into c:\mysql\data\test (if we use test database)) eg; mysql> LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm; Here the data is not inserting properly: Bcs 1) The data in the alarm.txt file should be like each colum data should be seperated by \t and each row should be separated by \n . and from spool the data is not coming in the desired format. I did not find any suitable command/option to get the spooling file with the desired delimiters. Colud any one suggest me here. 2) The date format is different in oracle and mysql. This also i took care externally. 3) Even i tried by formating the data in the required (see the attached alarm.txt for the format), getting the result as the following . Not inserting the data properly. mysql> LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql> select * from alarm; ++++--++--+---+--+-+--- -+ | ARRIVED| DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON | ++++--++--+---+--+-+--- -+ | 2004-09-27 | -00-00 | 2004-09-27 |0 | 0 |0 | 0 |0 | 0 | | ++++--++--+---+--+-+--- -+ 1 row in set (0.00 sec) mysql> I tried with another command mysqlimport. but that command also seems to be work with the above format only. Please give me a solution for this. Is there any other way to do this from oracle. My aim is dumping the data from oracle to mysql. Thanks, Narasimha -Original Message- From: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS) Sent: Tue 9/28/2004 9:48 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS); Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Cc: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS) Subject: RE: Using XML with my Sql Hi Narasim, In the Pro* C files (dbids.pc, dbtutil.pc), synonyms are being used. regards, - Praneesh -Original Message- From: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Sent: Monday, September 27, 2004 10:07 PM To: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Cc: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS); Deepak Nagarajan (WT01 - TELECOM SOLUTIONS) Subject: RE: Using XML with my Sql Hi, As per our discussion, You can create an alias or synonym for a MyISAM table by defining a MERGE table that maps to that single table. Synonym is an alias for any table, view or other object in database. May i know where in the code they used synonyms?. Attached i sthe document for merging tables. Thanks, Narasimha -Original Message- From: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Sent: Fri 9/24/2004 7:44 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: Subject: FW: Using XML with my Sql XML Support for MySQL support -Original Message- From: Bishnu Prasad Panda (WT01 - TELECOM SOLUTIONS) Sent: Thursday, September 16, 2004 6:32 PM To: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS); Jat
importing data into mysql from oracle using a text file
Title: Message Hi, I tried with the spool option to get the data from the tables in the oracle. For this go to pl/sql editor, go to file menu, select spool, asks for a file name give the file name you want, later type the select command from which you want the data. and select spool of option from the file menu. eg; select * from alarm; After spooling i got the file alarm.LST as the attached (it will be there in C:/orant/bin). Save that file as .txt file. Now go to mysql prompt as a root user. 1) choose any of the default database by using the command eg: use test 2) create the table alarm using the same columns, data types (here data types may differ) as in oracle eg; mysql> CREATE TABLE ALARM ( -> ARRIVED DATE NULL, -> DETECTED DATE NULL, -> NAME VARCHAR(20) NULL, -> TYPE INTEGER(1) NULL, -> ALARMLEVEL INTEGER(1) NULL, -> VERIFIED INTEGER(1) NULL, -> DISCLOSED INTEGER(1) NULL, -> CATEGORY_NUM INTEGER(1) NULL, -> EVENTID INTEGER(5) NOT NULL, -> REASON VARCHAR(60) NULL -> ); 3) Use the LOAD DAT INFILE command for data to import from .txt file (copy the alarm.txt file into c:\mysql\data\test (if we use test database)) eg; mysql> LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm; Here the data is not inserting properly: Bcs 1) The data in the alarm.txt file should be like each colum data should be seperated by \t and each row should be separated by \n . and from spool the data is not coming in the desired format. I did not find any suitable command/option to get the spooling file with the desired delimiters. Colud any one suggest me here. 2) The date format is different in oracle and mysql. This also i took care externally. 3) Even i tried by formating the data in the required (see the attached alarm.txt for the format), getting the result as the following . Not inserting the data properly. mysql> LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm;Query OK, 1 row affected (0.00 sec)Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql> select * from alarm;++++--++--+---+--+-++| ARRIVED | DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON |++++--++--+---+--+-++| 2004-09-27 | -00-00 | 2004-09-27 | 0 | 0 | 0 | 0 | 0 | 0 | |++++--++--+---+--+-++1 row in set (0.00 sec) mysql> I tried with another command mysqlimport. but that command also seems to be work with the above format only. Please give me a solution for this. Is there any other way to do this from oracle. My aim is dumping the data from oracle to mysql. Thanks, Narasimha -Original Message- From: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS) Sent: Tue 9/28/2004 9:48 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS); Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Cc: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS) Subject: RE: Using XML with my Sql Hi Narasim, In the Pro* C files (dbids.pc, dbtutil.pc), synonyms are being used. regards, - Praneesh -Original Message-From: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Sent: Monday, September 27, 2004 10:07 PMTo: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS)Cc: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS); Deepak Nagarajan (WT01 - TELECOM SOLUTIONS)Subject: RE: Using XML with my Sql Hi, As per our discussion, You can create an alias or synonym for a MyISAM table by defining a MERGE table that maps to that single table. Synonym is an alias for any table, view or other object in database. May i know where in the code they used synonyms?. Attached i sthe document for merging tables. Thanks, Narasimha -Original Message- From: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Sent: Fri 9/24/2004 7:44 PM To: Lakshmi
importing data into mysql from oracle
Hi, Could any one of you suggest me a better way to bump the data in oracle 7.3 to mysql 4.0 classic. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tue 9/28/2004 8:58 PM To: martin fasani Cc: [EMAIL PROTECTED] Subject: Re: Oracle query to mysql Your original Oracle(R) query (slightly reformatted): SELECT IMRTAB.IMR906 AS NUM906 , IMRTAB.IMRFLL AS FLL , SUM(IMRTAB.IMRCLL) AS CLL , ROUND(SUM(IMRTAB.IMRDSC),2) AS DUR , ROUND(SUM(IMRTAB.IMRDSC)/SUM(IMRTAB.IMRCLL),2) AS PRO , SUM(IMRTAB1.IMRCLL) AS CLL_N , ROUND(SUM(IMRTAB1.IMRDSC),2) AS DUR_N , ROUND(SUM(IMRTAB1.IMRDSC)/SUM(IMRTAB1.IMRCLL),2) AS PRO_N , SUM(IMRTAB2.IMRCLL) AS CLL_R , ROUND(SUM(IMRTAB2.IMRDSC),2) AS DUR_R , ROUND(SUM(IMRTAB2.IMRDSC)/SUM(IMRTAB2.IMRCLL),2) AS PRO_R FROM IMRTAB , IMRTAB IMRTAB1 , IMRTAB IMRTAB2 /* here does the tables alias*/ WHERE IMRTAB.IMRIDE = IMRTAB1.IMRIDE (+) AND IMRTAB.IMRIDE = IMRTAB2.IMRIDE (+) /* links by the uniqID both alias */ AND (IMRTAB1.IMRTAR (+) = 'N') /* takes N calls and discard the rest for IMRTAB1 */ AND (IMRTAB2.IMRTAR (+) = 'R') /* takes R calls and discard the rest for IMRTAB2 */ AND (IMRTAB.IMRFLL BETWEEN '01/09/2004' and '10/09/2004') AND (IMRTAB.IMRCLI=2584 AND (IMRTAB.IMR906=803xx )) GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL My MySQL translation: SELECT IMRTAB.IMR906 AS NUM906 , IMRTAB.IMRFLL AS FLL , SUM(IMRTAB.IMRCLL) AS CLL , ROUND(SUM(IMRTAB.IMRDSC),2) AS DUR , ROUND(SUM(IMRTAB.IMRDSC)/SUM(IMRTAB.IMRCLL),2) AS PRO , SUM(IMRTAB1.IMRCLL) AS CLL_N , ROUND(SUM(IMRTAB1.IMRDSC),2) AS DUR_N , ROUND(SUM(IMRTAB1.IMRDSC)/SUM(IMRTAB1.IMRCLL),2) AS PRO_N , SUM(IMRTAB2.IMRCLL) AS CLL_R , ROUND(SUM(IMRTAB2.IMRDSC),2) AS DUR_R , ROUND(SUM(IMRTAB2.IMRDSC)/SUM(IMRTAB2.IMRCLL),2) AS PRO_R FROM IMRTAB LEFT JOIN IMRTAB IMRTAB1 ON IMRTAB.IMRIDE = IMRTAB1.IMRIDE AND IMRTAB1.IMRTAR = 'N' /* N calls only for IMRTAB1 */ LEFT JOIN IMRTAB IMRTAB2 ON IMRTAB.IMRIDE = IMRTAB2.IMRIDE AND IMRTAB2.IMRTAR = 'R' /* R calls only for IMRTAB2 */ WHERE IMRTAB.IMRFLL BETWEEN '2004-09-01' and '2004-09-10' AND IMRTAB.IMRCLI=2584 AND IMRTAB.IMR906=803xx GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL You were using the Oracle syntax ", ...(+)" to declare your outer joins. The equivalent MySQL form is "LEFT JOIN... ON ...". http://dev.mysql.com/doc/mysql/en/JOIN.html I also had to reformat the dates in your WHERE clause to be MySQL formatted: '01/09/2004' (dd/mm/) =>> '2004-09-01' (-mm-dd) http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html http://dev.mysql.com/doc/mysql/en/DATETIME.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine "martin fasani" <[EMAIL PROTECTED]> wrote on 09/28/2004 07:23:38 AM: > > Hi guys, > > I'm working in a telecom company that has Oracle for the call statistics. > Now we export the daily stats to a remote mySql. > > The daily resume table looks like this: > ++---+-++--+ > --+++ > | IMRFLL | IMR906| IMRTER | IMRTAR | IMRDUR | > IMRFAC | IMRCLI | IMRCLL | > ++---+-++--+ > --+++ > | 2004-06-01 | 803xx | x | N | 446.9166572 | > 40355904 | 21 | 26 | > | 2004-06-01 | 803xx | 0 | R | 9.414 | > 40355904 | 21 | 10 | > ++---+-++--+ > --+++ > > What I need it's to get a report that joins the table to itself two times to > get the Normal tarif ( IMRTAR=N) and the Reduced tarif (IMRTAR=R). > > In Oracle is done using Outer j
Importing data
Dear friends, I import data to my table,from CSV file.CSV file has emails in each row. While importing, A window pops up stating number of rows imported, however when I see the table, nothing has been imported. Version of mysql, gui and structure of table has been pasted. Any guidance with regard to this scenerio and issue, please. Thank you. -- Mysql version: 4.0.17-nt Using SQLyog as gui ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | NO| int(5) unsigned | | PRI | NULL| auto_increment | | WEBADDRESS | varchar(50) | YES | | NULL|| | ADDRESS | varchar(200)| YES | | NULL|| | FIRSTNAME |varchar(240)| YES | | NULL| | | LASTNAME | varchar(240)| YES | | NULL|| | EMAIL | varchar(23) | | | | | | REMARKS | varchar(240)| YES | | NULL|| | PHONE | varchar(240)| YES | | NULL| | | FAX| varchar(240)| YES | | NULL| | | EMAIL2 | varchar(240)| YES | | NULL|| | STATE| varchar(240)| YES | | NULL|| | CITY | varchar(240)| YES | | NULL| | | COUNTRY| varchar(240)| YES | | NULL|| | ZIP| varchar(240)| YES | | NULL|| | POSTALCODE | varchar(240)| YES | | NULL|| | TITLE | varchar(240)| YES | | NULL|| | SALUTATION | varchar(240)| YES | | NULL|| | CUSTOM3| varchar(240)| YES | | NULL|| | CUSTOM4| varchar(240)| YES | | NULL|| | CUSTOM5| varchar(240)| YES | | NULL|| | CUSTOM6| varchar(240)| YES | | NULL|| | PRIMARYKEY | int(6) | | PRI | 0 || | COMPANY| varchar(29) | YES | | NULL|| ++-+--+-+-++ 23 rows in set (0.00 sec)
Re: Importing data, indexes, and analyzing tables.
After a day of looking, I answered my own questions, and I'll post those answers here in case anyone else was interested in the answer. First, "LOAD DATA" + "ALTER TABLE ADD INDEX"... seems to be slower than a "mysqldump" + "ANALYZE TABLE". Of course, you don't always have a mysql dump file. After importing a mysql dump file, it's wise to analyze all tables imported. I found that SHOW INDEX FROM table; would show the cardinality (the number of unique values) of an index. The more unique the data being indexed, the faster the index is. Another way to put it, if you set up an equation like, (# of rows in table) divided by (cardinality of an index) you would want a number that is as close to 1 as possible (there will never be more unique values in a table than there are rows). The lower that ratio is (the closer to 1), the more efficient the index becomes. Here's an example of the cardinality after a dump but before an ANALYZE TABLE, and after an ANALYZE TABLE (these two indexes are on the same table) from our database; there are 502055 rows in this table. Index names have been changed to protect the innocent: Before the ANALYZE, index1 has a cardinality of 81214 index2 has a cardinality of 81214 After the ANALYZE index1 has a cardinality of 97192 index2 has a cardinality of 20248 If no analyze was done, and someone did an equi-join on the column indexed by index1 and a second join on the column indexed by index2, the optimizer would use some other criteria for selecting an index other than the cardinality (perhaps the data-type of the column, the alphabetical order of the column, etc - anyone know what that criteria would be?). After the analyze, you can see that the cardinality of index1 has gone up, while index2 has gone down. Index1 is now a far better choice for the optimizer to use (remember, MySQL can only use one index per table per query, so it has to pick the most efficient one), and should result in faster results. Hope that provides some insight for anyone interested. David. David Griffiths wrote: We have a somewhat large database, with a snapshot of the data that we import into the database. Normally, we create a database, create the tables, import the data with "LOAD DATA", add the indexes, and then the foreign keys (we are using InnoDB, 4.0.18). We call this the load-data-method. Sometimes we dump the data with mysqldump, create a new database, and pipe the dump file into the new database. We call this the mysqldump-method. I was wondering about the time it takes to import either one. The mysqldump-method takes about 45 minutes. Tables, indexes and foreign keys are created. The load-data-method takes two to three hours. Using LOAD DATA is supposed to be the fastest way to get data into MySQL. We use the ALTER TABLE ADD INDEX (), ADD INDEX() ... method, which is supposed to be the fastest way to add indexes to a table. So I'm curious as to why it takes so much longer. I suspect that the mysqldump-method needs an "ANALYZE TABLE ..." (for each table in the database) at the end of it, to rebuild the statistics for the tables, and if that was done, then the load-data-method would be comparitively faster. Is this correct, or am I off-base with this? Are the statistics for the indexes in InnoDB correct or incorrect after a mysqldump file is imported into the db. This brings my next question - as I was looking around, trying to get some insight, I was reading the mysql documentation on "SHOW INDEX FROM tablename", which is supposed to show key-distribution across a table (http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html). I've run it, and I'm a bit confused about inferring anything from the results of the "SHOW INDEX..." statement (http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html). The most interesting column in the result set seems to be the "cardinality" column, which stores the number of unique values in an index, but on large tables, it's tough to know if that's accurate if the index is not unique. Is there any way to read the results of a SHOW INDEX to be able to figure out if a table needs analyzing? MySQL (Paul DuBois) was written before ANALYZE TABLE was relevant for InnoDB, and High Performance MySQL (Jeremy Zawodny and Derek Balling) doesn't delve into the details about the circumstances where tables (and indexes) need to be re-analyzed. Thanks in advance for any replies. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data, indexes, and analyzing tables.
We have a somewhat large database, with a snapshot of the data that we import into the database. Normally, we create a database, create the tables, import the data with "LOAD DATA", add the indexes, and then the foreign keys (we are using InnoDB, 4.0.18). We call this the load-data-method. Sometimes we dump the data with mysqldump, create a new database, and pipe the dump file into the new database. We call this the mysqldump-method. I was wondering about the time it takes to import either one. The mysqldump-method takes about 45 minutes. Tables, indexes and foreign keys are created. The load-data-method takes two to three hours. Using LOAD DATA is supposed to be the fastest way to get data into MySQL. We use the ALTER TABLE ADD INDEX (), ADD INDEX() ... method, which is supposed to be the fastest way to add indexes to a table. So I'm curious as to why it takes so much longer. I suspect that the mysqldump-method needs an "ANALYZE TABLE ..." (for each table in the database) at the end of it, to rebuild the statistics for the tables, and if that was done, then the load-data-method would be comparitively faster. Is this correct, or am I off-base with this? Are the statistics for the indexes in InnoDB correct or incorrect after a mysqldump file is imported into the db. This brings my next question - as I was looking around, trying to get some insight, I was reading the mysql documentation on "SHOW INDEX FROM tablename", which is supposed to show key-distribution across a table (http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html). I've run it, and I'm a bit confused about inferring anything from the results of the "SHOW INDEX..." statement (http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html). The most interesting column in the result set seems to be the "cardinality" column, which stores the number of unique values in an index, but on large tables, it's tough to know if that's accurate if the index is not unique. Is there any way to read the results of a SHOW INDEX to be able to figure out if a table needs analyzing? MySQL (Paul DuBois) was written before ANALYZE TABLE was relevant for InnoDB, and High Performance MySQL (Jeremy Zawodny and Derek Balling) doesn't delve into the details about the circumstances where tables (and indexes) need to be re-analyzed. Thanks in advance for any replies. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
This is just my experience. But if you are doing this sort of work, it may well pay to construct your own import program. There are many ways of doing this, like parsing an email message, or using a XML/SOAP server. This will probably pay on the long run, as you can introduce filters, data checks, security and pre-processing. Ben. Paul Kruger wrote: Hello.. Im a new sql user so if this is a common prob Please be kind Im trying to set up mysql 4.0.13 With a database that contains 60k records… The client wants to import 6 records a week With near 4mill records in a database… My problem is I don’t want to give ssh access to The machine… So iv tried phpmyadmin And access to import the data but it seems to get to 40k records And then fails or times out… What methods do you all use to import super large amounts of data? Thanks in advance Paul --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 5/22/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data
Hello.. Im a new sql user so if this is a common prob Please be kind Im trying to set up mysql 4.0.13 With a database that contains 60k records… The client wants to import 6 records a week With near 4mill records in a database… My problem is I don’t want to give ssh access to The machine… So iv tried phpmyadmin And access to import the data but it seems to get to 40k records And then fails or times out… What methods do you all use to import super large amounts of data? Thanks in advance Paul --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.690 / Virus Database: 451 - Release Date: 5/22/2004
Importing data to existing system
In a bit of a quandary here... I have a user table and a registered serials table Table data at end of email... I have been given a large set of new data, but it is flat file, basically, it looks just like the user table, with the addition of one field called serial. Somehow, I need to take the 100K or so records, which are currently in a table that is called user_tmp and has the exact same structure as user with one additional field called serial, copy each field from the user_tmp to the user table, which should auto-inc the id field, then copy the serial from the user_tmp table along with the id, and put those in the Registered_serials, with serial going into serial, and is into user_id. When all is said and done, I would hope both tables still have the same number of records in them. I was going to script this out, but the sheer volume of records make me think it could fail. user +-+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | username| varchar(16) | | | || | password| tinyblob | | | || | session | varchar(32) | | | || | first_name | varchar(48) | | | || | middle_name | varchar(32) | | | || | last_name | varchar(48) | | | || | name| varchar(255) | | | || | company | varchar(96) | | | || | department | varchar(96) | | | || | address | varchar(128) | | | || | address2| varchar(128) | | | || | city| varchar(64) | | | || | state | varchar(64) | | | || | country | varchar(64) | | | || | zip | varchar(64) | | | || | phone | varchar(48) | | | || | fax | varchar(48) | | | || | email | varchar(40) | | | || | active | char(1) | | | 0 || | updated | timestamp(14) | YES | | NULL|| | added | timestamp(14) | YES | | NULL|| +-+---+--+-+-++ Registered_serials +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | user_id | int(11) | | | 0 || | serial | varchar(20) | | | || | product | varchar(4)| | | || | updated | timestamp(14) | YES | | NULL|| | added| timestamp(14) | YES | | NULL|| +--+---+--+-+-++ -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data to existing system
In a bit of a quandary here... I have a user table and a registered serials table Table data at end of email... I have been given a large set of new data, but it is flat file, basically, it looks just like the user table, with the addition of one field called serial. Somehow, I need to take the 100K or so records, which are currently in a table that is called user_tmp and has the exact same structure as user with one additional field called serial, copy each field from the user_tmp to the user table, which should auto-inc the id field, then copy the serial from the user_tmp table along with the id, and put those in the Registered_serials, with serial going into serial, and is into user_id. When all is said and done, I would hope both tables still have the same number of records in them. I was going to script this out, but the sheer volume of records make me think it could fail. user +-+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | username| varchar(16) | | | || | password| tinyblob | | | || | session | varchar(32) | | | || | first_name | varchar(48) | | | || | middle_name | varchar(32) | | | || | last_name | varchar(48) | | | || | name| varchar(255) | | | || | company | varchar(96) | | | || | department | varchar(96) | | | || | address | varchar(128) | | | || | address2| varchar(128) | | | || | city| varchar(64) | | | || | state | varchar(64) | | | || | country | varchar(64) | | | || | zip | varchar(64) | | | || | phone | varchar(48) | | | || | fax | varchar(48) | | | || | email | varchar(40) | | | || | active | char(1) | | | 0 || | updated | timestamp(14) | YES | | NULL|| | added | timestamp(14) | YES | | NULL|| +-+---+--+-+-++ Registered_serials +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | user_id | int(11) | | | 0 || | serial | varchar(20) | | | || | product | varchar(4)| | | || | updated | timestamp(14) | YES | | NULL|| | added| timestamp(14) | YES | | NULL|| +--+---+--+-+-++ -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing Data into MySQL
If I understand correctly, your text file does not have an ID for each row, but the table does have an ID column. Good. Your table should have the record ID column defined as AUTO_INCREMENT. Something like CREATE TABLE table_name ( ID int NOT NULL PRIMARY KEY AUTO_INCREMENT, other column definitions... In that case, simply leave ID out of the columns you are importing to have mysql automatically fill in the ID with unique values. That's the point of AUTO_INCREMENT. The AUTO_INCREMENT column gets a unique number assigned by mysql if you leave it out or if you assign it the (non) value NULL. See "Using AUTO_INCREMENT" <http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html> and "CREATE TABLE Syntax", <http://www.mysql.com/doc/en/CREATE_TABLE.html> and "mysqlimport, Importing Data from Text Files" <http://www.mysql.com/doc/en/mysqlimport.html> in the manual for more. Michael Bob Cohen wrote: Sorry in advance for the dumb newbie question but . . . I'm using a content management system and want to avoid the hassle of entering data by hand when I've already got it in a delimited text file. I get the basic concept of using mysqlimport. However, the source file's data doesn't match the target file's structure. Of particular concern for me is that the target table has record id files the would need to be incremented. I get the task on the conceptual level. E.g., map the incoming columns with the appropriate fields in the target table and increment the record id number. I'm a web designer not a programmer. I'm not at all sure how to accomplish this task. Though I would like to learn. Any help would be appreciated. Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing Data into MySQL
Sorry in advance for the dumb newbie question but . . . I'm using a content management system and want to avoid the hassle of entering data by hand when I've already got it in a delimited text file. I get the basic concept of using mysqlimport. However, the source file's data doesn't match the target file's structure. Of particular concern for me is that the target table has record id files the would need to be incremented. I get the task on the conceptual level. E.g., map the incoming columns with the appropriate fields in the target table and increment the record id number. I'm a web designer not a programmer. I'm not at all sure how to accomplish this task. Though I would like to learn. Any help would be appreciated. Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing data
[snip] How can i import table structure and data which is there in a text file to database say test_database using phpmyadmin. I m asking using phpMyadmin as i don have shell access and hence can't use mysql commands. [/snip] What format is the table structure in? As for the data it should be fairly straighht forward. Even as text it should be delimited (either by comma, tab, or other similar delimeter). Once the tables are created you can import in phpmyadmin by viewing the table properties page and clicking the 'Insert data from a textfile into table' link and following the directions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data
Hi all, How can i import table structure and data which is there in a text file to database say test_database using phpmyadmin. I m asking using phpMyadmin as i don have shell access and hence can't use mysql commands. Plz help me ... its very urgent .. Thanks in advance Binay
Re: Importing Data
* Trevor > after a bit of help on how to import data from a pastel > accounting database into mysql, i also need to remove some lines > out of the pastel txt file before importing into mysql You are defining your new problem to be something to be sorted out before mysql is involved, in which case this list is the wrong forum. You would use a programming language or text stream editor tool, or maybe even a text editor, if the amount of data is small. Which of those you would choose depends on your OS and your previous knowledge. However, your problem can probably also be solved within mysql. > eg 1 > "*PC\CC-ABS533 ","COMPUCON Abacus Celeron/Pentium PC > ",0,0,0,0,0,0,0,0,0,0,0,0,0,0,"","" > > has to end up with this data > "*PC\CC-ABS533 ","COMPUCON Abacus Celeron/Pentium PC ",0 > > eg2 > "NCHW\IQISW050R ","iNEQ Wireless BB Rou.& USB PrnServer > ",218,217,0,0,239.8,238.7,0,0,0,0,0,0,0,0,""," > > has to end up with this data > "NCHW\IQISW050R ","iNEQ Wireless BB Rou.& USB PrnServer",218 Looks like you wish to drop many of the columns? You can do that after the import, using the ALTER TABLE statement, or you can avoid importing them in the first place, using a field list in the LOAD DATA statement. http://www.mysql.com/doc/en/ALTER_TABLE.html > http://www.mysql.com/doc/en/LOAD_DATA.html > > in the same file it has line as follows, they have a common > factor "zz, these lines need to be deleted > "ZVPX\ARDX550 ","zz BenQ DX550 1200 Lum. XGA DLP AV-Box You can delete rows after the data is imported, using a DELETE statement. If the column was named ProdName and the table was named Products: DELETE FROM Products WHERE ProdName LIKE "zz%"; http://www.mysql.com/doc/en/DELETE.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing Data
Hi All after a bit of help on how to import data from a pastel accounting database into mysql, i also need to remove some lines out of the pastel txt file before importing into mysql eg 1 "*PC\CC-ABS533 ","COMPUCON Abacus Celeron/Pentium PC ",0,0,0,0,0,0,0,0,0,0,0,0,0,0,"","" has to end up with this data "*PC\CC-ABS533 ","COMPUCON Abacus Celeron/Pentium PC ",0 eg2 "NCHW\IQISW050R ","iNEQ Wireless BB Rou.& USB PrnServer ",218,217,0,0,239.8,238.7,0,0,0,0,0,0,0,0,""," has to end up with this data "NCHW\IQISW050R ","iNEQ Wireless BB Rou.& USB PrnServer",218 in the same file it has line as follows, they have a common factor "zz, these lines need to be deleted "ZVPX\ARDX550 ","zz BenQ DX550 1200 Lum. XGA DLP AV-Box Thanks for any help that you can offer Cheers Trevor
Importing data into MySQL
I have data that is in a progress database. I need to get a copy of the data into my mysql database. What would be the best approach ? I can dump the data in any specific format, so. Never tried ODBC with Progress (8.2C12), so don't know if that can/will work. thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Sorry hit the wrong reply button ;-) > Hi Rajendra, > > In case you are new to mysql the easiest way would be to do it on the > command line base : > > 1) shutdown the server > 2) go to your data directory f.e /var/lib/mysql/dbname1 > 3) copy the table files to the folder of dbname2 > > f.e cp -r * ../dbname2 > > 4) change the ownership of the copied tables in ../dbname2 to the Linux > mysql account > > chown -r > chgrp -r > > 5) start your mysql server again > > Thats not the most professional way , put for beginners the easiest way to > do it. > > > > Best regards > > Nils Valentin > Tokyo/Japan > > 2003年 5月 30日 金曜日 11:24、Rajendra Kumar さんは書きました: > > Hello Sir, > > > > thanks for the reply. i have one more doubt. > > > > i want to import 2 tables from one database to > > another database like the tables structure and as well > > as its records. > > > > have a nice day, > > > > Naren. > > > > > > > > --- Egor Egorov <[EMAIL PROTECTED]> wrote: > > > > > Rajendra Kumar <[EMAIL PROTECTED]> wrote: > > > > i know little bit about mysql. i use mysql to use > > > > the webportal system of phpnuke. can anyone help > > > > > > me > > > > > > > to import data in mysql. for exams i have a file > > > > nuke.sql which contains like tables design and > > > > > > there > > > > > > > records. > > > > > > > > how to call this file when i am having sql > > > > > > prompt. i > > > > > > > use redhat linux for mysql. > > > > > > Execute from the shell: > > > > > > mysql -uuser_name -p database_name < nuke.sql > > > > > > and enter the user password. > > > > > > > > > > > > -- > > > For technical support contracts, goto > > > https://order.mysql.com/?ref=ensita > > > This email is sponsored by Ensita.net > > > http://www.ensita.net/ > > >__ ___ ___ __ > > > / |/ /_ __/ __/ __ \/ /Egor Egorov > > > / /|_/ / // /\ \/ /_/ / /__ > > > [EMAIL PROTECTED] > > > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > > ><___/ www.mysql.com > > > > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > Missed your favourite TV serial last night? Try the new, Yahoo! TV. > >visit http://in.tv.yahoo.com -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 有限会社ナレッジデザイン 〒182-0024 東京都調布市布田4-6-1 調布丸善ビル7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Hello Sir, thanks for the reply. i have one more doubt. i want to import 2 tables from one database to another database like the tables structure and as well as its records. have a nice day, Naren. --- Egor Egorov <[EMAIL PROTECTED]> wrote: > Rajendra Kumar <[EMAIL PROTECTED]> wrote: > > > > i know little bit about mysql. i use mysql to use > > the webportal system of phpnuke. can anyone help > me > > to import data in mysql. for exams i have a file > > nuke.sql which contains like tables design and > there > > records. > > > > how to call this file when i am having sql > prompt. i > > use redhat linux for mysql. > > > > > > Execute from the shell: > > mysql -uuser_name -p database_name < nuke.sql > > and enter the user password. > > > > -- > For technical support contracts, goto > https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net > http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ > [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > Missed your favourite TV serial last night? Try the new, Yahoo! TV. visit http://in.tv.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Rajendra Kumar <[EMAIL PROTECTED]> wrote: > > i know little bit about mysql. i use mysql to use > the webportal system of phpnuke. can anyone help me > to import data in mysql. for exams i have a file > nuke.sql which contains like tables design and there > records. > > how to call this file when i am having sql prompt. i > use redhat linux for mysql. > > Execute from the shell: mysql -uuser_name -p database_name < nuke.sql and enter the user password. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data to tables
Hello List, i am new to mysql. i use php-nuke web portal. i have a database.sql file which contains my database. it contains tables & there records in .sql file which can be opened with notepad. if i do a fresh installation of mysql how do i recover my database using my backup file. i mean how to import database or tables. any help appreciated. Thanks,Naren. Catch all the cricket action. Download Yahoo! Score tracker
importing data
Hello List, i know little bit about mysql. i use mysql to use the webportal system of phpnuke. can anyone help me to import data in mysql. for exams i have a file nuke.sql which contains like tables design and there records. how to call this file when i am having sql prompt. i use redhat linux for mysql. any help appreciated. Naren. Missed your favourite TV serial last night? Try the new, Yahoo! TV. visit http://in.tv.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is the best data format to start with when importing data into mysql?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 27 May 2003 11:17:34 +0200, Emiliano Rustighini wrote: >I can ask for the data in any data format, but I would like to get some >advice on what is best to ask for. > I have found text format to be the most flexible method for transferring between systems. It bypasses big-/little-endian problems, you can floating point as accurate as you want, etc. Its only downside is the increase in size, approximately 1.5x, over using binary formats. >If you have some suggestions about the connection tool as well it would be >grand. > Since you gave no clue which systems you are using, it's hard to suggest anything. - -- jimoe at sohnen-moe dot com pgp/gpg public key: http://www.keyserver.net/en/ -BEGIN PGP SIGNATURE- Version: PGPfreeware 5.0 OS/2 for non-commercial use Comment: PGP 5.0 for OS/2 Charset: cp850 wj8DBQE+1EC9sxxMki0foKoRAlrLAJ0dcg2Wf4qyNW4dkmaIDc1IDB7wKwCgnmOA qSjWb8sEhPA2mOypLz53+zw= =a3p6 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data from pervasvie to MySQL
> Hi all, Hi, > I have a database in Pervasive and I want to import it to MySQL. > I tried to convert the Pervasive data to a text file and then import it > into MySQL but it failed. > Is there any tool to do this directly? > Does MySQLCC provide any export option through which I can get the data > from Pervasive? Try Perl. You can do everything You need. Best regards, Maciej Bobrowski - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
importing data from pervasvie to MySQL
Hi all, I have a database in Pervasive and I want to import it to MySQL. I tried to convert the Pervasive data to a text file and then import it into MySQL but it failed. Is there any tool to do this directly? Does MySQLCC provide any export option through which I can get the data from Pervasive? Thanks, Prasanth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem importing data from Access
try this: 1. export the data from ms access to a file on the windows box. 2. fire up ftp from the command line and set it to ascii mode. 3. ftp the file from the windows box to the linux box. if the eol is the problem, the ftp protocol will insure that the eol is correctly translated when moved to the new system. > When I import data from the text file I exported out of Access it works > correctly except that MySql seems to append and/or prepend some sort of > mystery character to the fields it imports. So, I can get the data into the > database, but I can't get it out (since the SQL statement can't match the > mystery character). I'm using the graphical interface to import data since > it fails at the command line even though my version of MySql is a later one > than the required 3.22.15. The GUI shows the mystery character as a bold > pipe "|". The command line seems to show a problem with the size of the > field. The select * from table statements look like this on the command > line: > > +--+ > | Col 1 | > +--+ > | data | > |ta| >|dat| > +--+ > > instead of this: > > +--+ > | Col 1 | > +--+ > | data | > | data | > | data | > +--+ > > My guess is that there is a problem with the end of line character being > exported to the text file from Access. I chose "\n" as the end of line > character to separate records, but maybe it is something else. > > Any ideas? > > Thanks, > > Brad > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem importing data from Access
I figured it out. Thanks for all the tips everyone. I didn't know there were 2 characters at the end of a line in Windows. For anyone who has this problem: Use "\r\n" to separate records Use "\t" instead of a comma to deliminate fields -Original Message- From: Gelu Gogancea [mailto:ggelu@;arctic.ro] Sent: Wednesday, November 06, 2002 1:02 PM To: Schroeder, Bradley (Contractor); 'Black, Kelly W [PCS]'; [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, You must use a HEX editor to see this characters which are not printable.I use DOS NAVIGATOR...it's free and you can download it from the INTERNET. You must view the file in HEX and find the characters "0A,0D" on the end of line. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> To: "'Black, Kelly W [PCS]'" <[EMAIL PROTECTED]>; "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 9:41 PM Subject: RE: Problem importing data from Access > The text file seems to be fine as far as I can tell. The columns are all in > the correct order. > > It sounds like I need to look out for the "line feed" character, as well as > the carriage return since I'm working on Windows. Anybody know how the line > feed character is represented? > > > -Original Message- > From: Black, Kelly W [PCS] [mailto:kblack05@;sprintspectrum.com] > Sent: Wednesday, November 06, 2002 12:34 PM > To: 'Gelu Gogancea'; Schroeder, Bradley (Contractor); > [EMAIL PROTECTED] > Subject: RE: Problem importing data from Access > > > This is incorrect. > > !google "new line character" linux windows > > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Wednesday, November 06, 2002 11:33 AM > To: Black, Kelly W [PCS]; Schroeder, Bradley (Contractor); > [EMAIL PROTECTED] > Subject: Re: Problem importing data from Access > > > Hi, > Is no difference but in *nix some text editors (like "vi" for example) put > only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like > in Windows(0Dh,0Ah). > Almost sure is something wrong with the .txt file.Few days a go i have a > similar situation and it was from the the dump/.txt file.Data in the .txt > file must be in the same order with the fields/columns from MySQL table. > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> > To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, November 06, 2002 9:04 PM > Subject: RE: Problem importing data from Access > > > > I have seen this when Windows was involved. > > Windows word and note pads have a different new > > line character than Un*x type systems. > > > > Perhaps that's what's foiling the sql query. > > > > > > ~K Black > > > > -Original Message- > > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > > Sent: Wednesday, November 06, 2002 10:54 AM > > To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] > > Subject: Re: Problem importing data from Access > > > > > > Hi, > > Are you sure that .txt file is OK? > > > > Regards, > > > > Gelu > > _ > > G.NET SOFTWARE COMPANY > > > > Permanent e-mail address : [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > - Original Message - > > From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, November 06, 2002 7:06 PM > > Subject: Problem importing data from Access > > > > > > > When I import data from the text file I exported out of Access it works > > > correctly except that MySql seems to append and/or prepend some sort of > > > mystery character to the fields it imports. So, I can get the data into > &g
Re: Problem importing data from Access
Hi, You must use a HEX editor to see this characters which are not printable.I use DOS NAVIGATOR...it's free and you can download it from the INTERNET. You must view the file in HEX and find the characters "0A,0D" on the end of line. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> To: "'Black, Kelly W [PCS]'" <[EMAIL PROTECTED]>; "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 9:41 PM Subject: RE: Problem importing data from Access > The text file seems to be fine as far as I can tell. The columns are all in > the correct order. > > It sounds like I need to look out for the "line feed" character, as well as > the carriage return since I'm working on Windows. Anybody know how the line > feed character is represented? > > > -Original Message- > From: Black, Kelly W [PCS] [mailto:kblack05@;sprintspectrum.com] > Sent: Wednesday, November 06, 2002 12:34 PM > To: 'Gelu Gogancea'; Schroeder, Bradley (Contractor); > [EMAIL PROTECTED] > Subject: RE: Problem importing data from Access > > > This is incorrect. > > !google "new line character" linux windows > > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Wednesday, November 06, 2002 11:33 AM > To: Black, Kelly W [PCS]; Schroeder, Bradley (Contractor); > [EMAIL PROTECTED] > Subject: Re: Problem importing data from Access > > > Hi, > Is no difference but in *nix some text editors (like "vi" for example) put > only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like > in Windows(0Dh,0Ah). > Almost sure is something wrong with the .txt file.Few days a go i have a > similar situation and it was from the the dump/.txt file.Data in the .txt > file must be in the same order with the fields/columns from MySQL table. > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> > To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, November 06, 2002 9:04 PM > Subject: RE: Problem importing data from Access > > > > I have seen this when Windows was involved. > > Windows word and note pads have a different new > > line character than Un*x type systems. > > > > Perhaps that's what's foiling the sql query. > > > > > > ~K Black > > > > -Original Message- > > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > > Sent: Wednesday, November 06, 2002 10:54 AM > > To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] > > Subject: Re: Problem importing data from Access > > > > > > Hi, > > Are you sure that .txt file is OK? > > > > Regards, > > > > Gelu > > _ > > G.NET SOFTWARE COMPANY > > > > Permanent e-mail address : [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > - Original Message - > > From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, November 06, 2002 7:06 PM > > Subject: Problem importing data from Access > > > > > > > When I import data from the text file I exported out of Access it works > > > correctly except that MySql seems to append and/or prepend some sort of > > > mystery character to the fields it imports. So, I can get the data into > > the > > > database, but I can't get it out (since the SQL statement can't match > the > > > mystery character). I'm using the graphical interface to import data > > since > > > it fails at the command line even though my version of MySql is a later > > one > > > than the required 3.22.15. The GUI shows the mystery character as a > bold > > > pipe "|". The command line se
Re: Problem importing data from Access
ASCII table is the same for ALL OS. In my understanding to be a "new line character" is to have a different representation.. So ...always: the LineFeed is 0Ah and CarriageReturn is 0Dh Windows use 0A,0D and some *nix editors use only 0A to make new line. This is means only the "rule" for "breaking" lines in a sequential file is different. The characters which are used in this "rule" are the same. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 9:33 PM Subject: RE: Problem importing data from Access > This is incorrect. > > !google "new line character" linux windows > > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Wednesday, November 06, 2002 11:33 AM > To: Black, Kelly W [PCS]; Schroeder, Bradley (Contractor); > [EMAIL PROTECTED] > Subject: Re: Problem importing data from Access > > > Hi, > Is no difference but in *nix some text editors (like "vi" for example) put > only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like > in Windows(0Dh,0Ah). > Almost sure is something wrong with the .txt file.Few days a go i have a > similar situation and it was from the the dump/.txt file.Data in the .txt > file must be in the same order with the fields/columns from MySQL table. > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> > To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, November 06, 2002 9:04 PM > Subject: RE: Problem importing data from Access > > > > I have seen this when Windows was involved. > > Windows word and note pads have a different new > > line character than Un*x type systems. > > > > Perhaps that's what's foiling the sql query. > > > > > > ~K Black > > > > -Original Message- > > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > > Sent: Wednesday, November 06, 2002 10:54 AM > > To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] > > Subject: Re: Problem importing data from Access > > > > > > Hi, > > Are you sure that .txt file is OK? > > > > Regards, > > > > Gelu > > _ > > G.NET SOFTWARE COMPANY > > > > Permanent e-mail address : [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > - Original Message - > > From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, November 06, 2002 7:06 PM > > Subject: Problem importing data from Access > > > > > > > When I import data from the text file I exported out of Access it works > > > correctly except that MySql seems to append and/or prepend some sort of > > > mystery character to the fields it imports. So, I can get the data into > > the > > > database, but I can't get it out (since the SQL statement can't match > the > > > mystery character). I'm using the graphical interface to import data > > since > > > it fails at the command line even though my version of MySql is a later > > one > > > than the required 3.22.15. The GUI shows the mystery character as a > bold > > > pipe "|". The command line seems to show a problem with the size of the > > > field. The select * from table statements look like this on the command > > > line: > > > > > > +--+ > > > | Col 1 | > > > +--+ > > > | data | > > > |ta| > > >|dat| > > > +--+ > > > > > > instead of this: > > > > > > +--+ > > > | Col 1 | > > > +--+ > > > | data | > > > | data | > > > | data | > > > +--
RE: Problem importing data from Access
The text file seems to be fine as far as I can tell. The columns are all in the correct order. It sounds like I need to look out for the "line feed" character, as well as the carriage return since I'm working on Windows. Anybody know how the line feed character is represented? -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05@;sprintspectrum.com] Sent: Wednesday, November 06, 2002 12:34 PM To: 'Gelu Gogancea'; Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: RE: Problem importing data from Access This is incorrect. !google "new line character" linux windows -Original Message- From: Gelu Gogancea [mailto:ggelu@;arctic.ro] Sent: Wednesday, November 06, 2002 11:33 AM To: Black, Kelly W [PCS]; Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, Is no difference but in *nix some text editors (like "vi" for example) put only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like in Windows(0Dh,0Ah). Almost sure is something wrong with the .txt file.Few days a go i have a similar situation and it was from the the dump/.txt file.Data in the .txt file must be in the same order with the fields/columns from MySQL table. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 9:04 PM Subject: RE: Problem importing data from Access > I have seen this when Windows was involved. > Windows word and note pads have a different new > line character than Un*x type systems. > > Perhaps that's what's foiling the sql query. > > > ~K Black > > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Wednesday, November 06, 2002 10:54 AM > To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] > Subject: Re: Problem importing data from Access > > > Hi, > Are you sure that .txt file is OK? > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, November 06, 2002 7:06 PM > Subject: Problem importing data from Access > > > > When I import data from the text file I exported out of Access it works > > correctly except that MySql seems to append and/or prepend some sort of > > mystery character to the fields it imports. So, I can get the data into > the > > database, but I can't get it out (since the SQL statement can't match the > > mystery character). I'm using the graphical interface to import data > since > > it fails at the command line even though my version of MySql is a later > one > > than the required 3.22.15. The GUI shows the mystery character as a bold > > pipe "|". The command line seems to show a problem with the size of the > > field. The select * from table statements look like this on the command > > line: > > > > +--+ > > | Col 1 | > > +--+ > > | data | > > |ta| > >|dat| > > +--+ > > > > instead of this: > > > > +--+ > > | Col 1 | > > +--+ > > | data | > > | data | > > | data | > > +--+ > > > > My guess is that there is a problem with the end of line character being > > exported to the text file from Access. I chose "\n" as the end of line > > character to separate records, but maybe it is something else. > > > > Any ideas? > > > > Thanks, > > > > Brad > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > B
RE: Problem importing data from Access
This is incorrect. !google "new line character" linux windows -Original Message- From: Gelu Gogancea [mailto:ggelu@;arctic.ro] Sent: Wednesday, November 06, 2002 11:33 AM To: Black, Kelly W [PCS]; Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, Is no difference but in *nix some text editors (like "vi" for example) put only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like in Windows(0Dh,0Ah). Almost sure is something wrong with the .txt file.Few days a go i have a similar situation and it was from the the dump/.txt file.Data in the .txt file must be in the same order with the fields/columns from MySQL table. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 9:04 PM Subject: RE: Problem importing data from Access > I have seen this when Windows was involved. > Windows word and note pads have a different new > line character than Un*x type systems. > > Perhaps that's what's foiling the sql query. > > > ~K Black > > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Wednesday, November 06, 2002 10:54 AM > To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] > Subject: Re: Problem importing data from Access > > > Hi, > Are you sure that .txt file is OK? > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > ----- Original Message - > From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, November 06, 2002 7:06 PM > Subject: Problem importing data from Access > > > > When I import data from the text file I exported out of Access it works > > correctly except that MySql seems to append and/or prepend some sort of > > mystery character to the fields it imports. So, I can get the data into > the > > database, but I can't get it out (since the SQL statement can't match the > > mystery character). I'm using the graphical interface to import data > since > > it fails at the command line even though my version of MySql is a later > one > > than the required 3.22.15. The GUI shows the mystery character as a bold > > pipe "|". The command line seems to show a problem with the size of the > > field. The select * from table statements look like this on the command > > line: > > > > +--+ > > | Col 1 | > > +--+ > > | data | > > |ta| > >|dat| > > +--+ > > > > instead of this: > > > > +--+ > > | Col 1 | > > +--+ > > | data | > > | data | > > | data | > > +--+ > > > > My guess is that there is a problem with the end of line character being > > exported to the text file from Access. I chose "\n" as the end of line > > character to separate records, but maybe it is something else. > > > > Any ideas? > > > > Thanks, > > > > Brad > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem importing data from Access
Hi, Is no difference but in *nix some text editors (like "vi" for example) put only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like in Windows(0Dh,0Ah). Almost sure is something wrong with the .txt file.Few days a go i have a similar situation and it was from the the dump/.txt file.Data in the .txt file must be in the same order with the fields/columns from MySQL table. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Black, Kelly W [PCS]" <[EMAIL PROTECTED]> To: "'Gelu Gogancea'" <[EMAIL PROTECTED]>; "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 9:04 PM Subject: RE: Problem importing data from Access > I have seen this when Windows was involved. > Windows word and note pads have a different new > line character than Un*x type systems. > > Perhaps that's what's foiling the sql query. > > > ~K Black > > -Original Message- > From: Gelu Gogancea [mailto:ggelu@;arctic.ro] > Sent: Wednesday, November 06, 2002 10:54 AM > To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] > Subject: Re: Problem importing data from Access > > > Hi, > Are you sure that .txt file is OK? > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, November 06, 2002 7:06 PM > Subject: Problem importing data from Access > > > > When I import data from the text file I exported out of Access it works > > correctly except that MySql seems to append and/or prepend some sort of > > mystery character to the fields it imports. So, I can get the data into > the > > database, but I can't get it out (since the SQL statement can't match the > > mystery character). I'm using the graphical interface to import data > since > > it fails at the command line even though my version of MySql is a later > one > > than the required 3.22.15. The GUI shows the mystery character as a bold > > pipe "|". The command line seems to show a problem with the size of the > > field. The select * from table statements look like this on the command > > line: > > > > +--+ > > | Col 1 | > > +--+ > > | data | > > |ta| > >|dat| > > +--+ > > > > instead of this: > > > > +--+ > > | Col 1 | > > +--+ > > | data | > > | data | > > | data | > > +--+ > > > > My guess is that there is a problem with the end of line character being > > exported to the text file from Access. I chose "\n" as the end of line > > character to separate records, but maybe it is something else. > > > > Any ideas? > > > > Thanks, > > > > Brad > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem importing data from Access
I have seen this when Windows was involved. Windows word and note pads have a different new line character than Un*x type systems. Perhaps that's what's foiling the sql query. ~K Black -Original Message- From: Gelu Gogancea [mailto:ggelu@;arctic.ro] Sent: Wednesday, November 06, 2002 10:54 AM To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, Are you sure that .txt file is OK? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 7:06 PM Subject: Problem importing data from Access > When I import data from the text file I exported out of Access it works > correctly except that MySql seems to append and/or prepend some sort of > mystery character to the fields it imports. So, I can get the data into the > database, but I can't get it out (since the SQL statement can't match the > mystery character). I'm using the graphical interface to import data since > it fails at the command line even though my version of MySql is a later one > than the required 3.22.15. The GUI shows the mystery character as a bold > pipe "|". The command line seems to show a problem with the size of the > field. The select * from table statements look like this on the command > line: > > +--+ > | Col 1 | > +--+ > | data | > |ta| >|dat| > +--+ > > instead of this: > > +--+ > | Col 1 | > +--+ > | data | > | data | > | data | > +--+ > > My guess is that there is a problem with the end of line character being > exported to the text file from Access. I chose "\n" as the end of line > character to separate records, but maybe it is something else. > > Any ideas? > > Thanks, > > Brad > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem importing data from Access
Hi, Are you sure that .txt file is OK? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Schroeder, Bradley (Contractor)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, November 06, 2002 7:06 PM Subject: Problem importing data from Access > When I import data from the text file I exported out of Access it works > correctly except that MySql seems to append and/or prepend some sort of > mystery character to the fields it imports. So, I can get the data into the > database, but I can't get it out (since the SQL statement can't match the > mystery character). I'm using the graphical interface to import data since > it fails at the command line even though my version of MySql is a later one > than the required 3.22.15. The GUI shows the mystery character as a bold > pipe "|". The command line seems to show a problem with the size of the > field. The select * from table statements look like this on the command > line: > > +--+ > | Col 1 | > +--+ > | data | > |ta| >|dat| > +--+ > > instead of this: > > +--+ > | Col 1 | > +--+ > | data | > | data | > | data | > +--+ > > My guess is that there is a problem with the end of line character being > exported to the text file from Access. I chose "\n" as the end of line > character to separate records, but maybe it is something else. > > Any ideas? > > Thanks, > > Brad > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem importing data from Access
When I import data from the text file I exported out of Access it works correctly except that MySql seems to append and/or prepend some sort of mystery character to the fields it imports. So, I can get the data into the database, but I can't get it out (since the SQL statement can't match the mystery character). I'm using the graphical interface to import data since it fails at the command line even though my version of MySql is a later one than the required 3.22.15. The GUI shows the mystery character as a bold pipe "|". The command line seems to show a problem with the size of the field. The select * from table statements look like this on the command line: +--+ | Col 1 | +--+ | data | |ta| |dat| +--+ instead of this: +--+ | Col 1 | +--+ | data | | data | | data | +--+ My guess is that there is a problem with the end of line character being exported to the text file from Access. I chose "\n" as the end of line character to separate records, but maybe it is something else. Any ideas? Thanks, Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem Importing Data from Text File, HOW IGNORE SOME TEXT COLUMNS???
Reformat your import file. or Add two columns , import, and drop the two columns. or Import to a six column temporary file and do an insert into select from. tl wrote: >Hello, > >#If I have a file "t.txt" > > >111 222 333 ddd 444 ddd >111 222 333 ddd 444 ddd >111 222 333 ddd 444 ddd ><< > ># A table > > >CREATE TABLE userdata ( >a1 varchar(128) NOT NULL default '', >a2 varchar(128) NOT NULL default '', >a3 varchar(128) NOT NULL default '', >a4 varchar(128) NOT NULL default '', >KEY a1 (a1), >KEY a2 (a2), >KEY a3 (a3), >KEY a4 (a4) >) TYPE=MyISAM; ><< > ># How insert into table <- text columns with indexes: (1, 2, 3, 5) ?? ># How ignore text columns with indexes (4, 6) ??? >mysql>> LOAD DATA LOCAL INFILE 't.txt' INTO TABLE userdata FIELDS >mysql>> TERMINATED BY ',' ENCLOSED BY '"' (a1, a2, a3, a4); > > ># If need to have this rezult: >mysql> select * from userdata; >+-+-+-+-+ >| a1 | a2 | a3 | a4 | >+-+-+-+-+ >| 111 | 222 | 333 | 444 | >| 111 | 222 | 333 | 444 | >| 111 | 222 | 333 | 444 | >+-+-+-+-+ > > >Thanks Very much. > >Alvydas > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem Importing Data from Text File, HOW IGNORE SOME TEXT COLUMNS???
Hello, #If I have a file "t.txt" >> 111 222 333 ddd 444 ddd 111 222 333 ddd 444 ddd 111 222 333 ddd 444 ddd << # A table >> CREATE TABLE userdata ( a1 varchar(128) NOT NULL default '', a2 varchar(128) NOT NULL default '', a3 varchar(128) NOT NULL default '', a4 varchar(128) NOT NULL default '', KEY a1 (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4) ) TYPE=MyISAM; << # How insert into table <- text columns with indexes: (1, 2, 3, 5) ?? # How ignore text columns with indexes (4, 6) ??? mysql>> LOAD DATA LOCAL INFILE 't.txt' INTO TABLE userdata FIELDS mysql>> TERMINATED BY ',' ENCLOSED BY '"' (a1, a2, a3, a4); # If need to have this rezult: mysql> select * from userdata; +-+-+-+-+ | a1 | a2 | a3 | a4 | +-+-+-+-+ | 111 | 222 | 333 | 444 | | 111 | 222 | 333 | 444 | | 111 | 222 | 333 | 444 | +-+-+-+-+ Thanks Very much. Alvydas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Urgent Help! Importing Data from Text File...
I did the following to resolve the problem. 1. Your create command didnt work for me and I changed the add(128) on line 6 to addrs varchar(128). I suppose this was a typo since you already had a working table. 2. Your main problem comes from the format of your input. Use /N for ommited column 1 which is an auto_increment and make sure you specify "" explicitly for fields you do not have values for, and delimit carefully with ','. 3. Lastly, you dont need the LINE statement in the LOAD DATA statement since you are going by the default. Also notice that you will have some warnings going by your trying a null value in the auto_increment column. You can ignore this. See my captured procedure and result below: mysql>create database test; Your Table schema file 'create.txt' :(changed add(128) to addrs varchar(128) ) --- CREATE TABLE userdata ( accno int(10) unsigned NOT NULL auto_increment, category mediumint(6) unsigned zerofill NOT NULL default '0', fname varchar(128) NOT NULL default '', lname varchar(128) NOT NULL default '', addrs varchar(128) NOT NULL default '', zip varchar(6) NOT NULL default '', city varchar(64) NOT NULL default '', telefone varchar(16) NOT NULL default '', homepage varchar(128) NOT NULL default '', email varchar(128) NOT NULL default '', telefax varchar(16) NOT NULL default '', PRIMARY KEY (accno), KEY category (category), KEY fname (fname), KEY telefone (telefone), KEY zip (zip), KEY city (city), KEY telefax (telefax) ) TYPE=MyISAM; -- #mysql test < create.txt Your input file 't2t.txt': -- /N,"1","Peter","John","512 Rennes Road","19810","Wilmington","","http://www.mobilink.com","mailto:pjohn@mobili nk.com","", /N,"1","Sandra","Bridget","12th Aveneue No. 301","12548","Broklyn","(212)780101 10","","mailto:[EMAIL PROTECTED]","";, --- mysql> mysql> \u test Database changed mysql> mysql> mysql> LOAD DATA LOCAL INFILE 't2t.txt' INTO TABLE userdata FIELDS TERMINATED BY ',' ENCLOSED BY '"'; Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 3 mysql> select * from userdata; +---+--++-+--+---+-- --++-+-- +-+ | accno | category | fname | lname | addrs| zip | city | telefone | homepage| email| telefax | +---+--++-+--+---+-- --++-+-- +-+ | 1 | 01 | Peter | John| 512 Rennes Road | 19810 | Wilmington || http://www.mobilink.com | mailto:[EMAIL PROTECTED]| | | 2 | 01 | Sandra | Bridget | 12th Aveneue No. 301 | 12548 | Broklyn| (212)780101 10 | | mailto:[EMAIL PROTECTED] | | +---+--++-+--+---+-- --++-+-- +-+ 2 rows in set (0.00 sec) Regards, 'Gbamila - Original Message - From: "Nasir Aziz Gill" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 10, 2002 10:26 AM Subject: Urgent Help! Importing Data from Text File... > Hi fellows, > > I got one text file of 9816 records (9816 lines) seprated by commas and > enclosed by the inverted quotes and seprated by the end of lines. But when I > import the file, it only gets half of records in the table using below > mentioned command; > > LOAD DATA INFILE 'user.txt' INTO TABLE userdata > FIELDS TERMINATED BY ',' ENCLOSED BY '"' > LINES TERMINATED BY '\n' > (category, fname, lname, adresse, zip, city, telephone, email, > homepage); > > > I get the below mentoned message; > > >>Query OK, 4908 rows affected (0.91 sec) > >>Records: 4908 Deleted: 0 Skipped: 0 Warnings: 4913 > >
Re: Urgent Help! Importing Data from Text File...
On Thursday, Oct 10, 2002, at 02:26 America/Phoenix, Nasir Aziz Gill wrote: > Hi fellows, > > I got one text file of 9816 records (9816 lines) seprated by commas and > enclosed by the inverted quotes and seprated by the end of lines. But > when I > import the file, it only gets half of records in the table using below > mentioned command; > > LOAD DATA INFILE 'user.txt' INTO TABLE userdata > FIELDS TERMINATED BY ',' ENCLOSED BY '"' > LINES TERMINATED BY '\n' > (category, fname, lname, adresse, zip, city, >telephone, email, > homepage); > > > I get the below mentoned message; > >>> Query OK, 4908 rows affected (0.91 sec) >>> Records: 4908 Deleted: 0 Skipped: 0 Warnings: 4913 > > > Few records are as follow from the text file; > > "1","Peter","John","512 Rennes > Road","19810","Wilmington","","mailto:[EMAIL PROTECTED]","http:// > www.mobili > nk.com" > "1","Sandra","Bridget","12th Aveneue No. > 301","12548","Broklyn","(212)780 > 101 10","mailto:[EMAIL PROTECTED]",""; > > Note:"" fields are empty(missing data) i.e.(in some records, one is > missing faxnumber and in some records one is missing homepage and in > some > one is missing telephone as well. That's why I have to use the empty > quotes > for representing that field. > > The table structure is as follow; > > CREATE TABLE userdata ( > accno int(10) unsigned NOT NULL auto_increment, > category mediumint(6) unsigned zerofill NOT NULL default '0', > fname varchar(128) NOT NULL default '', > lname varchar(128) NOT NULL default '', > add(128) NOT NULL default '', > zip varchar(6) NOT NULL default '', > city varchar(64) NOT NULL default '', > telefone varchar(16) NOT NULL default '', > homepage varchar(128) NOT NULL default '', > email varchar(128) NOT NULL default '', > telefax varchar(16) NOT NULL default '', > PRIMARY KEY (accno), > KEY category (category), > KEY fname (fname), > KEY telefone (telefone), > KEY zip (zip), > KEY city (city), > KEY telefax (telefax) > ) TYPE=MyISAM; > > Please advise me that whey I am not getting the whole records in the > table > from the text file. > > Your help will be highly appreciated... > With Best Regards... Create a table with all the restrictions removed. No "Key", or "NOT NULL" or "ENUM" or anything else. If All the data can be loaded into that table then you no there is some problems with the restrictions. When loading data from a file, I think it best to always load data into an unrestricted table then move it to the final one. It may take a little longer, but you can then check to see if any records were skipped. Of course you may want some skipped. Or you may find errors in your input file. -- Clayburn W. Juniel, III Phone: (602) 326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.com -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Urgent Help! Importing Data from Text File...
Hi Two thoughts try \r\n instead of \n or if you are getting warnings it may be that there is a problem with the data - illegal characters or something? if neither works email me offlist and I will take a look, if that helps. HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net tel. 0121-242-1473 --- -Original Message- From: Nasir Aziz Gill [mailto:[EMAIL PROTECTED]] Sent: 10 October 2002 09:27 To: [EMAIL PROTECTED] Subject: Urgent Help! Importing Data from Text File... Importance: High Hi fellows, I got one text file of 9816 records (9816 lines) seprated by commas and enclosed by the inverted quotes and seprated by the end of lines. But when I import the file, it only gets half of records in the table using below mentioned command; LOAD DATA INFILE 'user.txt' INTO TABLE userdata FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (category, fname, lname, adresse, zip, city, telephone, email, homepage); I get the below mentoned message; >>Query OK, 4908 rows affected (0.91 sec) >>Records: 4908 Deleted: 0 Skipped: 0 Warnings: 4913 Few records are as follow from the text file; "1","Peter","John","512 Rennes Road","19810","Wilmington","","mailto:[EMAIL PROTECTED]","http://www.mobili nk.com" "1","Sandra","Bridget","12th Aveneue No. 301","12548","Broklyn","(212)780 101 10","mailto:[EMAIL PROTECTED]",""; Note:"" fields are empty(missing data) i.e.(in some records, one is missing faxnumber and in some records one is missing homepage and in some one is missing telephone as well. That's why I have to use the empty quotes for representing that field. The table structure is as follow; CREATE TABLE userdata ( accno int(10) unsigned NOT NULL auto_increment, category mediumint(6) unsigned zerofill NOT NULL default '0', fname varchar(128) NOT NULL default '', lname varchar(128) NOT NULL default '', add(128) NOT NULL default '', zip varchar(6) NOT NULL default '', city varchar(64) NOT NULL default '', telefone varchar(16) NOT NULL default '', homepage varchar(128) NOT NULL default '', email varchar(128) NOT NULL default '', telefax varchar(16) NOT NULL default '', PRIMARY KEY (accno), KEY category (category), KEY fname (fname), KEY telefone (telefone), KEY zip (zip), KEY city (city), KEY telefax (telefax) ) TYPE=MyISAM; Please advise me that whey I am not getting the whole records in the table from the text file. Your help will be highly appreciated... With Best Regards... Nasir - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Urgent Help! Importing Data from Text File...
Hi fellows, I got one text file of 9816 records (9816 lines) seprated by commas and enclosed by the inverted quotes and seprated by the end of lines. But when I import the file, it only gets half of records in the table using below mentioned command; LOAD DATA INFILE 'user.txt' INTO TABLE userdata FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (category, fname, lname, adresse, zip, city, telephone, email, homepage); I get the below mentoned message; >>Query OK, 4908 rows affected (0.91 sec) >>Records: 4908 Deleted: 0 Skipped: 0 Warnings: 4913 Few records are as follow from the text file; "1","Peter","John","512 Rennes Road","19810","Wilmington","","mailto:[EMAIL PROTECTED]","http://www.mobili nk.com" "1","Sandra","Bridget","12th Aveneue No. 301","12548","Broklyn","(212)780 101 10","mailto:[EMAIL PROTECTED]",""; Note:"" fields are empty(missing data) i.e.(in some records, one is missing faxnumber and in some records one is missing homepage and in some one is missing telephone as well. That's why I have to use the empty quotes for representing that field. The table structure is as follow; CREATE TABLE userdata ( accno int(10) unsigned NOT NULL auto_increment, category mediumint(6) unsigned zerofill NOT NULL default '0', fname varchar(128) NOT NULL default '', lname varchar(128) NOT NULL default '', add(128) NOT NULL default '', zip varchar(6) NOT NULL default '', city varchar(64) NOT NULL default '', telefone varchar(16) NOT NULL default '', homepage varchar(128) NOT NULL default '', email varchar(128) NOT NULL default '', telefax varchar(16) NOT NULL default '', PRIMARY KEY (accno), KEY category (category), KEY fname (fname), KEY telefone (telefone), KEY zip (zip), KEY city (city), KEY telefax (telefax) ) TYPE=MyISAM; Please advise me that whey I am not getting the whole records in the table from the text file. Your help will be highly appreciated... With Best Regards... Nasir - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem importing data from SQL server 2000 to mysql
Hi guys, I want to migrate data from SQL Server 2000 to Mysql.I have two problems. First is easy one, First problem is to migrate big fields which had type varchar(7900) or so I tried using Text datatype of mysql, is that the right one or I should have used BLOB. That field doesnt contain any image informtaion or any special information. Only at one place it stores data which is a "email" I am using command LOAD DATA INFILE "c:/mysql/roma/mailmessagetext_table.txt" INTO TABLE mailmessagetext_table; Data which i export from sql server is tab dilimited . Another problem is, For the field messagetext - varchar(7900), it doesnt migrate the only migrated few characters, as this field stores emails. so it has some enter chanracters. SQL server saves them as a special character. But when i try to import, then Mysql cuts the field at that special character, and does not take rest of string. e.g., if i have data like dear roma, how are you thanks abc it will import only "dear roma," as it will find a enter character at the end. I tried using BLob field instead. I tried exporting data using pipe delimited , that does not work either. Please help Thanks Roma - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem importing data from SQL server 2000 to mysql
Hi guys, I want to migrate data from SQL Server 2000 to Mysql. My problem is to migrate big fields which had type varchar(7900) or so I tried using Text datatype of mysql, then I cant migrate data in that field I used command LOAD DATA INFILE "c:/mysql/roma/mailmessagetext_table.txt" INTO TABLE mailmessagetext_table; For the field messagetext - varchar(7900), it only migrated few characters, not the complete length Please help Thanks Roma - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem importing data from SQL server 2000 to mysql
Hi, I want to migrate data from SQL Server 2000 to Mysql. My problem is to migrate big fields which had type varchar(7900) or so I tried using Text datatype of mysql, then I cant migrate data in that field I used command LOAD DATA INFILE "c:/mysql/roma/mailmessagetext_table.txt" INTO TABLE mailmessagetext_table; For the field messagetext - varchar(7900), it only migrated few characters, not the complete length Please help Thanks Roma - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Importing data from Oracle
Hi, I am having two servers running Oracle and MySQL database. Views has been created for me in Oracle and I want to import data from that Oracle table to my MySQL table. I want a script like something main thing is I want to import data from that Oracle database to MySQL daily...like a scheduled job.. The mysqlimport statement imports data only from text filesbut in my case i want to run a scheduled script that imports data from Oracle to MySQL at a specified time... I am using MySQL version 3.23.22 Is there any tool available for free or any other way to do this..Please help me. Shankar __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbee: importing data from .MYD .MYI .frm
Those are the actual table files that MySQL uses for the MyISAM table type. You should be able to just copy them into the datadir under a database directory and MySQL will find them and you should be able to use the tables like normal after copying them. If you don't know where your datadir is you can find out by doing a SHOW VARIABLES like 'datadir'. I recommend shutting down the server first as well, or else you could get into some weird data corruption stuff. One point that people tend to get stuck on is file permissions after copying them in. Make sure they are readable/writable by whomever the mysqld is running as. Harrison - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, May 04, 2002 1:41 PM Subject: Newbee: importing data from .MYD .MYI .frm > Hi all, > > A colleague has created a database with MySQL. She has provided me three files > with the extensions .MYD .MYI .frm, to put them in the server. How can I > import > them into MySQL? I have MySQL 3.23.49 in Debian Linux 'potato'. I have used > myisamchk to check the database, but I do not know how to do something else. > > Thanks in advance. > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbee: importing data from .MYD .MYI .frm
Hi all, A colleague has created a database with MySQL. She has provided me three files with the extensions .MYD .MYI .frm, to put them in the server. How can I import them into MySQL? I have MySQL 3.23.49 in Debian Linux 'potato'. I have used myisamchk to check the database, but I do not know how to do something else. Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Importing data from SQL Server into MySQL
I am in the process of migrating our data from m$ SQL Server to MySQL. I am having a heck of a time trying to figure out how to deal with m$ SQL Server's data type called 'uniqueidentifier'. I have tried very large varchar's and blob's and even longtext but I always get datatype conversion errors when i'm sucking the data across (in Perl, and m$ access) I searched the mailing list archives but didn't find anything useful. I'm hoping someone out there has already jumped this hurtle and can point me in the right direction. Jeremy McNamara, Systems Engineer ISPhone Inc. http://www.isphone.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: importing data
Dear Kevin, check the online manual (www.mysql.com/documentation) for "mysqlimport" and for "load data infile". Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: "Bing Du" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 07, 2001 9:03 PM Subject: Re: importing data > Please see section 6.17 of http://www.mysql.com/doc/index.html. > > Bing > > Bing Du <[EMAIL PROTECTED], 979-845-9577> > Texas A&M University, CIS, Operating Systems, Unix > > >>> Kevin Fonner <[EMAIL PROTECTED]> 08/07/01 10:08AM >>> > Is there an easy way or perhaps a utility to import a tab delimeted > text > file of data into a mysql table > > Thanks, > > -- > Kevin Fonner > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: importing data
Dear Kevin, > Is there an easy way or perhaps a utility to import a tab delimeted text > file of data into a mysql table Try PhpMyAdmin (www.phpmyadmin.com). Great browser based utility. You need to have PHP installed in order to use it. You may as well use the LOAD DATA INFILE command. Check http://www.mysql.com/doc/L/O/LOAD_DATA.html for details. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: "Kevin Fonner" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 07, 2001 5:08 PM Subject: importing data > Is there an easy way or perhaps a utility to import a tab delimeted text > file of data into a mysql table > > Thanks, > > -- > Kevin Fonner > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: importing data
Please see section 6.17 of http://www.mysql.com/doc/index.html. Bing Bing Du <[EMAIL PROTECTED], 979-845-9577> Texas A&M University, CIS, Operating Systems, Unix >>> Kevin Fonner <[EMAIL PROTECTED]> 08/07/01 10:08AM >>> Is there an easy way or perhaps a utility to import a tab delimeted text file of data into a mysql table Thanks, -- Kevin Fonner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: importing data
http://www.mysql.com/doc/L/O/LOAD_DATA.html http://www.mysql.com/doc/m/y/mysqlimport.html http://www.mysql.com/doc/L/o/Loading_tables.html On Tue, 7 Aug 2001, Kevin Fonner wrote: > Is there an easy way or perhaps a utility to import a tab delimeted text > file of data into a mysql table > > Thanks, > > -- > Kevin Fonner > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: importing data
Look up IMPORT DATA INFILE in the manual / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq > -Original Message- > From: Kevin Fonner [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 5:09 PM > To: [EMAIL PROTECTED] > Subject: importing data > > > Is there an easy way or perhaps a utility to import a tab delimeted text > file of data into a mysql table > > Thanks, > > -- > Kevin Fonner > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
importing data
Is there an easy way or perhaps a utility to import a tab delimeted text file of data into a mysql table Thanks, -- Kevin Fonner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
importing data from msql...
I have a little problem. I'm trying to convert a fairly large database from msql (v1.0.16) to MySQL. Using msqldump worked fine on the smaller databases in this project, and MySQL imported them just fine. However on the larger ones it chokes and segfaults. This is likely because of the rather aged hardware it's running on currently. Is there anyway for MySQL to import a msql database that hasn't been "dumped"? Otherwise the alternative appears to be to install msql on a beefier machine, copy the database files over and then try a dump and I don't really want to go through that effort ;-> Thanks, Josh -- "Listen: We are here on Earth to fart around. Don't let anybody tell you any different!" - Kurt Vonnegut Josh Burroughs [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Importing data from MS SQL 7 to MySQL
Hello, I am trying to transfer my Microsoft SQL Server to a MySQL Server running under Linux. Until now I have tryied the following methods. I installed DBtools 1.0.8 in a third machine running Win98 and connected to the MySQL server (Linux). I tried to import data from the Microsoft SQL server : 1) Directly from the Microsoft SQL Server through ODBC connection 2) From an Access 2000 .mdb file where I have previously imported data from the Microsoft SQL Server However both ways don't seem to import all the tables, constrains, procedures, etc. Is there any tool I can use to migrate MS SQL with MySQL??? Thank you in advance Konstantinos Katsoridis Webmaster Net One SA [EMAIL PROTECTED] + 30 (1) 68.20.240 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Importing data from remote server..
I'm in the process of moving SQL data from one server to another (Linux) - The tables on the new DBase server are not identical to the old - Is there a way to import only specified fields data from the old to the new ? Thanks in advance, Regards, MB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Importing data from Outlook Express
I'm looking for Outlook Express API's, I want to import data from a .dbx file (archive outlook) into a MySql table. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: importing data from FileMaker Pro to MySQL
Hi, > I have to import Datas From FileMaker Pro to MySQL . > > Unfortunately, the FileMaker web site is speaking about plugins to export > databases, but the access page gives error 500 (!). Plug-ins? You don't need any plug-ins to export data to MySQL. The only plug-in for Filemaker I've ever heard of concerning MySQL is one that allowed you to access MySQL data through Filemaker. > Then, I try this mailling list, and there is my question : > > is there any defined tool to import datas into MySQL Databases, that may also > read the FileMakerPro Format, or any standard CSV format ? MySQL will import data from CSV format quite happily, with no extras required. > I thought about developping something myself, in Perl (quite easy to use ...), > but there is also no Perl module avaliable for FileMaker ... I would be extremely surprised if there was! I think you're getting into extremely muddy waters here. What you're trying to do is very simple. Just open your Filemaker database, export the fields you want to a tab-seperated text file, transfer that file to your MySQL server (make sure you convert Mac line breaks to UNIX too) and import the file using the LOAD DATA statement in the MySQL command-line client. Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
importing data from FileMaker Pro to MySQL
Hi, I have to import Datas From FileMaker Pro to MySQL . Unfortunately, the FileMaker web site is speaking about plugins to export databases, but the access page gives error 500 (!). Then, I try this mailling list, and there is my question : is there any defined tool to import datas into MySQL Databases, that may also read the FileMakerPro Format, or any standard CSV format ? I thought about developping something myself, in Perl (quite easy to use ...), but there is also no Perl module avaliable for FileMaker ... Thanks, Fred
importing data into a table with auto_increment
hi there, is there a problem with importing data into a table with an auto_increment field with a primary key, if the data contains values for the auto_increment field? I keep getting an error that it can't insert the value "1" more than once. However, all the values for the auto_increment field are unique (though not consecutive) - and if I create the table with the field as a normal int, then the import is successful and all values for the int field are indeed unique. I could not find anything in the manual describing this behaviour. I've also tried sending this post with the data file (small) and commands used but unfortunately it gets rejected by the anti-spam software and 3 attempts later I couldn't work out why, so I hope this is enough information thanks dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: importing data from excel
On Thu, 4 Jan 2001, TEXLID_SUPPORT wrote:
> Could anyone please let me know how I can transfer data from excel to
> a mysql database? If it is possible. The data is stored in an excel
> file on my PC. And I want to transfer the contents (about 500 records
> with 20 fields) of this excel file to a mysql table on a remote linux
> server.
Edd,
export from EXCEL to *.csv format. Then pipe it though the program below.
Depending on your data some additional quoting with sed might be needed.
Resulting TAB separated file is easily read with "load data infile". You
have to write the CREATE clause manually.
Thomas
/*
* csv - preprocess *.csv files
*
* AUTHOR: Thomas Spahni ([EMAIL PROTECTED])
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
*
*/
#include
#include
/*
* Files in *.csv format can be a pain to process with sed when they
* contain '\n' ( or ) characters within quoted
* data fields.
*
* This filter will go through the file and determine if a is
* part of the data or indicates the end of a record. Any
* found as data is converted to '\r' or and existing
* carriage-returns are deleted. At the same time this is a conversion
* from DOS to unix style.
*
* Functions performed:
* - field separating semicolons are replaced by
* - field delimiting semicolons are deleted
* - newlines within quoted data fields become '\r'
* - preexisting carriage returns are deleted
*
*/
int main (int argc, char *argv[])
{
int c;
/*
* quoted_data is a switch to indicate whether we are in a quoted
* data field or not. 0 = normal outside, 1 = within data field
*/
int quoted_data = 0;
/*
* hanging_quote is a switch to remember if we have already
* seen the first one of a pair of double quotes.
*/
int hanging_quote = 0;
while ((c = getchar()) != EOF) {
if ( c == '"' ) {
/*
* quoted_data status does not matter if it is a quote;
* this quote could be the beginning or the end of a
* data field or it is the beginning of a quote ""
*/
if ( hanging_quote ) {
/* this is definitely a complete quote now */
hanging_quote = 0;
putchar(c);
} else {
hanging_quote = 1; /* wait to see what follows */
}
} else {
/* not a quote character */
if ( quoted_data ) {
/* we are within a quoted data field */
if ( hanging_quote ) {
/* it's a single quote terminating quoted data */
quoted_data = 0;
hanging_quote = 0;
if ( c == ';' ) {
putchar('\t');
} else if ( c != '\r' ) {
putchar(c);
}
} else {
/* within quoted data, special rules apply */
if ( c == '\n' ) {
putchar('\r');
} else if ( c != '\r' ) {
putchar(c);
}
}
} else {
if ( hanging_quote ) {
/* it's a single quote starting quoted data */
quoted_data = 1;
hanging_quote = 0;
}
if ( c == ';' ) {
putchar('\t');
} else if ( c != '\r' ) {
putchar(c);
}
}
}
} /* wend */
fflush(stdout);
exit(EXIT_SUCCESS);
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
importing data from excel
Hi, Could anyone please let me know how I can transfer data from excel to a mysql database? If it is possible. The data is stored in an excel file on my PC. And I want to transfer the contents (about 500 records with 20 fields) of this excel file to a mysql table on a remote linux server. Thanks in advance for any possible help. Edd.
Importing data to Excell
I have a table in my database, witch have a column thas is float(12,3), when i try to make a filter width that column i get an error message that says "Column PRPRVEND1 can't be used in criteria". Can someone tell me how to solve this problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
