LinuxGold
-
Posts
13 -
Joined
-
Last visited
Never
Posts posted by 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.
-
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)
-
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.
-
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)"
-
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]
fields optionally enclosed by problem
in MySQL Help
Posted
Ok. I'm drinking Pina Colada, some Budweiser and other alcoholic things... Will try it when I'm sober.
Appreciate your time.