merge data from multiple files

April 10, 2010 at 08:34:50
Specs: linux
merge data from same line from 10 files , first 6 columns is the same for 10 file, column 7 is not.
c1,c2,c3,c4,c5,c6 f1 c7, f2 c7 ....


See More: merge data from multiple files

Report •


#1
April 10, 2010 at 09:01:14
If i understant you use Excel. Use vlookup function .Here a tutorial.

Report •

#2
April 10, 2010 at 11:02:23
Hi,

If you want some help, you need to explain your requirements.

Also it is customary to ask for assistance - everyone who responds is a volunteer!

1. You say you want to merge data.
Does this mean you want to concatenate text strings, e.g., Cell C1 on Sheet1 in workbook One.xls contains "My" and Cell C1 on Sheet1 in workbook Two.xls contains "Document", then cell C1 on Sheet1 in the workbook Merge.xls will contain "My Document"
or
Are you summing data, C1 in One.xls contains 10, C1 in Two.xls contains 10, so C1 in the Merge.xls file will contain 20.

2. Am I correct in thinking that your 10 files are ten individual workbooks.

It would be helpful if you explained this - also is the data in each workbook in a worksheet named "Sheet1" or is it in another worksheet.

3. Can you explain more about the sequence
c1,c2,c3,c4,c5,c6 f1 c7, f2 c7 ....
Is c7 meant to appear twice.
What happens after f2, c7. Does the sequence repeat e.g., f1, c7, f2, c8, f3, c9 ...
or is it
f1, c7, f2, c7, f3, c7 ....
and where does this end - what are the last cells to be merged.

4. It would help to know if you are looking for a one time merge, i.e., get the data from the 10 workbooks, and merge them and save
or
will the data in the 10 workbooks be changing and you need formulas that link to the relevant cells in all 10 workbooks.

Here is a formula that adds the values in cell A1 in Sheet1 in four workbooks:

='C:\temp\[File1.xls]Sheet1'!$A$1 + 'C:\temp\[File2.xls]Sheet1'!$A$1 + 'C:\temp\[File3.xls]Sheet1'!$A$1 + 'C:\temp\[File4.xls]Sheet1'!$A$1
Each workbook is in the same folder on Drive C:, but part of the formula needs the path to each file, so the formula quickly becomes rather long.

If you can say what you are trying to achieve, it might help in coming up with a solution.

Regards


Report •

#3
April 10, 2010 at 15:03:42
thanks for the comment, acturally it is not worksheet. it is txt file.
what we want to do is we have 10 files, each file have 100,000 lines, they have equal line number.
first six columns in each files is the same, but 7th column is not,

we need to merge them into a big report, keep first 6 columns, and concat seven column from each file,

find output should be like the following:
column1, coulmn2... column6, file1 column 7, fil2 column7, file3 column7, file 10 column 7


Report •

Related Solutions

#4
April 10, 2010 at 18:58:12
Hi,

If you want some help, you need to explain your requirements.

Also it is customary to ask for assistance - everyone who responds is a volunteer!

Also you are posting in a forum for Office programs such as Excel.

If you post in an appropriate forum, it would still be helpful if you answered the questions.

Perhaps the Moderator will move your question to a more appropriate forum.

Regards


Report •


Ask Question