RE: What's the best way to INSERT a long list of email addresses and names into a TABLE?

2006-09-13 Thread Jerry Schwartz 
I'm not sure I understand where the other field data (that you say you have
to enter manually) is coming from, but what I often do when confronted with
data that needs to be massaged before entry is put it in an Excel
spreadsheet and use a formula to build the MySQL statements.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

-Original Message-
From: axis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 12, 2006 9:10 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: What's the best way to INSERT a long list of email addresses and
names into a TABLE?

Hi,

I have a long list of email addresses, and names in a text file in csv
format, and I want to know what's the best approach to quickly INSERT
them into my customers TABLE.
Initially I thought it could be easy to just add SQL INSERTs to the
database dump but, the problem is I have to manually add some fields and
that can be a tedious and slow process, is there a better approach?

This is the table structure:

CREATE TABLE `customers` (
  `customers_id` int(11) NOT NULL auto_increment,
  `purchased_without_account` tinyint(1) unsigned NOT NULL default '0',
  `customers_gender` char(1) NOT NULL default '',
  `customers_firstname` varchar(32) NOT NULL default '',
  `customers_lastname` varchar(32) NOT NULL default '',
  `customers_dob` datetime NOT NULL default '-00-00 00:00:00',
  `customers_email_address` varchar(96) NOT NULL default '',
  `customers_default_address_id` int(11) default NULL,
  `customers_telephone` varchar(32) NOT NULL default '',
  `customers_fax` varchar(32) default NULL,
  `customers_password` varchar(40) NOT NULL default '',
  `customers_newsletter` char(1) default NULL,
  `customers_group_name` varchar(27) NOT NULL default 'Retail',
  `customers_group_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`customers_id`),
  KEY `purchased_without_account` (`purchased_without_account`)
) TYPE=MyISAM AUTO_INCREMENT=60 ;

I only have Name and Email in the text file.

Sincerely,

Axis

--



--
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]

What's the best way to INSERT a long list of email addresses and names into a TABLE?

2006-09-12 Thread axis 

Hi,

I have a long list of email addresses, and names in a text file in csv 
format, and I want to know what's the best approach to quickly INSERT 
them into my customers TABLE.
Initially I thought it could be easy to just add SQL INSERTs to the 
database dump but, the problem is I have to manually add some fields and 
that can be a tedious and slow process, is there a better approach?


This is the table structure:

CREATE TABLE `customers` (
 `customers_id` int(11) NOT NULL auto_increment,
 `purchased_without_account` tinyint(1) unsigned NOT NULL default '0',
 `customers_gender` char(1) NOT NULL default '',
 `customers_firstname` varchar(32) NOT NULL default '',
 `customers_lastname` varchar(32) NOT NULL default '',
 `customers_dob` datetime NOT NULL default '-00-00 00:00:00',
 `customers_email_address` varchar(96) NOT NULL default '',
 `customers_default_address_id` int(11) default NULL,
 `customers_telephone` varchar(32) NOT NULL default '',
 `customers_fax` varchar(32) default NULL,
 `customers_password` varchar(40) NOT NULL default '',
 `customers_newsletter` char(1) default NULL,
 `customers_group_name` varchar(27) NOT NULL default 'Retail',
 `customers_group_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`customers_id`),
 KEY `purchased_without_account` (`purchased_without_account`)
) TYPE=MyISAM AUTO_INCREMENT=60 ;

I only have Name and Email in the text file.

Sincerely,

Axis

--



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: What's the best way to INSERT a long list of email addresses and names into a TABLE?

2006-09-12 Thread Don 
Presuming your csv is set up in the structure of your table - this may help

BULK INSERT tablename
   FROM 'C:\filelocation\my_data.csv'
   WITH 
  (
 FIELDTERMINATOR =',',
 ROWTERMINATOR ='\n'
  ) 

I am going through a SQL class and this is the method we are using to bulk
insert into our tables.  Works like a champ.

Don

-Original Message-
From: axis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 12, 2006 9:10 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: What's the best way to INSERT a long list of email addresses and
names into a TABLE?

Hi,

I have a long list of email addresses, and names in a text file in csv
format, and I want to know what's the best approach to quickly INSERT them
into my customers TABLE.
Initially I thought it could be easy to just add SQL INSERTs to the database
dump but, the problem is I have to manually add some fields and that can be
a tedious and slow process, is there a better approach?

