none
Need to switch my excel import from vertical to horizontal RRS feed

  • Question

  • Hey, I am using an excel table that shows product information vertically with the information titles in the first column. I am trying to import this into Access and It is only allowing me to select the first row for my title headings. Is there a way to make is so that I can select my first column for my title headings? I cannot copy the data in excel and paste the translation because the there are random formulas throughout that are not locked correctly. Any ideas?
    Thursday, November 30, 2017 9:33 PM

All replies

  • Hi Andrew,

    Sounds like you're using the Wizard for the import process. If so, it works a certain way and expect certain things as input. If your current setup does not work well with the Wizard, you might have to create your own import/export process using VBA.

    Just my 2 cents...

    Thursday, November 30, 2017 9:35 PM
  • hmm, that is beyond my skills at this point. I am very familiar with Excel, but barely a newb where Access is involved.

    Thursday, November 30, 2017 9:40 PM
  • Perhaps the next best thing is to use Excel VBA to convert  the table sideways, so the Access import Wizard can easily consume the data.

    Just a thought...

    Thursday, November 30, 2017 10:00 PM
  • I don't fully follow your post.  But to copy / paste from excel into Access one must be sure that the field count is the same.

    For instance - if one attempts to highlight the column in excel, copy - and then paste into Access - - - this typically will not work because it goes to infinity (or whatever is the max record count in excel.....).  Instead - start at the bottom of the excel in the last row and establish your highlight by scrolling up to the top - so that you have a defined set of records.  This will paste into an Access table column.

    In general transforms vertical to horizontal and vice versa are more easily accomplished in Excel.  One has the option to deal with the displaying values, not the attached formulas - and there is a transform paste option.

    Thursday, November 30, 2017 10:54 PM
  • Hi Andrew Co,

    I would also suggest you convert data in excel from vertical to horizontal and then import to Access. You could copy the data range and then paste, special past-Transpose, to a temporary worksheet. And then you could import data from the temporary worksheet.

    Here is the demonstrate.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Terry Xu - MSFT Wednesday, December 20, 2017 2:04 AM
    Friday, December 1, 2017 1:44 AM
  • Hello Aderew,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 20, 2017 2:04 AM