UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Normalize Data From Excel, Access 2013    
 
   
sck067
post Apr 16 2018, 02:00 PM
Post#1



Posts: 20
Joined: 12-April 18



I have a Excel file that I need to upload to my database however the format of the Excel file is an issue. the Excel data is displayed as below:

Emp_Name Location jan-18 feb-18 mar-18 ..........
J Doe FLA 10 15 3
M Smith Tx 5 20 4
J Doe Ga 2 3 4

there are more standard fields and the months will change over time (as months pass)

I want to transform it into a table structure as below

Emp_Name Location Month Sales
j doe fla 1/2018 10
j doe fla 2/2018 15
.
.
.

sample data only, the name and location will be convereted to IDs

any thoughts on how best to do this? considering months will be added and dropped over time

thanks!!

sk
Go to the top of the page
 
theDBguy
post Apr 16 2018, 02:19 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,916
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

How exactly is the Excel file generated? Do you have any control on what column headers and formatting the Excel file can have?

You will probably need some VBA to perform this task but having a consistent format would make it easier to do the import process.

Just my 2 cents...
Go to the top of the page
 
sck067
post Apr 16 2018, 03:04 PM
Post#3



Posts: 20
Joined: 12-April 18



Limited control over the first few general data columns (name, location,....) but the months will roll. there will be no skipped months in the columns but I cant control how far out they will go. It is basically a sales review and forecast sheet that management wants to see. From a user point of view it is easier to review and edit with the months as columns (in excel) and load into the tool.

I can do it with VBA by cycling through the imported column names but I was wondering it there was something in SQL that could do it (backward crosstab)
Go to the top of the page
 
projecttoday
post Apr 16 2018, 03:15 PM
Post#4


UtterAccess VIP
Posts: 10,348
Joined: 10-February 04
From: South Charleston, WV


What about the dbGuy's first question?
QUOTE
How exactly is the Excel file generated?
.
Go to the top of the page
 
sck067
post Apr 16 2018, 03:52 PM
Post#5



Posts: 20
Joined: 12-April 18



it is an export out of the database that is then edited by individual managers for upload. it is a copy of the spreadsheet that is currently in use. I can "ask/train" them to not change headers but as we know, somebody will....

Basically, the db is replacing the spreadsheet so that multiple users can edit their data at the same time. they require that the edits be done in a spreadsheet sales matrix format
This post has been edited by sck067: Apr 16 2018, 03:53 PM
Go to the top of the page
 
projecttoday
post Apr 16 2018, 08:10 PM
Post#6


UtterAccess VIP
Posts: 10,348
Joined: 10-February 04
From: South Charleston, WV


Please confirm that this Excel import is not a one-time upload.

We need more information about how the sales transactions are entered initially into what system, if, in fact, they are entered into a single system. And if there is a single system, why do managers need to change the data? Your said they're trying to eliminate the spreadsheet by requiring them to enter into a spreadsheet. Please explain.

The less back-and-forth the better.
If data must be transferred, computer files are much preferable to spreadsheets.
Go to the top of the page
 
sck067
post Apr 17 2018, 10:47 AM
Post#7



Posts: 20
Joined: 12-April 18



this is a monthly update to the database by mult managers. the current issue is that they are all trying to work in a single spreadsheet. what they want to do is have individual spreadsheets that they can upload to a database for consolidation. They do not want to work in the database because they are comfortable with Excel (ease of use) and they require that the data be displayed in a monthly sales format (months as columns).

I think I have about handled the conversion but I have run into a sticking point. I pull the field names in the upload via For each f in db.tabledefs("tblSalesLoadTemp").fields all months are in a YYYYMM format so use the following to find the months

If IsNumeric(f.name) then
bunch of other code and a append qry here
end if

the issue is I am trying to use f.name as a value in the SalesMonth field in the append query and it is giving me a overflow error. I tried

Dim intMonthID as integer
intMonthID= cint(f.name)

and get the same result

if I debug.print f.name, the result is 201801

I know it is an issue with f.name being an object but cant figure out how to convert it to a value.
Go to the top of the page
 
sck067
post Apr 17 2018, 12:00 PM
Post#8



Posts: 20
Joined: 12-April 18



got it

needed to be Clng() not Cint()

and

ref the recordset as rst.fields(f.name)


thanks!!
Go to the top of the page
 
haresfur
post Apr 17 2018, 09:36 PM
Post#9



Posts: 294
Joined: 4-April 12
From: Bendigo, Australia


You can protect the worksheet and lock cells that the users shouldn't edit or unlock only the cells they should.
Go to the top of the page
 
projecttoday
post Apr 18 2018, 04:10 AM
Post#10


UtterAccess VIP
Posts: 10,348
Joined: 10-February 04
From: South Charleston, WV


I realize you have to do what you are told. But, assuming my understanding is correct, it would be much simpler for you to develop a form in Access and distribute that form as a front-end database. All the users have to do is open the form and type in the data into clearly-labeled fields. Another form allows them to see reports of the combined data at the click of a button output on the screen, for printing, or as spreadsheets, however they choose. That would be more "comfortable" than navigating cells on a spreadsheet for data entry to say the least. And way more "comfortable" than scraping data off multiple spreadsheets.
Go to the top of the page
 
sck067
post Apr 18 2018, 08:40 AM
Post#11



Posts: 20
Joined: 12-April 18



I understand what you are saying but in my experience, users always prefer to work in Excel when they are looking at multiple row/column entries (100+ rows and 36 months (columns). the simple copy/insert/edit is the biggest "pro" over typing into individual fields. Yes, it is a pain to validate data but they will not accept field by field typed entry into a form.
Go to the top of the page
 
projecttoday
post Apr 18 2018, 09:01 AM
Post#12


UtterAccess VIP
Posts: 10,348
Joined: 10-February 04
From: South Charleston, WV


I see. Can you give a specific example of this input?
Go to the top of the page
 
haresfur
post Apr 22 2018, 04:37 PM
Post#13



Posts: 294
Joined: 4-April 12
From: Bendigo, Australia


Another option to consider is to hide a second sheet in the excel workbook and use Excel formulas and/or VBA to rearrange the data into that sheet so it can be easily loaded into access. The Excel offset and other ways of using cell references could make the task easier and I'm in favour of easy solutions even if they aren't elegant.
Go to the top of the page
 
projecttoday
post Apr 22 2018, 06:13 PM
Post#14


UtterAccess VIP
Posts: 10,348
Joined: 10-February 04
From: South Charleston, WV


I'm in favor of easy solutions, too. So is this an Excel workbook into which users enter data? Can you post an example?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 01:18 PM