1M is pretty small; we run at 16M, and we don't do anything in the way of
batch loading. But you probably don't have that access, so you'll have to
make sure your queries are smaller than 1M each.
Just break your query down into smaller batches, and run them sequentially.
I don't know how big the chunks can be, but if those records are
representitive of all your data, then you can do like 13,000 records per
query. I'd probably drop that down to a few thousand though, just to be
safe. Not like it's going to be any more expensive.
Cheers,
barneyb
> -----Original Message-----
> From: Mickael [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 15, 2004 9:17 AM
> To: CF-Talk
> Subject: Re: Import a Text file to MYSQL
>
> Hi,
>
> The is my problem my server does not allow my to use LOAD
> DATA INFILE so I made a text file that contains my insert
> information its called text12.txt
> the text file looks like this with the last row with out a comma
>
> 733513, '2002-11-12', '6:54:01 PM', 'Automatic Debtor status
> change to OUT.'),
> (733513, '2002-11-12', '6:54:01 PM', 'Automatic Debtor status
> change to OUT.'),
> (733513, '2002-11-12', '6:54:01 PM', 'Automatic Debtor
> status change to OUT.'),
> (733513, '2002-11-26', '10:05:08 AM', '##6303 NO ONE BY THAT NAME'),
>
>
>
> Here is my CF Code
>
> Start <cfoutput>#now()#</cfoutput>
>
>
> <cfquery name="GetFile" datasource="mysqldsn">
> INSERT INTO Client_comm
> VALUES
> <cfinclude template="/text12.txt">
> </cfquery>
> <br>
> <br>
> <br>
>
>
> End <cfoutput>#now()#</cfoutput>
>
> This is the error that I get back from CF
>
> Error during query: Unexpected Exception:
> java.lang.IllegalArgumentException message given: Packet is
> larger than max_allowed_packet from server configuration of
> 1047552 bytes
>
> Any Ideas on how to import this many records?
>
> Mike
> ----- Original Message -----
> From: Barney Boisvert
> To: CF-Talk
> Sent: Tuesday, April 13, 2004 3:16 PM
> Subject: RE: Import a Text file to MYSQL
>
>
> You can use LOAD DATA INFILE if you have sufficient
> privileges on the server
> and the file format is acceptable. If not, it's pretty
> simple to do some
> text manipulation and convert text into a multi-line INSERT
> statement:
>
> INSERT INTO tabl
> (col1, col2, col3)
> VALUES
> ('11', '12', '13'),
> ('21', '22', '23'),
> ('31', '32', '33'),
> .....
> ('n1', 'n2', 'n3)
>
> Basically, it goes like this: replace all "'" with "\'",
> add "('" to the
> start of every line, add "')" to the end, replace "," with
> "','", prepend
> the INSERT .... VALUES stuff, and then remove the trailing comma.
>
> Cheers,
> barneyb
>
> > -----Original Message-----
> > From: Mickael [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 13, 2004 12:06 PM
> > To: CF-Talk
> > Subject: Import a Text file to MYSQL
> >
> > Hello All,
> >
> > Can you import a text file to MYSQL with CF. I would like to
> > take a text file that is exported to Access then Drop/Create
> > the table with CF in MYSQL then import the file in its
> > entirety to create the table.
> >
> > This sounds simple enough in theory, how can this be done in
> > CF and MYSQL I don't really have any experience with MYSQL
> >
> > Thanks in advance
> >
> > Mike
> >
> >
> >
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
