CSV Import Options

[cavey]

bpat1434 recommended I post this here...

I have a small (600kb) CSV file with like 5000 records, 20 fields each.
I need to import it into a MySQL database just one time, but phpMyAdmin
is being a pain in the butt. I keep getting this error:

Invalid field count in CSV input on line 802

Although every time I run the import it is a different line. I can login out that it is running through that record and expecting 20 fields and only finding > 20 so it throws an error, but how do I fix that?

Also, I did actually
get it to run once and it said 5000 queries were ran, but when I click browse
there are only 2114 records, where did they all go?

I guess my question is, is there an easier way to import this file? I only need to do it
this once so there's no use for a huge NASA front end, and i don't want to spend a week
setting up and learning to use a GUI for a split second import. Ideas? I have root shell access to the Linux server but I am not that comfortable with the command line to go that route.

This is for a part of the subscription database for a national magazine, if anyone can help me I'll put your name in ink 😃

[bpat1434]

See... I told you you could post 😛

Hmm... for my name in ink, I think I'll offer this:

Double-check that your new-line characters are \n not \r as that can seem to have an issue with CSV files....

Also, the command to run after you upload the csv file to your host would be:

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv'
INTO TABLE `table_name`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(`field1`, `field2`, `field3`, ... `field20`);

Alternatively, you could use a quick PHP script to break the CSV into an array, then loop through that to construct a bunch of INSERT statements, and execute them.... something like:

<?php
$breaker = "\n";
$table = 'table_name';
$fields = array('field1', 'field2', 'field3', 'field4', 'field5', /* ... */ 'field20');
$conn = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('my_database', $conn) or die(mysql_error());

$csv = file_get_contents('/path/to/file.csv');
$lines = explode($breaker, $csv);
$query = "INSERT INTO `$table` ('" . implode(", '", $fields) . "') VALUES ";
$i=0;
foreach($lines as $line)
{
  if($i>0 && ($i%100 == 0))
  {
    $query = substr($query, 0, -1); // Get rid of trailing comma
    $query .= ";
INSERT INTO `$table` ('" . implode("', '", $fields) . "') VALUES ";
  }

  $values = explode(',', $line);
  $query .= "
('" . implode("', '", $values) . "'),";
}
mysql_query($query) or die(mysql_error());
if(mysql_affected_rows() != count($lines))
  die('MySQL Insert failed to insert all entries.');
else
  echo 'Insert successful!';
?>

Now, obviously the CLI is much faster.....

[cavey]

Thanks for the help, I'll try these first thing tomorrow...

There's no "/n" breaks or anything, it looks like this but there's like 5000 records.

Following example record edited, rest deleted, as they looked like they could possibly contain actual personal data -- NogDog

Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;

Also, any idea why only some of the records are inserted? I didn't select Ignore Duplicates, and there's no reason for only like 2135 to insert but that's all I get. If I do the 200 record test file I only get 102 records inserted. Ideas on that?

[bpat1434]

Whoa... there's no line-breaks? They're only broken by ";"?

And as for inserting many rows but not all, if your file looks like:

Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;
Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;Paid Subscription,715010,9,14,,John,Doe,123 Main St.,,Smallville,TN,99999,United States,555-555-2177,,,,,,Active,NULL;

Then MySQL will only break on new line characters ("\n"). This breaking would give you 7 valid inserts, and then on the 8th it would fail because there's no break between "NULL;" and "Paid Subscription".

