Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    mbart is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Location
    Atlanta
    Posts
    2

    Converting horizontal Excel data with monthly periods into vertical Access database records

    I wish to convertdemand data from an Excel worksheet into an Access database table. Thedata forecast sales demand in monthly buckets. I have aneed to upload the Excel data and store in a normal database format.

    I'm rather new with Access, but do want to create something in Access thatcan convert the data as a repeatable process, since I must do this atleast once a month for a large volume of parts and 30 or monthly periods perpart.


    Any suggestions would be much appreciated.

    Thank you
    Attached Thumbnails Attached Thumbnails Example.png  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    More than one way to accomplish.

    1. Set link to worksheet, use UNION query to rearrange data to normalized structure, use that query as source for INSERT INTO SELECT action. Unfortunately, this will require setting link and rebuilding the UNION query every time - automating that with VBA would be significant challenge because the Excel column names are different each time

    2. Lots of VBA code to manipulate Excel objects - Excel automation - for ideas review http://www.accessmvp.com/KDSnell/EXC...m#WriteFileRst
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    one way that you can do this is use a query something like this

    Code:
    SELECT *
    FROM (SELECT * FROM [sheet1$A2:E1000] as Data,  [sheet1$A1:E1] as Header IN 'C:\pathtoexcelfile\filename.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes])  AS XL
    this will give you 10 fields

    Data.F1 to Data.F5

    and

    Header.F1 to Header.F5

    you can then use this as basis for a union query used to insert data to your table

    assuming you save the query as QryXL

    Code:
    INSERT INTO myTable (PartNo, Period, Qty)
    SELECT [Data.F1] as Partno, Header.F2 as Period, Data.F3 as Qty FROM qryXL
    UNION All SELECT [Data.F1] as Partno, Header.F2 as Period, Data.F2 as Qty FROM qryXL
    UNION All SELECT [Data.F1] as Partno, Header.F3 as Period, Data.F3 as Qty FROM qryXL
    UNION All SELECT [Data.F1] as Partno, Header.F4 as Period, Data.F4 as Qty FROM qryXL
    UNION All SELECT [Data.F1] as Partno, Header.F5 as Period, Data.F5 as Qty FROM qryXL

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Oh doh! I should have remembered the query reaching into Excel since I gave that as an answer in another thread some time ago.

    This does mean need to know the bottom-right cell reference and query will have to be modified each time if more than 1000 rows.

    However, UNION appears to have 1 too many rows and advise converting the MoYr text to an actual date value.

    SELECT Data.F1 AS ProdNum, Data.F2 AS Qty, Header.F2 AS MoYr, DateAdd("m",1,CDate(Left(Header.F2,3) & "/" & 1 & "/" & Right(Header.F2,2)))-1 AS RptDate FROM qryXL
    UNION SELECT Data.F1, Data.F3, Header.F3, DateAdd("m",1,CDate(Left(Header.F3,3) & "/" & 1 & "/" & Right(Header.F3,2)))-1 FROM qryXL
    UNION SELECT Data.F1, Data.F4, Header.F4, DateAdd("m",1,CDate(Left(Header.F4,3) & "/" & 1 & "/" & Right(Header.F4,2)))-1 FROM qryXL
    UNION SELECT Data.F1, Data.F5, Header.F5, DateAdd("m",1,CDate(Left(Header.F5,3) & "/" & 1 & "/" & Right(Header.F5,2)))-1 FROM qryXL
    ORDER BY ProdNum, RptDate;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    This does mean need to know the bottom-right cell reference and query will have to be modified each time to reflect the change if more than 1000 rows.
    No - you can make it a million rows if you like, or the whole column - see below. The query will only bring back the populated rows no blanks. The exception might be if an excel user has populated say 300,000 rows, then deleted most of them (would have to check, most of the excel sheets I import come from other systems and are not modified by users before import). But if so, add a criteria, something like 'WHERE F1 is not null'. I have had to do that in the past when an excel file is generated with lots of blank rows at the top or mixed in with the data.

    An alternative

    Code:
    SELECT *
    FROM (SELECT * FROM [sheet1$A:E] as Data,  [sheet1$A1:E1] as Header IN 'C:\pathtoexcelfile\filename.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE Data.F1<>Header.F1)  AS XL
    I've just run this on an excel file with 12k rows - and returns 12k records, no blanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Right, the query needs to use cell reference that would allow for greatest possible number of rows, ever. My point was if they built it with only 1000 then would have to modify - so if there is a cell reference, I would hope 1,000,000 would be adequate but who knows... Not specifying as shown in your revised query would seem best.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    like all these things, it is impossible to have a 'one size fits all' solution. The nearest you could get to it is to use

    [sheet1$] AS Data, [sheet1$1:1] AS Header

    but there will be situations where the header row is not on the first row, F1 is not populated because it looks nicer if the first column is left blank, or more complex requirements when you need to join data in sheet1 with data in sheet2, etc

    for me the biggest issue is with sheet names - some systems provide sheet names which change monthly/annually etc.

  8. #8
    mbart is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Location
    Atlanta
    Posts
    2
    Thank you both, I'll give it a try.

  9. #9
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    A very simple way to do this is through the Excel Addin -- Power Query. It is a free download for Excel 2010 through 2013. It is native in 2016, 2019 and 365 and is call Get and Transform. Here is the Mcode for that
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Part Number"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"
    Once normalized per above, you can import or link to Access. See attached file to review.
    Attached Files Attached Files

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Alan
    As an occasional user of both PowerQuery & Get&Transform, I had never thought of using it for that purpose.
    However I'm embarrassed to say that I'm not clear how to use your code to achieve that result.
    MCode=macro code?

    Any chance of an idiot's guide?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Colin

    Here is a link that will tell you how to take Mcode (Power Query Language) and apply it.

    https://excel.solutions/2017/11/powe...te-code-video/

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Alan
    Many thanks for that link
    I've followed the instructions and can see how it works perfectly on your simple example.
    I've modified it slightly to add more complexity - attached
    Trying now to apply it to a much more complex example but so far without success....

    If I can get it to work more widely, I'd like to post this idea at another forum with credit back to yourself & a link to this thread & the video link
    Let me know if you have any objections?

    However if I have ongoing problems I may send you a PM about this topic
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Success.
    Had to recreate my original data range for some reason

    If anyone is interested, attached is another example using attendance data from an Access crosstab query (Sheet1)
    The PowerQuery (Query1) converts this to a normalised structure (Sheet2)

    Thanks once again to alansidman for this useful idea.
    I can see this approach being extremely useful for fixing poorly structured tables whether in forum posts or for clients
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Colin,
    Happy to hear that you got it working. Headed out right now, but will review it later for my own education.

  15. #15
    Uncle Gizmo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    26
    Quote Originally Posted by mbart View Post
    I wish to convertdemand data from an Excel worksheet into an Access database table. Thedata forecast sales demand in monthly buckets. I have aneed to upload the Excel data and store in a normal database format.

    I'm rather new with Access, but do want to create something in Access thatcan convert the data as a repeatable process, since I must do this atleast once a month for a large volume of parts and 30 or monthly periods perpart.


    Any suggestions would be much appreciated.

    Thank you

    Hi mbart,
    I created a "Transpose Tool" which I reckon could do the job easily and regularly, and also take care of any anomalies in your data, which tend to popup regularly in Excel Data!

    You can see how the "Transpose Tool" works in videos, text and pictures, on the Website here:- Transpose Tool - Nifty Access You can download it here:- Transpose Tool - Nifty Access
    to get it Free, use the following coupon code:- IHaveSubscribedToYourYouTube ....


    I would be very grateful if you would subscribe to my YouTube channel Nifty Access - Microsoft


    If you if you want to send me a sample database with some dummy data in it, I can go through the conversion process and create a YouTube video demonstrating it for your particular user case.

    My email address is:- mailtonyhine@gmail.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-10-2015, 11:47 AM
  2. Replies: 1
    Last Post: 01-29-2015, 12:41 PM
  3. Replies: 6
    Last Post: 07-31-2014, 12:53 PM
  4. Replies: 12
    Last Post: 05-23-2013, 10:51 AM
  5. Horizontal to vertical data
    By hoachen in forum Access
    Replies: 3
    Last Post: 07-18-2012, 10:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums