Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading big datasets daily?
Hi, I am new to this forum and have a question that might be too simple or
easy for you guys. I'll try to do my best describing my problem and what I am doing so far to solve it... I want to download a daily database from a website. I set up a program that tells the computer to go everyday and opens an excel file that has a web query embbeded to it. The query goes to the website and selects the table I need and downloads it. Then, the program saves it with a csv format and name=date. So far so good....The problem is that the website now provides a table with more than 65536 observations [maximum allowed by my MS Excel 2002 SP1]. Therefore, I am missing a part of the dataset I need... 1) I was thinking about migrating to access and use the same query. I never used access and from the quick search I did, it doesn't seem like the same query can be used. 2) What if I set the web query in excel to split the dataset in pieces of 60K or less lines and place them in different parts of the file? Any suggestion? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading big datasets daily?
I recommend the Access approach. You can analyze the table in Access that you
create wiht Excel via query or pivot table, but you are going to keep running into difficulties using Excel to try to store and manipulate that much data. -- HTH... Jim Thomlinson "marulo" wrote: Hi, I am new to this forum and have a question that might be too simple or easy for you guys. I'll try to do my best describing my problem and what I am doing so far to solve it... I want to download a daily database from a website. I set up a program that tells the computer to go everyday and opens an excel file that has a web query embbeded to it. The query goes to the website and selects the table I need and downloads it. Then, the program saves it with a csv format and name=date. So far so good....The problem is that the website now provides a table with more than 65536 observations [maximum allowed by my MS Excel 2002 SP1]. Therefore, I am missing a part of the dataset I need... 1) I was thinking about migrating to access and use the same query. I never used access and from the quick search I did, it doesn't seem like the same query can be used. 2) What if I set the web query in excel to split the dataset in pieces of 60K or less lines and place them in different parts of the file? Any suggestion? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloading big datasets daily?
marulo:
Not sure how you are bringing the recordset in but you could use CopyFromRecordset CopyFromRecordset looks at the end of file (EOF) not the row limitation 65,536 So to import more than 65,536 rows you need to tell Excel where to put the additional records For example the following would put the first 65,536 records of a four field recordset in columns A B C D and the balance in columns E F G H '/Starts at A1 Worksheets("Sheet3").Range("A:A,D:D").CopyFromReco rdset rs '/If more than 65,536 records you must add a second line '/Starts at E Worksheets("Sheet3").Range("E:E,H:H").CopyFromReco rdset rs Good Luck TK "marulo" wrote: ............... So far so good....The problem is that the website now provides a table with more than 65536 observations [maximum allowed by my MS Excel 2002 SP1]. Therefore, I am missing a part of the dataset I need... 2) What if I set the web query in excel to split the dataset in pieces of 60K or less lines and place them in different parts of the file? Any suggestion? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals in very large datasets | Excel Worksheet Functions | |||
HOW DO I FREEZE DATA THAT CHANGES DAILY. dAILY/MONTHLY BUDGET | Excel Discussion (Misc queries) | |||
sorting datasets on two rows | Excel Worksheet Functions | |||
Linking Daily Worksheet To Daily Invoice Total | Excel Worksheet Functions | |||
owc next version must support datasets | Excel Programming |