Problem attempting to use load data into
Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/ elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:29 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 2:01 PM, Rob Wultsch wrote: On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. Hi Rob, Where would I set that? I tried to add it to the load data infile line and it didn't like that... Should I try it before I do the indata? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 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: Problem attempting to use load data into
Hi again everyone, After taking the advice of someone offlist I tried the IGNORE 1 LINES and that didn't help... Same result. I've tried a tab delimited file, and a comma separated file. Same result with both. Any other ideas? :) On Apr 14, 2008, at 1:29 PM, Jason Pruim wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/ raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 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: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:33 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P I don't know you from Adam, you insignificant little cur! ;-P Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 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: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 4:37 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. Just to complete the archives, This did fix it. Make sure you don't try and put literal tab values \t and new line values \n into your data and it should work just fine! So thank you Dan for your help! And everyone else as well! -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 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]