Do a sanity check on your CSV file. Make sure that each line has 20 elements (or 19 commas) and is ended by a new line (or even just the same set of characters that doesn't appear anywhere else). Then use that as your splitter for the MySQL CLI query.

[cavey]

As far as the data I posted, it's all fake.... I selected 200 random first names, combined them with 200 random last names, and swapped out random street addresses and zip codes, emails etc...

Here is what my CSV file looks like: each line is like this:

Paid Subscription,715012,9,14,,Kyle,Hutson,511A W. Sherman,,Woodburn,OR,97023,United States,503-358-0042,,,,,,Active,Nothing

They are all on their own line but there is no line break element at the end, it doesn't add one... I found a csv to sql converter that works but since there is no line break, it just errors on line one with a field mismatch... it doesn't give me the option to select a line break character, so should I just add a specific one nd try that? Maybe \n or something?

[cavey]

There are 21 fields in the database and 21 in the CSV file (columns) as i created that CSV file by exporting the database initially, only changing the data and not the header row. Here is the error I get:

#1054 - Unknown column 'subtype,id,startissue,endissue,company,firstname,lastname,addres' in 'field list'

Also, if I go to the SQL tab on phpMyAdmin and try to insert just one record, it fails as well... here is that query:

INSERT INTO subscriber_data (subtype,id,startissue,endissue,company,firstname,lastname,address1,address2,city,state,zipcode,country,phone,email,payment,subcode,rencode,source,status,notes) VALUES ('Paid Subscription,715000,1,14,,Jason,Kruger,2255 Devos St.,,S. Lake Tahoe,CA,95603,United States,530-416-7920,,,,,,Active,');

I see that last comma, there is one field beyond that but it is being ignored??? Can I add a manual line break in there somehow?

[cavey]

Okay I give up LOL... I have tried every single option available and this just will not work. I cannot even insert one record thru phpMyAdmin it gives me that error above. What am I doing wrong?

[bpat1434]

Wait!!! Don't give up!!!

Look at your SQL query!!!

back-ticks denote column names. So the your SQL line:
[code]subtype,id,startissue,endissue,company,firstname,lastname,address1,address2,city,state,zipcode,country,phone,email,payment,subcode,rencode,source,status,notes`[/code]
Is looking for a column with the title:

subtype,id,startissue,endissue,company,firstname,lastname,address1,address2,city,state,zipcode,country,phone,email,payment,subcode,rencode,source,status,notes

That's the error you're getting. Your column setup should be something like thsi:

`subtype`,`id`,`startissue`,`endissue`,`company`,`firstname`,`lastname`,`address1`,`address2`,`city`,`state`,`zipcode`,`country`,`phone`,`email`,`payment`,`subcode`,`rencode`,`source`,`status`,`notes`

Notice the difference?

The same thing goes for your VALUES() statements.... each column should be encapsulated with single-quotes.

'Paid Subscription',715000,1,14,NULL,'Jason','Kruger','2255 Devos St.',NULL,'S. Lake Tahoe','CA',95603,'United States','530-416-7920',NULL,NULL,NULL,NULL,NULL,'Active',NULL

Hope that helps you get through this in time!!

[cavey]

Yup, it helped!

I wrote a quick script that dumps the CSV data into an SQL query, and adds the tick marks and single quotes and I just inserted 5333 records successfully!!! Kind of like this:

$query = "INSERT INTO $subscriber_data (";
$firstpass      = true;
foreach($column as $column)	
{
	if(!$firstpass)
		$base_query .= ", ";
	$column_name = trim($column);
	$query .= "$column";
	$firstpass = false;
}
$base_query .= ") ";

Now, look for perplexing posts from me about my wonky pagination and using sessions to determine the color theme the user has stored for their account!

I'll run your name in the next issue of my magazine since you're the only one who helped LOL... hit me up on Yahoo

[bpat1434]

haha, glad to hear you got it working!!

Please don't forget to mark this thread as resolved 😉

I'll keep my eyes open for your posts..... but please please please remember to search first 🙂

[cavey]

Ya ya.... I did get my color themes working. My only major concern now is my pagination code. It works but there is one major gliche, I just need to decide how to explain it, it is kinda complicated. Time for 4th meal though (Taco Bell) and go home to see my kids. I'll post up tomorrow. -t

[bpat1434]

4th meal? pfft... I'm on like.... wait.... I knew I forgot to do something today!!!