fields optionally enclosed by problem

[LinuxGold]

I included --fields-optionally-enclosed-by="" in the mysqlimport to include everything including the "," character inside fields to no avail.  Here are the detailed result of my attempt.

 

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --ignore-lines=1 --user=root --password shark "c:\documents and settings\shamm\desktop\result.csv"

 

Enter password: **********

 

mysqlimport: Error: Row 48 was truncated; it contained more data than there were input columns, when using table: result

 

 

Line 48:

 

"48", "14.729606", "10.1.1.22", "10.182.167.209", "TCP", "pop3 > 1111 [sYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460"

 

 

 

mysql> desc result;

+-------------+------------------+------+-----+---------+-------+

| Field      | Type            | Null | Key | Default | Extra |

+-------------+------------------+------+-----+---------+-------+

| No          | int(10) unsigned | NO  |    |        |      |

| Time        | text            | NO  |    |        |      |

| Source      | text            | NO  |    |        |      |

| Destination | text            | NO  |    |        |      |

| Protocol    | text            | NO  |    |        |      |

| Info        | text            | NO  |    |        |      |

+-------------+------------------+------+-----+---------+-------+

6 rows in set (0.03 sec)

[fenway]

sounds like an escaping issue.

[LinuxGold]

mysqlimport --fields-terminated-by="," --fields-enclosed-by="\"" --fields-escaped-by=, --lines-terminated-by="\r\n" --ignore-lines=1 --user=

root --password shark "c:\documents and settings\shamm\desktop\result.csv"

Enter password: **********

mysqlimport: Error: Row 1 doesn't contain data for all columns, when using table: result

[fenway]

Strange... what does the documentation say? I haven't encountered this before... can't you use single quotes for that attribute value?

[LinuxGold]

There is also ' in fields as well.

 

Secondly, the csv is generated by Shark program on windows machine.

 

 

[btherl]

Does it matter that the fields are seperated by comma space rather than plain comma?

[LinuxGold]

Does it matter that the fields are seperated by comma space rather than plain comma?

 

There is also comma space inside the fields i.e. (record 36671):

 

"36671", "2383.298312", "10.182.167.209", "10.182.15.1", "SPOOLSS", "ClosePrinter request, OpenPrinterEx(\\Motchrtsrv01\Andreavich's Printer)"

 

 

[fenway]

Don't you have to put each of those parameter values in quotations?

[LinuxGold]

Don't you have to put each of those parameter values in quotations?

 

Wireshark program created that csv file resulting into 390 thousand rows, I have no other option but to try to import it into mysql.

[fenway]

I mean in the import spec.

[LinuxGold]

Tried this -- new problem:

 

 

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by="," --lines-terminated-by="r\n" --ignore-lines=1 --user=root --password shark

"c:\documents and settings\shamm\desktop\result.csv"

 

Enter password: **********

 

shark.result: Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

 

result info:

mysql> desc result;

+-------------+------------------+------+-----+---------+-------+

| Field      | Type            | Null | Key | Default | Extra |

+-------------+------------------+------+-----+---------+-------+

| No          | int(10) unsigned | NO  |    |        |      |

| Time        | text            | NO  |    |        |      |

| Source      | text            | NO  |    |        |      |

| Destination | text            | NO  |    |        |      |

| Protocol    | text            | NO  |    |        |      |

| Info        | text            | NO  |    |        |      |

+-------------+------------------+------+-----+---------+-------+

6 rows in set (0.14 sec)

 

 

[shoz]

Perhaps if you post the file (a truncated/altered version if you don't want to post it all) as an attachment someone may be able to figure out the required options that you need. Ensure that the file (truncated or not) that you post gives the same problems.

[LinuxGold]

Here it is.

 

[attachment deleted by admin]

[shoz]

The file posted imports for me. The only difference is the escaped " but that may not apply to you. This is in linux btw. Try downloading the file from here and verify that it doesn't import for you.

 

mysqlimport -u username -p --fields-optionally-enclosed-by="\"" --fields-terminated-by="," --lines-terminated-by="\r\n" --ignore-lines=1 dbname /path/to/test.csv

Records: 161  Deleted: 0  Skipped: 0  Warnings: 24

[LinuxGold]

 

C:\Documents and Settings\shamm>mysqlimport -u root -p --fields-optionally-enclosed-by="\"" --fields-terminated-by="," --lines-terminated-by="\r\n" --ignore-lines=1 shark "c:\Documents and Settings\shamm\Desktop\result.csv"

Enter password: **********

mysqlimport: Error: Row 48 was truncated; it contained more data than there were input columns, when using table: result

 

If you want to privmsg me your email address, I will be more than glad to email you the whole file.

[shoz]

I imported the file using the LOAD DATA syntax and looked at the warnings and I see the same error. The file imports but the columns with a comma are truncated as stated. You should be able to import the file using the -f option to have mysqlimport continue the import even if errors are found.

 

The better solution would be to If possible, have "shark" create the csv using a delimiter such as "~" or "`" for the fields. If not you can use this code snippet to replace all "," within the fields with  "!c!" and then you can replace them using SQL afterwards.

 

<?php
$filePath = '/path/to/file.csv';
$outPath = '/path/to/newfile.csv';

$str = file_get_contents($filePath);
$str = preg_replace('/,(?=[^"]*"(?:\r\n|,[ ]*"))/m', '!c!', $str);
file_put_contents($outPath, $str);
?>

 

UPDATE table SET columname = REPLACE(columnname, "!c!", ",");

 

EDIT: Added semicolons to the end of the first two lines.

[LinuxGold]

So it's a MySQL bug thing?  I'll try that php code.  Thanks.

 

I imported the file using the LOAD DATA syntax and looked at the warnings and I see the same error. The file imports but the columns with a comma are truncated as stated. You should be able to import the file using the -f option to have mysqlimport continue the import even if errors are found.

 

The better solution would be to If possible, have "shark" create the csv using a delimiter such as "~" or "`" for the fields. If not you can use this code snippet to replace all "," within the fields with  "!c!" and then you can replace them using SQL afterwards.

 

<?php
$filePath = '/path/to/file.csv';
$outPath = '/path/to/newfile.csv';

$str = file_get_contents($filePath);
$str = preg_replace('/,(?=[^"]*"(?:\r\n|,[ ]*"))/m', '!c!', $str);
file_put_contents($outPath, $str);
?>

 

UPDATE table SET columname = REPLACE(columnname, "!c!", ",");

 

EDIT: Added semicolons to the end of the first two lines.

[shoz]

So it's a MySQL bug thing?

Well I don't think it's a "bug". MYSQL expects that the file will have ","  escaped within the fields.

 

The preg_replace could have replaced the ',' with '\,' but because the shark app doesn't escape "\" either it doesn't really make sense to. Although, I suppose a more robust solution would be to have the script escape all the relevant characters that the application doesn't and import the file that way.

 

The preg_replace line could be the following in that case

$str = preg_replace('/(,|\\\|\r\n)(?=[^"]*"(?:\r\n|,[ ]*"))/m', '\\\$1', $str);

 

You then wouldn't be required to UPDATE the table because everything should be imported correctly.

[shoz]

The preg_replace line has been modified above.

[LinuxGold]

Ok.  I'm drinking Pina Colada, some Budweiser and other alcoholic things...  Will try it when I'm sober. 

 

Appreciate your time.