[fields optionally enclosed by problem]

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

 

Appreciate your time.

[fields optionally enclosed by problem]

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.

[fields optionally enclosed by problem]

 

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.

[fields optionally enclosed by problem]

Here it is.

 

[attachment deleted by admin]

[fields optionally enclosed by problem]

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)

 

 

[fields optionally enclosed by problem]

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.

[fields optionally enclosed by problem]

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)"

 

 

[fields optionally enclosed by problem]

There is also ' in fields as well.

 

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

 

 

[fields optionally enclosed by problem]

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

[fields optionally enclosed by problem]

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)

[Stored Procedure for list_zip_by_miles]

Ugh, delimiter got it.  *fixes my own eyes*
[code]
delimiter //
CREATE PROCEDURE find_zip_by_miles (
targetzip INT,
rangemiles INT
)
/*
*/
BEGIN
/* DECLARE @targetzip=int;
DECLARE @rangemiles int;*/
SET @targetzip=targetzip;
SET @rangemiles=rangemiles;
SELECT
C.ZipCode,
Zi.City,
Zi.State,
Round(C.Miles) AS 'Mile(s)'
FROM
(
SELECT
Lat_A,
Long_A,
Lat_B,
Long_B,
ZipCode,
(degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 AS 'Miles'
FROM
(
SELECT
CAST(latitude AS decimal(8,6)) AS Lat_A,
CAST(longitude AS decimal(8,6)) AS Long_A
FROM
zips
WHERE
`zip code`=@targetzip
) AS A,
(
SELECT
CAST(latitude AS decimal(8,6)) AS Lat_B,
CAST(longitude AS decimal(8,6)) AS Long_B,
`zip code` AS ZipCode
FROM
zips
) AS B
WHERE
(degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 <= @rangemiles
) AS C,
zips Zi
WHERE
Zi.`zip code`=C.ZipCode
ORDER BY
Round(C.Miles);
END
//
DELIMITER ;
CALL find_zip_by_miles (19943,20);
[/code]

[Stored Procedure for list_zip_by_miles]

Anyone know how to create stored procedure?  This is the bottleneck that I'm having right now.  Any url for rookie like me?

[Stored Procedure for list_zip_by_miles]

I am trying to create a procedure that lists a lists of zip codes within given miles and local zip code.  After reading manual online I am unable to understand how to create it.  I use mysql command line interface to create stored procedure.  How do I get around to it?  I want to do list_zip_by_miles (19943,20) as following with result afterwards:

[code]
SET @targetzip='19943';  /*Local Zip Code*/
SET @rangemiles='20';    /*Mile(s) range limit */
SELECT
C.ZipCode,
Zi.City,
Zi.State,
Round(C.Miles) AS 'Mile(s)'
FROM
(
SELECT
Lat_A,
Long_A,
Lat_B,
Long_B,
ZipCode,
(degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 AS 'Miles'
FROM
(
SELECT
CAST(latitude AS decimal(8,6)) AS Lat_A,
CAST(longitude AS decimal(8,6)) AS Long_A
FROM
zips
WHERE
`zip code`=@targetzip
) AS A,
(
SELECT
CAST(latitude AS decimal(8,6)) AS Lat_B,
CAST(longitude AS decimal(8,6)) AS Long_B,
`zip code` AS ZipCode
FROM
zips
) AS B
WHERE
(degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 <= @rangemiles
) AS C,
zips Zi
WHERE
Zi.`zip code`=C.ZipCode
ORDER BY
Round(C.Miles);
[/code]
[code]
+---------+----------------+----------+---------+
| ZipCode | City          | State    | Mile(s) |
+---------+----------------+----------+---------+
| 19943  | Felton        | Delaware |      0 |
| 19979  | Viola          | Delaware |      3 |
| 19980  | Woodside      | Delaware |      4 |
| 19934  | Camden Wyoming | Delaware |      6 |
| 19962  | Magnolia      | Delaware |      6 |
| 19946  | Frederica      | Delaware |      7 |
| 19952  | Harrington    | Delaware |      7 |
| 19954  | Houston        | Delaware |      8 |
| 19964  | Marydel        | Delaware |      10 |
| 19942  | Farmington    | Delaware |      10 |
| 19904  | Dover          | Delaware |      11 |
| 19901  | Dover          | Delaware |      11 |
| 19963  | Milford        | Delaware |      11 |
| 21636  | Goldsboro      | Maryland |      11 |
| 19902  | Dover Afb      | Delaware |      11 |
| 19953  | Hartly        | Delaware |      11 |
| 21640  | Henderson      | Maryland |      12 |
| 21639  | Greensboro    | Maryland |      12 |
| 21649  | Marydel        | Maryland |      12 |
| 19950  | Greenwood      | Delaware |      13 |
| 19955  | Kenton        | Delaware |      15 |
| 19960  | Lincoln        | Delaware |      15 |
| 21644  | Ingleside      | Maryland |      16 |
| 21660  | Ridgely        | Maryland |      16 |
| 21629  | Denton        | Maryland |      16 |
| 19941  | Ellendale      | Delaware |      17 |
| 21607  | Barclay        | Maryland |      17 |
| 19938  | Clayton        | Delaware |      18 |
| 19933  | Bridgeville    | Delaware |      19 |
| 21668  | Sudlersville  | Maryland |      19 |
| 21641  | Hillsboro      | Maryland |      20 |
| 19977  | Smyrna        | Delaware |      20 |
+---------+----------------+----------+---------+
[/code]