Mysqlimport complex question

[Scott Hamm]

/*
OBJECTIVE:
INPUT:
 E524541015.txt:20061004|,535,999|Scot|Hamm|,410|||101 Walter
Rd|Dover|MD|76709|,041|

 WHERE error (,###) can be in any fields *AFTER* the first | char

DESIRED OUTPUT:
 filename: E524541015.txt
 ord:  20061004
 error:  535
 error1:  999
 error2:  410
 error3:  041
 error4:
 error5:

Explanation:
 filename: 14 chars before :
 ord:  8 chars after :
 error#:  3 chars (first error MUST NOT be null) after ,

Table specifications:
*/

create table all_files (
ID int auto_increment primary key,
filename varchar(255) not null,
ord int(8) not null,
error int(3) not null,
error1 int(3),
error2 int(3),
error3 int(3),
error4 int(3),
error5 int(3),
unique key(filename,ord),
index(filename)
);


/*

Been trying to get mysqlimport to use these characters to no avail, how do I
get around to it?

*/

Re: Mysqlimport complex question

[Christian Hammers]

On 2006-10-04 Scott Hamm wrote:
 OBJECTIVE:
  INPUT:
   E524541015.txt:20061004|,535,999|Scot|Hamm|,410|||101 Walter
 Rd|Dover|MD|76709|,041|
... 
 Been trying to get mysqlimport to use these characters to no avail, how do
 I get around to it?

I can't answer your question but think that you're following the wrong
approach instead of the simple rule: 
 Use SQL for storing and calculating and a script language for parsing and 
formatting.
It's quite easy with regular expressions (they can be made more readable
with /x):

cat input.txt | perl -n -e
  '/^(.{14}):(.{8})\|,(\d+),(\d+)\|[^\|]+\|[^\|]+\|,(\d+)\|.*\|,(\d+)\|$/ 
  print INSERT INTO t VALUES (\$1\,$2,$3,$4,$5,$6);\n;'

The output is ready to be piped into mysql:
  INSERT INTO t VALUES (E524541015.txt,20061004,535,999,410,041);

bye,

-christian-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]