How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Pete Your Own Question

Pete
Pete, Computer Engineer
Category: Programming
Satisfied Customers: 15775
Experience:  Over 16 years of experience in computer programming.
30970729
Type Your Programming Question Here...
Pete is online now
A new question is answered every 9 seconds

How can you update an existing table in a database from an

Customer Question

how can you update an existing table in a database from an excel spreadsheet that has five columns, ID, Col1, Col2, Col3, Col4, the problem is that the excel spreadsheet columns should go into one column in the database table, so col1 - col4 will have the same ID in the table. The data in the spreadsheet is new data to be added to the existing table.
Submitted: 1 year ago.
Category: Programming
Expert:  Pete replied 1 year ago.

Hi there,

You will first have to combine the 4 columns within the spreadsheet to create a new column,

e.g. use this formula in a new column: =A1&" "&B1&" "&C1&" "&D1 to combine the contents of those 4 cells, and copy the formula down the column.

Let me know if you would like me to demonstrate this remotely.

Customer: replied 1 year ago.
But they should be in a separate cell but in the same column it should look something like this:
Id. Col
1. Col1
1. Col2
1. Col3
1. Col4
Etc.... There are hundreds of rows
Expert:  Pete replied 1 year ago.
When the formula is created for one row, it can instantly be copied to all other rows.
Let me know if you would like me to show you this remotely.
Pete.
Customer: replied 1 year ago.
your formula copies the data from one row to one cell, i want to copy each first row data and make a new row for each column so the original table content looks like the following:
ID. Col1. Col2. Col3. Col4
1. A. B. C. D
2. E. F. G. H
I want it to look like this
ID. Col
1. A
1. B
1. C
1. D
2. E
2. F
2. G
2. H
All in different rows
Expert:  Pete replied 1 year ago.
Ok, that is a bit more complicated, but you can do it with the following formula on a new worksheet:
=OFFSET(Sheet1!$A$1,((ROW()-1)*5)+(FLOOR(COLUMN()-1,4)/4),(COLUMN()-1)-(FLOOR(COLUMN()-1,4)))