Error Inserting into Table

[Ajak]

(MySQL 4.1.7 using phpMyAdmin 2.6.0-pl3)

Here's a file called info.csv:

"e-mail","Subscribe_to_Newsletter ",""
"ajak@myway.com","on",""
"anthony_knight@hotmail.com","off",""

I created a table called mail, with 2 columns (e-mail Subscribe_to_Newsletter)

Then ran this query:

INSERT INTO mail (email, Subscribe_to_Newsletter)
VALUES ("ajak@myway.com","on",""
"anthony_knight@hotmail.com","off","");

But got this error message:

Error

SQL-query:

INSERT INTO mail( email, Subscribe_to_Newsletter )
VALUES (
"ajak@myway.com", "on", "" "anthony_knight@hotmail.com", "off", ""
)

MySQL said: Documentation
#1136 - Column count doesn't match value count at row 1

What's wrong with the query & why does the CSV file use double quotation marks?.

 

[vbkris]

>>INSERT INTO mail (email, Subscribe_to_Newsletter)
u have set the query to accept only values for 2 columns

>>VALUES ("ajak@myway.com","on",""
"anthony_knight@hotmail.com","off","");


this is wrong, in mysql u have to set the values within '' and that too there can be only 2 field.

e.g.:
INSERT INTO mail (email, Subscribe_to_Newsletter) values('asd@asd.con','Y')



CSV files use "" to hold values that have special characters.
e.g.:
Normal CSV
1,2,3

If first column has , itself as a value then:
"1,",2,3

some CSV programs will generate ALL values using ""...


Known is handfull, Unknown is worldfull

 

[TonyGroves]

It should have been:
[tt]
INSERT INTO mail (email, Subscribe_to_Newsletter)
VALUES
("ajak@myway.com","on"),
("anthony_knight@hotmail.com","off");
[/tt]

CSV files can use any (unambiguous) character such as a comma to separate fields, and you can also enclose fields using a different character such as double-quotes. The traditional format is commas for separation, and double-quotes to enclose values. My own preference however is simply to use tabs for separation, and no enclosing character. You specify the characters used when you create the file, and when you import it.

By the way, do you know that you can import a CSV file directly into a MySQL table? See

http://dev.mysql.com/doc/mysql/en/load-data.html

.

 

[vbkris]

>>("ajak@myway.com","on"),

what is this method called? never done it before...


Known is handfull, Unknown is worldfull

 

[TonyGroves]

vbkris:

Don't know what you mean there ...

 

[vbkris]

INSERT INTO mail (email, Subscribe_to_Newsletter)
VALUES
("ajak@myway.com","on"),
("anthony_knight@hotmail.com","off");

how can this sql work? there are 2 columns but 4 values (2 in each paranthesis) used. What does the () mean? r u inserting multiple rows using just one sql stmt???


Known is handfull, Unknown is worldfull

 

[TonyGroves]

That statement inserts 2 records. Each record is enclosed in brackets and separated by commas. You can insert any number of records in one statement that way.

 

[vbkris]

ah thanks, so " can be used in mysql huh? i thought only ' is allowed, never struck me...

Known is handfull, Unknown is worldfull

 

[Ajak]

Thanks for all the advice, the problem was that there is an empty column at the end of each record & no line break, so I was trying to insert 6 columns into 2. I added an extra column called blank & did this to get it to work:

INSERT INTO mail( email, Subscribe_to_Newsletter,blank)
VALUES (
"ajak@myway.com", "on","" ),
("anthony_knight@hotmail.com", "off", ""
);

However as I am expecting to capture, hundreds maybe thousands of email addresses, I can't edit each one. I will have to inform Magic Downloads, that their CSV file is not formatted correctly.

 

[TonyGroves]

There's nothing wrong with the formatting there. As I said in my main response, commas and double-quotes are normal for CSV files, and the data can be easily imported into a MySQL table.

 

[Ajak]

I have no problem with the commas & double-quotes but the 2 records seem to run into each other, how would MySql know where one stopped & the other started?.

I don't have shell access to my hosting account, I tried importing the file as is, using phpMyAdmin & got a column mismatch error.

 

[TonyGroves]

OK. Are you saying you have two records on each line? Is it always two records per line?

If so then you could:

1. Set up your table to hold two addresses and flags per record;
2. Import the CSV file;
3. Run a query to select all the second addresses and flags and insert them into new records as the first address and flag;
4. Drop the second address and flag columns;
5. Voilà.

If you need help with that, let us know.

 

[Ajak]

No they are all like that, if there were 10 records there would be 30 columns ie:

"e-mail","Subscribe_to_Newsletter ",""
"ajak@myway.com","on",""
"anthony_knight@hotmail.com","off",""
"abcd@myway.com","on",""
"wxyz@hotmail.com","off",""

etc. etc., because there is no line break, MySQL sees one continuous line.

What I am trying to do is, allow users to download a file after entering an email address. then subscribe to a newsletter by ticking a check-box. The addresses should then be captured into a mailing list, or at least presented in such a way that they could imported/exported/copied & pasted to a mailing program.

Do you know of any open-source, free or cheap software that would do this? Preferably not a shopping cart.

 

[TonyGroves]

You still haven't made it clear exactly what format your file is in, but if the end of a record is indicated by a blank field followed by one or more spaces, then you could use something like:
[tt]
LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE mail
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY ',"" '
[/tt]
The table should be created with only two columns.

Alternatively, you could use a text editor to insert line endings into the file, using regular-expression search and replace.

 

[Ajak]

I don't know what you mean by format, the file is exported from Magic Downloads statistics page.

If you go here:

http://reamdaysoft.com/magic-downloads/demo.html

you can get a hands on demo of the process.