Help importing several excel files into same access table, getting error now.

mickael28

New Member
Joined
Oct 12, 2015
Messages
6
Hi,

I've never used access so not sure if I'm making a basic mistake but I've got 2 files that I'd like to import there and I'm getting errors.

Main file is this:
Uploadfiles.io - NASDAQ_20170817.xls

And once that table is loaded, I'd like to load the following one on the same table (Uploadfiles.io - NASDAQ_20170816.xls). The 1st column would be the identifier that might already exist in the 1st table but I'd like the 2nd column to be 'merged' into the row for the same symbol

Do you guys know if this would be possible to achieve with access? and any idea of the steps that I should follow to avoid the errors and end up with a single table with the data combined?

I'll need to append more columns similar to the one on the 2nd link once those two are merged, hence that I'd need to have a procedure that it's somehow repeatable, at least a number of times...

If you could have a look and let me know pls? Many thanks!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
When you say merge the 2nd column do you mean add, replace or concatenate the fields? Access can do any of these things, no problems. The first thing you would need to do is either import or link to both of the files. Then you would have to write some code to do what ever you need to do. For example if you wanted to replace the data in column 2 with new data you could do it like this.
Code:
Dim RsRead as recordset
Dim RsWrite as recordset

set rsread = currentdb.openrecordset(table name, dbopentable)
set rswrite = currentdb.openrecodset(other table, dbopentable)

if not rsread.bof and not rsread.eof then
    do until rsread.eof
        rswrite.findfirst(criteria) then
            if rswrite.nomatch then
                Add new record
            else
                update existing record
            end if
        rsread.movenext
    loop
end if
 
Upvote 0
ah, I meant concatenate (ie, have a final table with the values of both - linked by the symbol column).

I've now imported both tables independently, where could I write that code? and do you know how I could change the code above so that I concatenate the values rather than replace them? Ta
 
Upvote 0
Adding new columns every time you import a file sounds wrong for data stored in database tables.
What are the columns you are importing?
 
Upvote 0
Adding new columns every time you import a file sounds wrong for data stored in database tables.
What are the columns you are importing?

I didn't have another idea, not sure if something else would be easier.

The idea is to create an initial table with symbol and volume, and then each day import another table with the same headers (symbol and volume) but different values and concatenate the results. After a couple of weeks I won't care about old data anymore, so maybe at the end it'd be a final table with around 11 columns (one symbol and 11 columns with volume data) and I will just keep dropping the latest column and adding a new one on a daily basis...

At the moment, I don't even know how to do that just with two volume columns as I've never used access before. I was trying in a spreadsheet but as symbols can appear and disappear, it was getting to tricky.
 
Upvote 0
Why would you need extra columns? What extra columns? In general you want to add data at the "bottom", not on the "side"...
 
Upvote 0
Just to kick out an idea, here's three different imports to one table on three different day:
-------------------------------------------
|   DateImported | SymbolID | SymbolValue |
-------------------------------------------
| 1/1/2017 13:25 | Foo      |       15.43 |
| 1/1/2017 13:25 | Bar      |       11.42 |
| 1/2/2017 17:25 | Foo      |       16.47 |
| 1/2/2017 17:25 | Bar      |       11.44 |
| 1/3/2017 11:40 | Bar      |       10.58 |
-------------------------------------------
 
Upvote 0
The idea I had in mind is that I wanted to have all data for the same symbol in the same row, so that then I could export it to excel and do more easy calculations (like average of the last X days for each symbol, filter the ones which averages is higher than X, etc). If it's all mixed up one below the other, I don't think I could do that...

Just to kick out an idea, here's three different imports to one table on three different day:

[FONT=&quot]-------------------------------------------
| SymbolID(unique) | Day_1 | Day_2 | Day_3 | Day_4 | etc... (columns imported manually each day)
-------------------------------------------
| Foo | 15. 43 | 75.43 |
| Bar | 11.42 | 9.42 |
| Another | 16.47 | 20.32 |
| Symbol | | 11.44 |
| Etc | 10.58 | 18.33 |
-------------------------------------------[/FONT]
 
Upvote 0
Access is for relational data, stored in rows where the tables are related to one another by one or more fields. The relationships can be like a daisy chain, or or like several chains linked together at different points. The data can be grouped and summarized (averaged, summed, counted, etc.) over groups (symbols, days, whatever). Excel is basically the opposite. Since that is where you want to end up anyway, I question if Access is of much value here at all. If you think it is for some reason, I suggest you dig into db design concepts and normalization lest you find yourself stumped at every step of the way. The problem with Access is, once you go down a bad path, you will face problems that could be avoided if you had some design knowledge - and the more the better.
 
Last edited:
Upvote 0
This is a personal project, it doesn't really need to have a good design or anything similar as long as it allows me to somehow import 10 columns. I guess it's quite straight forward for someone who uses this tool, but a little bit of reading I did today I think what I might need is to import all the tables individually and then just create a query to link them in a final view. I need to get all the data to give that a go these days and see if I manage to do it that way.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top