How to extract info from database to excel

I have more than 50,000 rows of data in Excel that look like this:

SKU     SerialNo
AZQW    10001   
XSWE    10023   
GTYH    10230   
MKLJ    10378       
GHYU    10567       
GTYH    10895

The table in the database which I wish to extract looks this like:-

SKU     Seria开发者_如何学PythonlNo    Grade
AZQW    10001           A
XSWE    10023           B
CVFR    10043           D
GTYH    10230           B
MKLJ    10378           A
RTYH    10456           C
GHYU    10567           D
AZQW    10784           B
GTYH    10895           B

The final output in excel should be as follows ie. to add Grade info:-

SKU     SerialNo    Grade
AZQW    10001   A
XSWE    10023   B
GTYH    10230   B
MKLJ    10378   A
GHYU    10567   D
GTYH    10895   B

How can I achieve this using a macro? If not possible, is it possible to automate a SQL statement that run row-by-row in order to extract the rq'd field (ie Grade) ?

Thanks.

If I understood well, i should be fairly easily achieved using Excel macro.

First output your DB's data into a separate worksheet of your Excel Document. Be sure both worksheets (original sheet with data and new one with DB data) are both sorted, else sort them with macro.

Then go down both list and append grade to your original worksheet's "SerialNo" column when line matches.

Once finished, don't forget to delete worksheet with DB data in it. Should be better than to call 50 000 times database, I usually try to minimize number of database calls because they're slow.

EDIT : Since you have to many records in table to output in SQL Worksheet, I'd suggest you import all your Excel data in a temporary SQL table, do an easy select by joining the temp table with the other one and export result to an Excel file !

select t1.SKU SKU, concat(t1.SerialNo, "  ", t1.grade) SerialGrade from yourTable t1
    join tempTable t2 on t2.SerialNo = t1.SerialNo
    orderby t1.SerialNo ASC

i can't see a fast way to deal with it:

1. build a macro that would call an SQL statement for every row >> would need many requests to the db
2. build a unique SQL query that would suffice for all the values >> i can't see a way to do this
3. the only way i can see is to export the SQL values to another Sheet of your Workbook and to use a VLOOKUP() formula

I wouldn't use VBA for this unless you need to. Create a new sheet and put an external data query on it. If you're using Access or SQLServer, you can OLEDB right to them. Otherwise, you can use MSQuery to make an ODBC connection. Bring over the relevant columns, Serial Number and Grade. It looks like serial number is unique, so you should need to bring SKU from the database.

Then use a VLOOKUP formula to pull the grade into the main sheet. You can even hide the sheet with the external data query on it if you like.