What do you do when you have to import a text file that equates to a
cursor/table too big (beyond 2GB)? I'm using MySQL's import which is
fine for such huge beasts, but the import isn't liking some of the field
formats (related to my last post but not exactly that) and so I was
going to use VFP as the intermediary to massage/fix the data, then put
it into the MySQL table.
Ideas? tia,
--Mike
(VFP9SP2)
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F443FE1.7030701@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 2/21/2012 8:07 PM, MB Software Solutions, LLC wrote:
> What do you do when you have to import a text file that equates to a
> cursor/table too big (beyond 2GB)? I'm using MySQL's import which is
> fine for such huge beasts, but the import isn't liking some of the field
> formats (related to my last post but not exactly that) and so I was
> going to use VFP as the intermediary to massage/fix the data, then put
> it into the MySQL table.
>
> Ideas? tia,
> --Mike
>
> (VFP9SP2)
>
I know I could physically cut the file in half (or more pieces) and
import that way then insert the parts to the final MySQL table, but I
wondered if anyone had other ideas?
tia,
--Mike
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F4470FE.8070905@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
That's how I would do it, cut it in half or in quarters.
----- Original Message -----
From: "MB Software Solutions, LLC" <mbsoftwaresolutions@mbsoftwaresolutions.com>
To: ProFox Email List <profox@leafe.com>
Cc:
Sent: Tuesday, February 21, 2012 11:37 PM
Subject: Re: Importing files > 2GB
On 2/21/2012 8:07 PM, MB Software Solutions, LLC wrote:
> What do you do when you have to import a text file that equates to a
> cursor/table too big (beyond 2GB)? I'm using MySQL's import which is
> fine for such huge beasts, but the import isn't liking some of the field
> formats (related to my last post but not exactly that) and so I was
> going to use VFP as the intermediary to massage/fix the data, then put
> it into the MySQL table.
>
> Ideas? tia,
> --Mike
>
> (VFP9SP2)
>
I know I could physically cut the file in half (or more pieces) and
import that way then insert the parts to the final MySQL table, but I
wondered if anyone had other ideas?
tia,
--Mike
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F4470FE.8070905@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/1329888864.80272.YahooMailNeo@web125601.mail.ne1.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 2/22/2012 12:34 AM, Michael Madigan wrote:
> That's how I would do it, cut it in half or in quarters.
Yes, but is there another way or is that the ONLY option? How messy
would going the LLFF route be I wonder?
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F447FD9.6070707@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
When dealing with files of that size, I've always done just that. Split the file into reasonable chunks that I then process for whatever backend it is destined for.
Michael Oke, II
661-349-6221
Contents of this and all messages are intended for their designated recipient.
On Feb 21, 2012, at 8:37 PM, "MB Software Solutions, LLC"<mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
> On 2/21/2012 8:07 PM, MB Software Solutions, LLC wrote:
>> What do you do when you have to import a text file that equates to a
>> cursor/table too big (beyond 2GB)? I'm using MySQL's import which is
>> fine for such huge beasts, but the import isn't liking some of the field
>> formats (related to my last post but not exactly that) and so I was
>> going to use VFP as the intermediary to massage/fix the data, then put
>> it into the MySQL table.
>>
>> Ideas? tia,
>> --Mike
>>
>> (VFP9SP2)
>>
>
>
>
> I know I could physically cut the file in half (or more pieces) and
> import that way then insert the parts to the final MySQL table, but I
> wondered if anyone had other ideas?
>
> tia,
> --Mike
>
> --
> Mike Babcock, MCP
> MB Software Solutions, LLC
> President, Chief Software Architect
> http://mbsoftwaresolutions.com
> http://twitter.com/mbabcock16
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BD5D4AC4-E55F-45E4-AA24-9886588AE485@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
I guess it all depends whether this is a one-time thing or will be done multiple times.
----- Original Message -----
From: "MB Software Solutions, LLC" <mbsoftwaresolutions@mbsoftwaresolutions.com>
To: ProFox Email List <profox@leafe.com>
Cc:
Sent: Wednesday, February 22, 2012 12:40 AM
Subject: Re: Importing files > 2GB
On 2/22/2012 12:34 AM, Michael Madigan wrote:
> That's how I would do it, cut it in half or in quarters.
Yes, but is there another way or is that the ONLY option? How messy
would going the LLFF route be I wonder?
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F447FD9.6070707@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/1329889415.41851.YahooMailNeo@web125606.mail.ne1.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 2/22/2012 12:42 AM, Michael Oke, II wrote:
> When dealing with files of that size, I've always done just that. Split the file into reasonable chunks that I then process for whatever backend it is destined for.
How are you inserting the rows into the final destination backend?
Let's say you've got a table that's over the 2GB barrier. You break it
up into <2GB pieces and then append the data into remote views into your
backend then do a tableupdate? Here's a potential gotcha...there's no
clearly defined PK for these rows. UGH.
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F44834F.1000609@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On 2/22/2012 12:55 AM, MB Software Solutions, LLC wrote:
> On 2/22/2012 12:42 AM, Michael Oke, II wrote:
>> When dealing with files of that size, I've always done just that. Split the file into reasonable chunks that I then process for whatever backend it is destined for.
>
>
> How are you inserting the rows into the final destination backend?
> Let's say you've got a table that's over the 2GB barrier. You break it
> up into<2GB pieces and then append the data into remote views into your
> backend then do a tableupdate? Here's a potential gotcha...there's no
> clearly defined PK for these rows. UGH.
>
>
I was thinking about saving to a CSV file again and then inserting into
the MySQL table from that, with the fields fixed that had issue prior.
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F448493.9050306@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
llfs in Fox is *fast* - and very flexible - that's the way I'd go. Read
in a chunk, take a substring up to the last whole record, work with that
then read on from that offset, rinse & repeat.
"Here's a potential gotcha...there's no clearly defined PK for these rows."
Write-only storage then? <g>
AndyD 8-)₹
On 22/02/2012 05:43, Michael Madigan wrote:
> <snip>
>
> Yes, but is there another way or is that the ONLY option? How messy
> would going the LLFF route be I wonder?
>
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F44983F.5030606@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
> I was thinking about saving to a CSV file again and then inserting into
> the MySQL table from that, with the fields fixed that had issue prior.
>
If wasting time is not a problem, you can use a loop to split the input file
into multiple cursors, then use them your way out :
[VFP]
*** creating a test file
SET TEXTMERGE ON noshow
SET TEXTMERGE TO testdata.txt
FOR i = 65 TO 90
\\<<REPLICATE(CHR(i),40)>>
\
ENDFOR
SET TEXTMERGE TO
*** appending the file in multiple cursors
*** we want to split into files made of nbby records, i.e. 10
nbby = 10
indname = 0
DO WHILE .T.
cpt = -1
** normally it should be cpt = 0, but the UDF mycount() is called one time
** before VFP executes the APPEND, for evaluation purpose (same way for
SELECT)
indname = indname+1
CREATE CURSOR ('work'+LTRIM(STR(indname))) (zon1 c(40))
APPEND FROM testdata.txt DELIMITED ;
FOR BETWEEN(mycount(), (indname-1)*nbby+1,indname*nbby)
IF _tally = 0
USE IN ('work'+LTRIM(STR(indname)))
EXIT
ENDIF
ENDDO
***
FUNCTION mycount
cpt = cpt+1
RETURN cpt
***
[/VFP]
Gérard.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/7EB786FDD10D42509BE040B4E92E01E8@MuriellePC
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.