Storing (and working on) an amount of data that will increase over time

[Apeximius]

Hello. First of all: thank you for all the times this website has provided me with answers through threads posted and answered by people who have been here longer than me. I'm sure that if I wasn't so clueless this time, I would have found a solution on the forum already.

So this is the situation:
- I am working with an API that provides data in either XML or JSON format.
- I need to use vba to retrieve the data and store it in a table, so that I can work on it later (offline).
- The rows of data will pile up over time (it's currently just a couple hundred rows long by about 14 columns)

-One of the columns needs to be split into 3, which I have been doing so far by loading the data it into an excel sheet and using variants to make the process sufficiently fast. (The criteria for the split is not entirely trivial, I need to split it by "-"[horizontal dash] and then by upperbound " "[space])
-This column needs to be splitted and contains crucial pieces of information.

-I will be using this data for data analysis purposes and I cannot be bound by the ~1,000,000 row limitation of excel sheets.

My question:
What would be the best way for me to store/work on this data?
Maybe Access Databases? Maybe PowerPivot? I'm open to suggestions.

Thank you.

 

[Rijnsent]

Hi Apeximius,
if you want to store it locally and have >1 mio records, Access sounds like quite a good option. For analysis purposes you could then create a SELECT query and link an Excel pivot table directly to that query. For VBA & JSON, I find this site helpfull: excel to json and back - Desktop Liberation

Cheers,

Koen

 

[Apeximius]

Thank you for your reply.
In the meanwhile I have already opted for using access (as you recommended now).
As for the type of data I am retrieving: I opted for xml, since I had both options available (xml and json) and excel has built in libraries to manipulate xml (but not json, as far as I know - I'd have to use the javascript library and use that to parse the json).
Never-the-less I'll take a look at your link, it may come in handy at a time where I am forced to use json.