Copy "X" number of records to new file

[YellowLabPro]

My worksheet is a .txt file which consists of a little over 12,000 records. This file is uploaded to a hosted server, to a MySql database.
I face a timeout problem, if anyone has any experience w/ this and can offer a solution, or articles to read or research please let me know.

In lieu of fixing the problem, my current solution is to select 2000 records at a time and save them out to a new file and upload one file at a time. I would like to automate this process.
1) Data begins on Row 4
2) Create new file w/ 2000 records per file
3) Save each new file named sequentially w/ a suffix number, 1,2,3...

What method should be used here? I am thinking a loop. If so, which, For Next?

Thanks,

YLP

[Bob Phillips]

Doug,

Does MySQL not have the option to set the timeout value within the code?

As for the loop, I would think For ... Next with a step of 200 would do it just as good as any.

[YellowLabPro]

Hi xld,
I do believe it does, and I believe it has been set. The file of 2000 records takes about 30-40 minutes to hash and upload, even if there are no errors. I have discussed this w/ my website developer and he has not been able to solve the issue. This means that if I want an updated and correct inventory, then I might have to sit around for 3 hours in total everyday to watch when one file of 2000 records is done and then begin the next..... not very fun or productive.

Ok, thanks for loop confirmation. Just for the record, it would be w/ a step of 2000?

[stanl]

If you are using an ADO or OLEDB connection to MySQL (assuming the Object var is oConn)

oConn.CommandTimeOut = n ' where n=seconds

then
oConn.Execute( [your upload statment] )

and I believe if you set n to -1, there is no timeout. .02 Stan

[YellowLabPro]

Thanks stanl,
How do I determine the type of connection, w/out having to ask my developer, he is sleeping right now....

[Bob Phillips]

Look in the code that does the work. There should be something that says



Dim oConn As ADODB.Connection


or similar.

[YellowLabPro]

Gotcha',
I will have a look. From what you just explained, then I am guessing that the line for the timeout that Stanl suggested would be in the same file too?

[Bob Phillips]

Yes, once the connection is established, issue the timeout command, before the SQL command is issued. I think Stan's suggestion of -1 is the best first option to try, you could always try tuning it once that you know that it works.

[stanl]

as an FYI - using an unlimited CommandTimeOut can disguise underlying server problems. A rule of thumb is 5 minutes [or n=300] for DML, whereas is using SELECT, then you can query the Recordset State [ ie. Open, Closed, fetching...].

With DML, or in your case a large INSERT, consider using TRANSACTION...ROLLBACK (I think MySQL supports it). If that is not an Option, ADO's Execute() method does have an optional RecordsAffected parameter.... sorry, Stan is ramblin':doh:

[YellowLabPro]

Stan,
Can we talk in laymen's terms?
What is DML?
If I can get the correct file to view, can you have a look at it and advise?

YLP

[Bob Phillips]

Doug,

For now, ignore that last post of Stan's. Try it with a timeout of -1, and if that works you can try modifying to an actual value. There is a chance that, as Stan says, it might mask a true error, but if 2000 records takes 30-40 minutes, it is probably just time (why does 2000 take so long)>

Either that, or implement a loop, and setup a progress bar so that you know it is working. I would probably do less than 2000 per hit myself, so that progress can be better monitored.

[YellowLabPro]

Doug,

For now, ignore that last post of Stan's. Try it with a timeout of -1, and if that works you can try modifying to an actual value. There is a chance that, as Stan says, it might mask a true error, but if 2000 records takes 30-40 minutes, it is probably just time (why does 2000 take so long)>
I think it takes a long time due to the number of fields it compares. I just started an upload at 12:40 and the progress bar on the browser window shows about 1/2 way, but I am not sure how accurate this is.
(Just finished: 1:00, 20 minutes)


Either that, or implement a loop, and setup a progress bar so that you know it is working. I would probably do less than 2000 per hit myself, so that progress can be better monitored.
Is the idea here to reduce the number until the reason is discovered?
I hate to say it, but I am having trouble just setting up a simple loop in Excel. To put one into a php file and have yet to do a progress bar. I will need some assistance w/ this. But don't want to impose....

thanks,

YLP

[Bob Phillips]

The reason for doing it in smaller increments is to lose less in a failure situation. If and when you introduce rollback as Stan suggested, it takes almost as long to rollback in a failure as it does to load, so you really don't want to wait all that time. Smaller incremental loops are useful here, plus you won't need such large timeouts, so more chance of catching any problems.

I thought you were doing it in Excel, I am afraid I don't do php.

[YellowLabPro]

Thanks xld.
It is a MySql DB w/ php files.
I do all the inventory files in Excel and save it as a .txt file which then gets uploaded.

Hope this clears up any confusion.
Does the setting of -1 still apply?

[YellowLabPro]

For....Next
As I started this thread off, I was inquiring if For Next would work. So I am heading down this path.

12,000+ rows and dynamically changing daily.

To define the range, I was thinking of finding the last row and copying increments of 2000 rows into a new file.
It commences at row 4, and every 2000 records will need to go into a new workbook.

To begin: does this work?
For x = 4 - LRow Step 2000

[Bob Phillips]

Thanks xld.
It is a MySql DB w/ php files.
I do all the inventory files in Excel and save it as a .txt file which then gets uploaded.

Hope this clears up any confusion.
Does the setting of -1 still apply? Yes, it is a connection parameter.

[Bob Phillips]

For....Next
As I started this thread off, I was inquiring if For Next would work. So I am heading down this path.

12,000+ rows and dynamically changing daily.

To define the range, I was thinking of finding the last row and copying increments of 2000 rows into a new file.
It commences at row 4, and every 2000 records will need to go into a new workbook.

To begin: does this work?
For x = 4 - LRow Step 2000 For x = 4 To LRow + 1999 Step 2000

[YellowLabPro]

I was working on something similar to this yesterday but had to quit.
I do not comprehend what the range is here:

For x = 4 To LRow + 1999 Step 2000
wss.Rows(x).Copy wst("A4")
Next x


For x = 4 to LRow + 1999 Step 2000 (If LRow is 12,500)
What is this saying?
Begin at 4 and .......?

[YellowLabPro]

I have not made any progress....
This copies the first row beginning at A4, and that is all.

[Bob Phillips]

I was working on something similar to this yesterday but had to quit.
I do not comprehend what the range is here:

For x = 4 To LRow + 1999 Step 2000
wss.Rows(x).Copy wst("A4")
Next x

For x = 4 to LRow + 1999 Step 2000 (If LRow is 12,500)
What is this saying?
Begin at 4 and .......? As well as stepping 2000 rows, you need to copy 2000 rows
For x = 4 To LRow Step 2000
wss.Rows(x).Resize(2000) Copy wst("A4")
Next x

I originally ran it from 4 to LRow + 1999 in order that any it didn't miss the last batch, but that was an error on my part, not needed.

[YellowLabPro]

xld:
Sorry no luck, there is an error in the line
wss.Rows(x).Resize(2000) Copy wst("A4")
Copy gets highlighted, and the whole line is red. I tried every change to this line I could think.

[Bob Phillips]

My b ad



For x = 4 To LRow Step 2000
wss.Rows(x).Resize(2000).Copy wst.Range("A4")
Next x