Re: Import file into MySQL Database..
On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
-Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- 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: Import file into MySQL Database..
The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... Now, I went through on a few of them and added \n to the end of the line, then tried to load the file again with the LINES TERMINATED BY \n and it looks like it went just fine for the ones I did that too... So now I need to add that to the rest... I could have sworn I had done this before and it didn't work though On Aug 9, 2007, at 11:22 AM, Jerry Schwartz wrote: Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- 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/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
Doesn't that (the trailing comma) depend upon whether or not you want the default value for the (missing) field, as opposed to or 0 used for empty fields? Either way, you are right - you should be able to import the data. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 11:29 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import file into MySQL Database..
If you can import your excel doc into MS Access I'd suggest: http://www.mysql.com/products/tools/migration-toolkit/ I just started using and love it - easy intutitive GUI tool for importing data into nySQL databases. Good luck. -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 1:15 PM To: mysql@lists.mysql.com Subject: Import file into MySQL Database.. Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
A... the one thing I can't do... I don't have Access to well... Access... :) Did some more testing, made a new table and matched the field names, now it will load it without any errors, it's just only importing the first row... Not the rest of the 934 records... On Aug 8, 2007, at 1:20 PM, Stephen Sunderlin wrote: If you can import your excel doc into MS Access I'd suggest: http://www.mysql.com/products/tools/migration-toolkit/ I just started using and love it - easy intutitive GUI tool for importing data into nySQL databases. Good luck. -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 1:15 PM To: mysql@lists.mysql.com Subject: Import file into MySQL Database.. Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Hi. On Wednesday 08 August 2007 18:39, Jason Pruim wrote: Did some more testing, made a new table and matched the field names, now it will load it without any errors, it's just only importing the first row... Not the rest of the 934 records... You are using ENCLOSED BY '' in your SQL, which I have had cause hassle with excel generated csv files. Basically, excel will only wrap field values in double quotes if the value contains the field delimiter. This caught me out, although I seem to remember I did get an error. Try removing the ENCLOSED BY anyway, it's optional. Another thing to watch for is column headers in your input file that may be triggering column constraints. You can get past this by using the IGNORE n LINES syntax in your command. I usually specify the table column names too - I have happily loaded up csv files from excel with something like the following: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE MyTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (column1,column2,column_etc); Hope this helps. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
First line of my .csv file is: First,Last,Add1,Add2,City,State,Zip,Date,Xcode,Reason DESCRIBE is: mysql describe test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | First | varchar(20) | YES | | NULL| | | Last | varchar(20) | YES | | NULL| | | Add1 | varchar(50) | YES | | NULL| | | Add2 | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(10) | YES | | NULL| | | Zip| varchar(20) | YES | | NULL| | | XCode | varchar(20) | YES | | NULL| | | Reason | varchar(50) | YES | | NULL| | | Date | varchar(20) | YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.09 sec) I've also tried adding the filed names at the end of my load data command but that didn't help... As it sits right now this is the command I'm attempting to use: mysql LOAD DATA LOCAL INFILE '/volumes/raider/aml.master. 8.6.07.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; Which displays this: Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 if I remove the IGNORE 1 LINES; from the end then I get this added into the table: A. DREW | Last | Add1 | Add2 | City | State | Zip | Date | Xcode | Reason Which is a combination of the first address and the column names. On Aug 8, 2007, at 3:34 PM, Gary Josack wrote: Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
I believe that excel files generally terminate lines with '\r\n' and if you use terminated by '\n' it will cause this behavior. so try it with lines terminated by '\r\n' or get TextPad or a similar editor that can save the file as unix platform that uses the same line terminators that MySQL expects by default and leave out the optional terminated by cj On 8/8/07, Jason Pruim [EMAIL PROTECTED] wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Jason Pruim wrote: First line of my .csv file is: First,Last,Add1,Add2,City,State,Zip,Date,Xcode,Reason DESCRIBE is: mysql describe test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | First | varchar(20) | YES | | NULL| | | Last | varchar(20) | YES | | NULL| | | Add1 | varchar(50) | YES | | NULL| | | Add2 | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(10) | YES | | NULL| | | Zip| varchar(20) | YES | | NULL| | | XCode | varchar(20) | YES | | NULL| | | Reason | varchar(50) | YES | | NULL| | | Date | varchar(20) | YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.09 sec) I've also tried adding the filed names at the end of my load data command but that didn't help... As it sits right now this is the command I'm attempting to use: mysql LOAD DATA LOCAL INFILE '/volumes/raider/aml.master.8.6.07.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; Which displays this: Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 if I remove the IGNORE 1 LINES; from the end then I get this added into the table: A. DREW | Last | Add1 | Add2 | City | State | Zip | Date | Xcode | Reason Which is a combination of the first address and the column names. On Aug 8, 2007, at 3:34 PM, Gary Josack wrote: Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