This is the table structure:

CREATE TABLE `customers` (
  `customers_id` int(11) NOT NULL auto_increment,
  `purchased_without_account` tinyint(1) unsigned NOT NULL default '0',
  `customers_gender` char(1) NOT NULL default '',
  `customers_firstname` varchar(32) NOT NULL default '',
  `customers_lastname` varchar(32) NOT NULL default '',
  `customers_dob` datetime NOT NULL default '-00-00 00:00:00',
  `customers_email_address` varchar(96) NOT NULL default '',
  `customers_default_address_id` int(11) default NULL,
  `customers_telephone` varchar(32) NOT NULL default '',
  `customers_fax` varchar(32) default NULL,
  `customers_password` varchar(40) NOT NULL default '',
  `customers_newsletter` char(1) default NULL,
  `customers_group_name` varchar(27) NOT NULL default 'Retail',
  `customers_group_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`customers_id`),
  KEY `purchased_without_account` (`purchased_without_account`)
) TYPE=MyISAM AUTO_INCREMENT=60 ;

I only have Name and Email in the text file.

Sincerely,

Axis

-- 



--
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]

What's the best way to INSERT a long list of email addresses and names into a TABLE?

2006-09-12 Thread axis 

I have a long list of email addresses, and names in a text file in csv
format, and I want to know what's the best approach to quickly INSERT
them into my customers TABLE.
Initially I thought it could be easy to just add SQL INSERTs to the
database dump but, the problem is I have to manually add some fields and
that can be a tedious and slow process, is there a better approach?

This is the table structure:

CREATE TABLE `customers` (
`customers_id` int(11) NOT NULL auto_increment,
`purchased_without_account` tinyint(1) unsigned NOT NULL default '0',
`customers_gender` char(1) NOT NULL default '',
`customers_firstname` varchar(32) NOT NULL default '',
`customers_lastname` varchar(32) NOT NULL default '',
`customers_dob` datetime NOT NULL default '-00-00 00:00:00',
`customers_email_address` varchar(96) NOT NULL default '',
`customers_default_address_id` int(11) default NULL,
`customers_telephone` varchar(32) NOT NULL default '',
`customers_fax` varchar(32) default NULL,
`customers_password` varchar(40) NOT NULL default '',
`customers_newsletter` char(1) default NULL,
`customers_group_name` varchar(27) NOT NULL default 'Retail',
`customers_group_id` int(11) NOT NULL default '0',
PRIMARY KEY (`customers_id`),
KEY `purchased_without_account` (`purchased_without_account`)
) TYPE=MyISAM AUTO_INCREMENT=60 ;

I only have Name and Email in the text file.

Sincerely,


Axis

--




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: What's the best way to INSERT a long list of email addresses and names into a TABLE?

2006-09-12 Thread Don 
I don't know; never tried it that way

  _  

From: axis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 12, 2006 9:37 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: What's the best way to INSERT a long list of email addresses
and names into a TABLE?


HTML_MESSAGE,MIME_HTML_ONLY autolearn=disabled version=3.1.4
X-Spam-Checker-Version: SpamAssassin 3.1.4 (2006-07-25) on farm03
X-NAS-Classification: 0 X-NAS-MessageID: 45965 X-NAS-Validation:
{9F3F1FB5-9CCB-44C4-8345-B1DFB7F0F848} Thank you for your quick reply, and
FROM only works with absolute paths?
Does it work with '../../Customer_Data.csv'?

Axis

On 9/12/2006 10:22:01 PM, Don ([EMAIL PROTECTED]) wrote: 
 Presuming your csv is set up in the structure of your table - this may 
 help 
 
 
 
 BULK INSERT tablename 
 
 FROM 'C:\filelocation\my_data.csv' 
 
 WITH 
 
 ( 
 
 FIELDTERMINATOR =',', 
 
 ROWTERMINATOR ='\n' 
 
 ) 
 
 
 
 I am going through a SQL class and this is the method we are using to 
 bulk 
 
 insert into our tables. Works like a champ. 
 
 
 
 Don 
 
 
 
 -Original Message- 
 
 From: axis [mailto:[EMAIL PROTECTED] 
 
 Sent: Tuesday, September 12, 2006 9:10 PM 
 
 To: [EMAIL PROTECTED] 
 
 Cc: mysql@lists.mysql.com