Import CSV Question

[ThunderAI]

I have a csv file that I would like to import using the LOAD DATA command, the only problem is i can not determine the proper setting for import within PhpMyAdmin.  A typical line looks like this

 

1234,stuf,,more stuf,,,,birthdate,,more stuff

 

When i try to import it using the following line, it skips the blanks, but I really need the blanks in the database.

LOAD DATA LOCAL INFILE 'C:\\xampplite\\tmp\\phpC852.tmp' INTO TABLE `tbl_temp` FIELDS TERMINATED BY ',' ENCLOSED BY ',' ESCAPED BY ',' LINES TERMINATED BY '\r\n'# Affected rows: 40096

 

any other combination that i have tried only gives me NULL in each field.

 

Can anyone please help!

[jonsjava]

try removing the ENCLOSED BY. That is an optional field, and you're basically saying that each field is terminated by the same thing that encloses it.

[ThunderAI]

Unfortunately all that does is put a NULL value in every single field in the database. Ignoring the actual values as well.

 

Here is the import code:

 

$objconn_support2 = mysqli_connect("localhost", "---USER---", "---PASSWORD---", "---DATABASE---");

$sql_support2 = "LOAD DATA LOCAL INFILE '----FILE---' INTO TABLE `---TABLE---` FIELDS TERMINATED BY ',' ESCAPED BY ',' LINES TERMINATED BY '\r\n'# Affected rows: 40096";

if (mysqli_connect_errno()) {

// there was an error trying to connect to the mysql database

printf("connect failed: %s\n", mysqli_connect_error());

exit();

}

else {

//mysql_insert_id();

$objrs_support2 = mysqli_query($objconn_support2, $sql_support2) or die(mysqli_error($objconn_support2));

}

 

[jonsjava]

try using this format

LOAD DATA LOCAL INFILE '--FILE--'
INTO TABLE `--TABLE`--`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

[ThunderAI]

try using this format

LOAD DATA LOCAL INFILE '--FILE--'
INTO TABLE `--TABLE`--`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

 

That seems to work just great!, Whats the syntax to skip the first row in the CSV file?

[ThunderAI]

What about when one field in a csv looks like this.  I know which field it is, and it is always the same one.

 

,"blah, blah",

 

the field is one field; however, because of the import method it treats it as two. and then the result fields have " at the front of one firld and a " at the end of the other field.

 

I tried to OPTIONALY ", but then all of the cells are NULL when the results are imported.