Hello
I tried to merge files with somewhat different columns.
Assume that: file 1 has four columns: A, B, C, D; file 2 has three columns: B, C, D
I want to merge these two files. The stack function apparently not working well here. It figures out that the columns do not match exactly. But it’s still directly stack two files together anyway. In the joint file, it just adds a row of variable name before the data from file 2.
What I want here is it automatically match column B, C ,D. And then, as for A, just leave it blank for the cases from the file two.
Don’t know if this could be done in the current version, or I need to wait to see if you can do it in the future.
Thanks
You can use the Gather Transform to unpivot your files.
Then, use the Merge Transform to merge the files.
Then, you can use the Pivot Transform on the Merged data to put the data back into columns.
1 Like
So you want to input:
| A | B | C | D |
|---|---|---|---|
| a1 | b1 | c1 | d1 |
and
| B | C | D |
|---|---|---|
| b2 | c2 | d2 |
then merge them ‘vertically’ to output:
| A | B | C | D |
|---|---|---|---|
| a1 | b1 | c1 | d1 |
| b2 | c2 | d2 |
Is that correct?
If so, you can easily do that by using Stack with Align columns by set to Header name.
If the column names don’t match you can use Rename Cols to rename them before the Stack.
Does that help?
Dan’s method works. But somehow Stack with Align columns by set to Header name not working here. if you stack them, you will see the problem I describe above. I cannot upload file as new user. but if you like, I can send you my files.
Yes, please send the input files (and, ideally your .transform file) to us:
https://www.easydatatransform.com/contact.html
i send the file to the email in that web page.
I have sent an example of how to do it.
Was the problem that you didn’t check has header on the inputs?
This is the problem. thank you very much.
1 Like
A final thought. I think the problem originated from the fact that I use a lot of numbers, for example, 10110112 as Headers in the first row. So when I import the data into the software, it does not automatically recognize the first role as headers. When I change in the headers into something like v10110112, it did automatically recognize the headers.
1 Like
Yes, it tries to guess if the first row is a header. If it contains numbers, it guesses that it isn’t.