INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

i NEED TO TRANSFER DATA FROM EXCEL

i NEED TO TRANSFER DATA FROM EXCEL

(OP)
i NEED TO TRANSFER DATA FROM EXCEL TO ACCESS. iN PARTICULAR, EMPLOYEES ENTER PRICE ND QUANTITY INFORMATION TO A DAILY SALES SPREADSHEET. i NEED TO TRANSFER CERTAIN INFORMATION, NOT ALL TO A REPORT IN ACCESS. iS THERE A LOOKUP FORMULA IN ACCESS THAT WILL TRANSFER DATA FROM EXCEL?

RE: i NEED TO TRANSFER DATA FROM EXCEL

Hi,

Is your data in Excel in a table: one table and only one table in a sheet headings in row 1 starting in colimn A?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: i NEED TO TRANSFER DATA FROM EXCEL

You can link an Excel sheet (hopefully in a consistent format) to Access as a linked table.

BTW: Typing in all CAPS is considered shouting. You have posted a number of times in the past with appropriate mixed case which is much preferred. Please preview your posts.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: i NEED TO TRANSFER DATA FROM EXCEL

(OP)
Sorry about all caps. I wrote that as I was leaving for lunch and did not realize the caps key was on until you mentioned.
The data ids in several cloumns. The first contains the item description, a 3 or 4 letter designation. The secon throuhj 30 columns contain a date and the information I need is in the middle. It looks like this:

Jan 1 Jan 2 Jan 3
BATI 1.19 2.44 .98
JEN 4.44 1.98 2.05
PAN .22 .24 .26

I need the " 1.19, 2.44, and .98, etc"
In excel, I just use a lookup formula. I'm not sure what to do in Access. thanks

RE: i NEED TO TRANSFER DATA FROM EXCEL

Your data view lacks structure. Please use TGML to provide us with decent column distinctions. Also if you have spaces in your field names, please enclose them in []s. Is this what your data looks like in Excel?

       [Jan 1] [Jan 2] [Jan 3]
BATI     1.19    2.44     .98
JEN      4.44    1.98    2.05
PAN       .22     .24     .26 

In Access, you would possibly use DLookup() or queries which are more efficient. How do you want to use the Excel data? Is there a column title for the first column? Can you answer Skip's questions?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: i NEED TO TRANSFER DATA FROM EXCEL

Quote:

The secon throuhj 30 columns contain a date and the information I need is in the middle.

You ONLY have data in Excel for the first 30 days of the year?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: i NEED TO TRANSFER DATA FROM EXCEL

(OP)
No, There is a different excel sheet for each month. There is no spaces in the dates. Also, the dates are in short form "1/2/18" etc. Thanks for the mention of Dhlookup. I had forgotten about that one.

Thanks

RE: i NEED TO TRANSFER DATA FROM EXCEL

So your Excel data actually looks like this:

   A       B       C       D      ...  AF
1        1/1/18  1/2/18  1/3/18   ... 1/31/18
2 BATI     1.19    2.44     .98   ...  2.34
3 JEN      4.44    1.98    2.05   ...  5.76
4 PAN       .22     .24     .26   ...  6.66
 
And you want to retrieve BLUE data staring from cell B2 - AF2 (for January)
and go down the data. Is that what you are after?


---- Andy

There is a great need for a sarcasm font.

RE: i NEED TO TRANSFER DATA FROM EXCEL

(OP)
yes. that's it exactly. thanks

RE: i NEED TO TRANSFER DATA FROM EXCEL

BIG mistake To use a report or summarized data for a table source like you have.

BIGGER mistake for a table source to be chopped up into multiple pieces.

Your workbook ought to have a single table for the data, from which monthly summary reports can be gleaned as well as a source for your Access.

It seems that this is a new workbook: “1/2/18”. Save yourself some grief. Make one table with Date, Item, Amount. Then each month’s REPORT can each be on a sheet via a PivotTable report. But your source table will be accessible for other analysis and reporting.

BTW, where are you getting the Item and Amount from for each day? Are you entering these values by hand?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: i NEED TO TRANSFER DATA FROM EXCEL

(OP)
You are absolutely correct, Skip. But I had nothing to do with the excel table. I have just been asked to get the correct data to include in the access reports. have been copying the data manually which is a big waste of time! I was hoping there was a simpler way to transfer the data. The data above is not real. I just made it up to show you the way it is laid out. Guess I didn't do a very good job!! Thanks

RE: i NEED TO TRANSFER DATA FROM EXCEL

The key question is, where does the data that someone puts into the Excel file come from? More than likely its comming from some other corporate table. Sombody’s manager wanted or wants to see a REPORT for each month. Fine.

I had a job that was outside IT. In several different companies. There are tables, usually rehosted: hours old, days old, weeks old “production” data that users, like yourself, can get access to. Or, if necessary, get to know an IT programmer who can set up a simple program to periodicly generate a file that you could import. There are ways for a user to get access to source data. THAT would sure help with your Access. Much, MUCH better than an Excel REPORT scattered throughout sheets.

BTW, once you have ALL your data in one excel sheet/table, File > Get External Data > Link Tables.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: i NEED TO TRANSFER DATA FROM EXCEL

