CFFILE, pass it through listToArray() with newline as the delimiter, and
then start looping and building insert statements, running them as need-be.
However, that's a lot of data to do that with, and memory will quickly
deplete.
You might try a loop with find() like this:
<cfset index = 1 />
<cfloop from="1" to="1000" index="I">
<cfset index = find(fileContent, chr(10), index) />
</cfloop>
Which will find the 1000th newline in the file. Then use left and
removeChars to split the string at that point. Repeat with the remainder
until you've got 1000 (or whatever) line chunks for the whole file. That'll
get you more usable batches which you can deal with individually (probably
using the listToArray() method). You can even write those chunks back out
to file to conserve memory, if that's a concern.
That should be fairly efficient, but reassigning huge strings like that
might be more expensive than you can deal with. If so, using
java.lang.StringBuffer (if you've got some Java experience) can probably
mitigate much of that cost, but again, I don't know for sure.
As a last resort, you might look at something like Perl, which should be
obnoxiouly fast at simple text parsing like that. Just have it generate a
collection of files, which you can then process with CF (or heck, just do
the INSERTs with Perl too, if you want).
Cheers,
barneyb
> -----Original Message-----
> From: Mickael [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 15, 2004 11:18 AM
> To: CF-Talk
> Subject: Re: Import a Text file to MYSQL
>
> Hi Barney,
>
> I am trying to read in a text file. The file is quite large
> over 400mb. I get this file dumped to me from an external
> process. How can you break it up?
> ----- Original Message -----
> From: Barney Boisvert
> To: CF-Talk
> Sent: Thursday, April 15, 2004 12:49 PM
> Subject: RE: Import a Text file to MYSQL
>
>
> If you have access, change the max packet size that your
> server will allow.
> 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]
