LinuxGold

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

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

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.

Here it is. [attachment deleted by admin]

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)

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

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

There is also ' in fields as well. Secondly, the csv is generated by Shark program on windows machine.

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

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)

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]

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

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]