Billheath,
I can't believe you are telling us the full story on this. The blue numbers from Andy's post have no meaning without the other data. They are just numbers without any reference to employees or dates. I suppose you could count or sum all the numbers but that seems odd to me.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: i NEED TO TRANSFER DATA FROM EXCEL

I am with Skip - get the source data. Or - I perfect world - get just the query so you can just access the data you are interested in, and don't even keep your own 'version'/copy of it.

To add to Skip's experience - I had seen the process where data was used to create a report, report was printed, then scanned, and OCR'ed to get the data back in 'electronic' format. Seriously.


---- Andy

There is a great need for a sarcasm font.

RE: i NEED TO TRANSFER DATA FROM EXCEL

(OP)
Actually I am. I have an access form with the date and the various initials entered automatically when I enter the form. I have a space for each value which I wanted to enter automatically from from the excel spreadsheet. The resultant table will look exactly like the one above with some additional information which I have to enter. It will be one table which lists the initials and pricing per the date. I hope this makes sense.

RE: i NEED TO TRANSFER DATA FROM EXCEL

Andy banghead

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: i NEED TO TRANSFER DATA FROM EXCEL

(OP)
I think the query idea might work. I can link the excel spreadsheet and do a query on it and use the query info as the basis for the report.
Thanks guys

RE: i NEED TO TRANSFER DATA FROM EXCEL

You need normalized data in access. If you really can do nothing about the data you receive and you have excel 2016 or 2013, you can do the preprocessing fully in excel, with power query. In:
- excel 2016 you have it built-in as get&transform feature,
- excel 2013 this ia a microsoft free com add-in, that you need to download, install and activate. See for starting: https://support.office.com/en-us/article/Get-Trans...

Before switching to power query:
- make sure that table headers are proper excel dates,
- convert monthly data to tables (select any cell inside data and choose insert>table, you should see them with alternating colours rows), give tables meaningful names (say tblJan, tblFeb, etc.).

Create query for each table that normalizes monthly tables:
- create query "from table" from excel get&transform (2016), you should get power query interface,
- select columns without date headers, right-click and unpivot other columns,
rename defauld headers if you like to,
- close and create a connection only (if excel added new sheet with table, you can delete it).
As a result you should get 12 queries named tblJan, tblFeb etc, as your table names. Again, you can rename them.

Now combine queries:
- add blank query, in power query interface write in formula line =tblJan (should be query name for January data),
- add February data from February query ("append" action),
- repeat steps for other months queries.
You can output to worksheet or create connection only.

This seems to be complicated, but you complete it in 15 minuts. You can connect access to connection or table in excel.

combo

RE: i NEED TO TRANSFER DATA FROM EXCEL

"The resultant table will look exactly like the one above" - are you saying you have a table in your data base for January with 31+ fields (one field per day), February table with 28+ fields (29 fields every 4 years or so)? Really....?

I hope I've got it all wrong...

@Duane - I wanted to machinegun whoever set this process....


---- Andy

There is a great need for a sarcasm font.

RE: i NEED TO TRANSFER DATA FROM EXCEL

Andy, its a REPORT! Most Excel users don’t know the difference between a report and a proper normalized table. In fact most Excel users would rather have a nice looking report than a proper table. They don’t realize that a multi-sheet report is an awful source for a database, data analysis and reporting. SAD.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: i NEED TO TRANSFER DATA FROM EXCEL

If I follow this, I do not think it would be to complicated to do via code. I think a SQL solution would be very time consuming You recieve 12 tables in this format:

CODE -->

A       B       C       D      ...  AF
1        1/1/18  1/2/18  1/3/18   ... 1/31/18
2 BATI     1.19    2.44     .98   ...  2.34
3 JEN      4.44    1.98    2.05   ...  5.76
4 PAN       .22     .24     .26   ...  6.66 

I am guessing you want to import the data into a normalized access table and persist the data in Access. Now you can do traditional db stuff on the data.

CODE -->

EmployeeID  Price_ND  Entry_Date
Batti       1.19      1/1/2018
JEN         4.44      1/1/2018
...
PAN         .26       1/31/2018
...
BATTI       99        12/31/2018 

The best solution would be to pre-process this an Excel, but I am not sure if that is easy. Normalizing this in SQL would also be difficult and time consuming.
I would link each workSheet as a linked table. Then I would in vba loop my tables, loop the rows and loop the columns and use an insert query to insert the data into a normalized format. You would convert the Field name into a real date. If the data does not change after it is inputted you can only enter new data. If it does you would have to update everything. The code to do this would not be very long or involved. If that makes sense, I could demo real quick.

RE: i NEED TO TRANSFER DATA FROM EXCEL

Quote (MajP)

The best solution would be to pre-process this an Excel, but I am not sure if that is easy.
Normalizing is easy in power query, also appending tables. Howewer, this requires recent excels (2013-2016).

combo

RE: i NEED TO TRANSFER DATA FROM EXCEL

OK, everyone says it is easy to normalize your data in excel. So definitely do it that way. Make a new worksheet that normalizes the data from the 12 worksheets into 1 worksheet that looks like this.

CODE -->

EmployeeID  Price_ND  Entry_Date
Batti       1.19      1/1/2018
JEN         4.44      1/1/2018
...
PAN         .26       1/31/2018
...
BATTI       99        12/31/2018 
Then link to that worksheet. Now you can use it in any report, query, or form.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close