Is there any vlookup function for mysql?

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Is there any vlookup function for mysql?

Postby sigitasl » 08. April 2019 15:00

Hello there. I have kind of a strange question. I've been using vlookup function in excel for almost a year now and it makes my work way more easy. And now it's the first time when I need to collect the data from MySQL database. I have an excel file with the column SKU and I need to fill the data for every SKU. The thing is that database has almost 800k rows and I don't know how to handle this amount of information. Is there any way to workaround this without doing every SKU one by one?
sigitasl
 
Posts: 5
Joined: 08. April 2019 14:57
XAMPP version: v3.2.2
Operating System: Windows 10

Re: Is there any vlookup function for mysql?

Postby Nobbie » 09. April 2019 01:44

??

You dont know SQL, am i right? It sounds as if you are looking for an easy SELECT statement, but unfortunately your description is very poor and its impossible to understand, what you are looking for finally?! What exactly do you mean by " I have an excel file with the column SKU and I need to fill the data for every SKU"? What is meant by "fill the data"??

SQL works with tables (Excel also does) and the idea of SQL is tables, tables, tables. Create any table from another table(s).

Example: You have table with 3 columns. First Columns is an id (an uniq number, usually the primary key), second columns is familyname (like "Miller"),the third columns is called SKU (i have NO idea, what you mean by SKU) and it contains a SKU (whatever it is). The table is called "mytable" for example. Issue this simple SQL statement:

SELECT familiyname, SKU from mytable;


This creates an table which contains familiyname and SKU for all entries of your table. Not very sophisticated and you simply dropped the id, not more.

A more sophisticated example: same table as above, we issue this command:

SELECT familyname, SKU from mytable WHERE familiyname = "Miller";

This results in table, where ALL familiynames are "Miller" and also contains their SKU.

This is a super simple example, SQL is an extremely powefull language and far far superior to the simple vlookup from Excel.
Nobbie
 
Posts: 13171
Joined: 09. March 2008 13:04

Re: Is there any vlookup function for mysql?

Postby sigitasl » 09. April 2019 07:29

By definition, a stock keeping unit (or SKU) is a number assigned to a product by a retail store. Basically a code for an item.
And I have 30 000 SKU's that needs the data such as Description, Group, Parts_info, Manufacturer. All of the data is in the database with all the SKU's. But the problem that the database has 800k rows and I need only 30 000 selected ones.
sigitasl
 
Posts: 5
Joined: 08. April 2019 14:57
XAMPP version: v3.2.2
Operating System: Windows 10

Re: Is there any vlookup function for mysql?

Postby Altrea » 09. April 2019 08:32

It is not clear for me what you want to achieve.
Why do you use Excel vlookup instead of filtering the the database directly?
Why do you need this 30000 selected rows? What do you want to do with it?
And how do you get this selected rows into your Excel?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: Is there any vlookup function for mysql?

Postby sigitasl » 09. April 2019 09:56

We got a file containing 30 000 SKUS from one of our suppliers. It took as a long time to get all the SKUS for the specific brand.
And now when we got the file, we need to fill the data for the products. We are going to open the store and we need this information.

By saying " instead of filtering the the database directly?" what do you mean? How it is achievable?

By the way, I also have an excel file which is the whole database, the excel file size is 25gb. I uploaded the whole file to database, because the excel file cannot handle this amount of information.
sigitasl
 
Posts: 5
Joined: 08. April 2019 14:57
XAMPP version: v3.2.2
Operating System: Windows 10

Re: Is there any vlookup function for mysql?

Postby Altrea » 09. April 2019 12:49

okay, summary:

- You got a list (file) of 30000 SKUs from your supplier
- You got a database of 800000 entries of what? products?
- Now you want to add information to the 30000 SKUs in the SKU file and this data came from the 800k rows database?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: Is there any vlookup function for mysql?

Postby sigitasl » 09. April 2019 14:20

- You got a list (file) of 30000 SKUs from your supplier - correct
- You got a database of 800000 entries of what? products? - SKU's with the data

Example:

SKU Group Part_info Fits for vehicle
123SKU ABS Sensors Weight: 100g (and additional info) BMW e39

And there are 800k rows with similar data like in the example
I got this excel file from the guy who is programmer. He's selling the info about vehicles and so on.


- Now you want to add information to the 30000 SKUs in the SKU file and this data came from the 800k rows database? Almost correct
I want to find the data for 30 000 SKU's in the database, because I don't really need all 800k rows.
sigitasl
 
Posts: 5
Joined: 08. April 2019 14:57
XAMPP version: v3.2.2
Operating System: Windows 10

Re: Is there any vlookup function for mysql?

Postby Altrea » 09. April 2019 14:27

So, if you would have this 30000 SKU entries also in the same database you could for example select all entries in your 800k Database which are match any of the 30000 SKU like so
https://stackoverflow.com/questions/544 ... in-another

Or you are searching for a specific SKU in your 800k Database which Is a simple SQL select statement.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: Is there any vlookup function for mysql?

Postby sigitasl » 09. April 2019 14:44

So the first thing I need to do is to import all the 30 000 SKUS to the database in the new table, am I right?

For example my main table with 800k rows is named MAIN_TABLE
Newly imported table with 30k skus is named NEW_TABLE


How can I re-edit the sql script in stackoverflow to justify my needs?
Im sorry, I'm new in to this.
sigitasl
 
Posts: 5
Joined: 08. April 2019 14:57
XAMPP version: v3.2.2
Operating System: Windows 10

Re: Is there any vlookup function for mysql?

Postby Nobbie » 09. April 2019 16:17

sigitasl wrote:How can I re-edit the sql script in stackoverflow to justify my needs?
Im sorry, I'm new in to this.


Sorry, but we cannot teach you SQL. That is by far beyond the scope of a forum. SQL is a very complex, huge and sophisticated language, you will need months in order to be an educated SQL programmer if you are "new to this". And its not done by only learning SQL, you also have to learn about the infrastructure from MySQL, about databases, tables, users, rightsmanagement and and and. If you only know excel, you have big mountain to climb, as excel knowledge does not help anyway. Dont you have SQL programmers and administrators in your company?
Nobbie
 
Posts: 13171
Joined: 09. March 2008 13:04

Re: Is there any vlookup function for mysql?

Postby Nobbie » 10. April 2019 00:57

sigitasl wrote:We are going to open the store and we need this information.


Which store? How is the store working? As far as i understood, the store is working with a MySQL Database. How can you manage and administrate that store without any SQL knowledge?? Its still very hard to understand what you want to achieve.
Nobbie
 
Posts: 13171
Joined: 09. March 2008 13:04


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 125 guests