Merge two table from different csv files

[Soly_90]

Hello ,
I am still beginner in using macro vba and I need a help .
I have two different csv file .
I want the user to enter the path file of the csv file to use as input in the script
This csv file has only one sheet called data ,that include one table with different columns .

The second csv file contains sheet called source
,include only one table .

I want to merge this table from first csv file with the table from the second csv file .

(first csv file (user input ) contains always column called target and the second csv file contains also target column )
Both of them has target column name
I need only to merge the rows that contains same value in target column and ignore the rest rows .


I would like to have like window ,that allow user to enter the file path of excel file and also the user enter folder path of csv file
And button to run the script

Could you help me ?
Thanks in advance

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

How to merge two csv file together depend on specific column - OzGrid Free Excel/VBA Help Forum

http://www.vbaexpress.com/forum/showthread.php?71880-How-to-merge-two-different-csv-files-by-Macro https://www.mrexcel.com/board/threads/merge-two-table-from-different-csv-files.1263926/ Hello , I am still beginner in using macro vba and I need a help…

forum.ozgrid.com

How to merge two different csv files by Macro

Hello , i need a help . i have two csv files the first csv file contains a table , each column seperates with ; the second csv file contains a table ,each column seperates with , i would like to have a macro vba script to let user to enter the input file path of csv and also choose the...

www.vbaexpress.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Soly_90]

Sorry, but I am not familiar with the download site and it is requiring me to sign up. No thanks. Good Luck. I'm out unless you use MrE's XL2BB functionality. Instructions are on the site. Read the forum rules and help.

I think u mentioned above to upload in drop box or other .
But anyway ,it is not requiring to sign up .
I will look , how it XL2BB works .

 

[Soly_90]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

How to merge two csv file together depend on specific column - OzGrid Free Excel/VBA Help Forum

http://www.vbaexpress.com/forum/showthread.php?71880-How-to-merge-two-different-csv-files-by-Macro https://www.mrexcel.com/board/threads/merge-two-table-from-different-csv-files.1263926/ Hello , I am still beginner in using macro vba and I need a help…

forum.ozgrid.com

How to merge two different csv files by Macro

Hello , i need a help . i have two csv files the first csv file contains a table , each column seperates with ; the second csv file contains a table ,each column seperates with , i would like to have a macro vba script to let user to enter the input file path of csv and also choose the...

www.vbaexpress.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry but I am new .i do not know about your rules .
Will you want to help me or give advice to solve the issue ?

 

[RoryA]

i do not know about your rules

Then please read them now. They are the same in this respect as the other forums you have posted on.

 

[Soly_90]

Then please read them now. They are the same in this respect as the other forums you have posted on.

I can delete the posts at other forums .if I have to do it because of your rules .
Will my post still here ?
Could I get some advices or solution of my problem ?
Thanks

 

[RoryA]

I can delete the posts at other forums .if I have to do it because of your rules .

If you actually read the rules, as I have asked, you will see the answer to that (which is no).

 

[felixstraube]

I could download the files.

So you would proceed like this:

1. Copy Data csv files in one folder, and Source csv files in another folder.
2. In an new excel file, go to the menu "Data/Get & Transform Data/Get Data/From File/From Folder"

Select Data folder. Click on Transform Data

3. In the power query editor click on the icon of the first column:

and click ok. Select Source.Name column and remove it.
You should see this:

4. (Still in Power Query Editor) click on menu "New Query/New source/File/Folder"

Select Source folder and then the same steps as before.
You should see this:

Now select column "Result" and go to menu "Home/Transform/Split Column/By Delimiter", select "Semicolon" and "Each occurence of the delimiter", click ok. You should see this:

5. Go to menu "Combine/Merge Queries/Merge Queries as New"

Select Data in the first, and Source in the second. Then click the target column in both. Below select "Inner" as the Join Kind:

You will get this:

Click on the marked icon and uncheck target from the list, and uncheck "Use original column name as prefix" (no neccesary).
Then you can rename and reorder the columns as needed.

6. Last remove the target column (right click, remove). And click menu Close&Load. You will get 3 new sheets, Data, Source and Merge1. You can delete sheets Data and Source if you like. In Merge1 you should get the result:

Let me know if this works for you or if you need any assistance.

 

[Soly_90]

I could download the files.

So you would proceed like this:

1. Copy Data csv files in one folder, and Source csv files in another folder.
2. In an new excel file, go to the menu "Data/Get & Transform Data/Get Data/From File/From Folder"
View attachment 116490

Select Data folder. Click on Transform Data View attachment 116491
3. In the power query editor click on the icon of the first column:
View attachment 116492
and click ok. Select Source.Name column and remove it.
You should see this:

View attachment 116493

4. (Still in Power Query Editor) click on menu "New Query/New source/File/Folder"
View attachment 116494

Select Source folder and then the same steps as before.
You should see this:

View attachment 116495

Now select column "Result" and go to menu "Home/Transform/Split Column/By Delimiter", select "Semicolon" and "Each occurence of the delimiter", click ok. You should see this:

View attachment 116505

5. Go to menu "Combine/Merge Queries/Merge Queries as New"
View attachment 116496

Select Data in the first, and Source in the second. Then click the target column in both. Below select "Inner" as the Join Kind:

View attachment 116503

You will get this:

View attachment 116504

Click on the marked icon and uncheck target from the list, and uncheck "Use original column name as prefix" (no neccesary).
Then you can rename and reorder the columns as needed.

6. Last remove the target column (right click, remove). And click menu Close&Load. You will get 3 new sheets, Data, Source and Merge1. You can delete sheets Data and Source if you like. In Merge1 you should get the result:

View attachment 116506


Let me know if this works for you or if you need any assistance.

Thanks a lot for your solution .
The first file that contain target ,type , result columns will change by user .
could I let the power query to do the same steps automatic , when the user change the first file in folder ?
If yes ,how ?
I appreciate it ,thanks a lot .

 

[felixstraube]

Once the file is changed or you add new files (with more data) to that folder
go to your excel file and go to menu Data and "Refresh All". Result should update with the new data.

 

[Soly_90]

Once the file is changed or you add new files (with more data) to that folder
go to your excel file and go to menu Data and "Refresh All". Result should update with the new data.

thanks a lot .
I will try your method .
Could I understand something from your experience ,
When do u decide to use macro script ?
When do you decide to use power query ?