Combine multiple csv files by rows number

[Enna]

I have multiple about 300 csv files (each have different headings and the number of records is the same -about 80,000), how can I combine all those 300 csv files by row numbers and make one final csv file with about 80,000 records and all the variables from each 300 files using VBA? Thank you

 

[Enna]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	REDCap ID		Submitter Affiliation	Point of Care affiliation	If Other, please provide affiliation	Submitter Full Name	Name of Submitter	Notification Date	Notification Time	Is this a:	Was DC Health Notified through DGMQ / Epi-X?	DGMQ ID / Epi-X ID	Please select mode of travel where exposure could have occurred based on DGMQ notification	Flight Number	Cruise Name	Passport Country	Passport Number	Key contact related to this person included in DGMQ	Date arrived in U.S.	Last possible exposure date (Day 0)	Date Monitoring by DC Health Started (may not be Date for Day 1)
2	1	4/1/2020	ZZ Health			Someone Someone		########	15:30	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No
3	3	3/26/2020	ZZ Health			Someone Someone		########	9:39	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No
4	6	4/6/2020	ZZ Health			Someone Someone	Someone S			<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No
Sheet1

 

[Enna]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	REDCap ID	Monitoring end date (14 days after [exposure_end_dt] )	How was DC health notified of the case?	Caller/Provider FIRST name	Caller/Provider LAST name	Caller/Provider Phone Number	Caller/Provider Email	Caller/Provider Affiliation/Organization	College/University/School Name:	If other, please specify	FIRST name	LAST name	MIDDLE Intial	Date of birth (in the format of MM-DD-YYYY)	Age (years)	Patient sex	Please describe other sex:	Patient sex at birth	Patient currently pregnant?	How many weeks pregnant?	Estimated due date
2	1		Healthcare Provider / Redcap / ELR	Someone	Some	202000000	someone@someth.org	Someone's Somewhene			NSOemc	Npepmfe		########	12	Male
3	3		Healthcare Provider / Redcap / ELR	Some	Something	202-000-000		MW jwpjdmce			vomoe	NCPmpr	C	########	55	Male
4	6		Patient								cmepfmwpef	MEPmce		########	8	Female			No
Sheet1

 

[Enna]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	REDCap ID	Monitoring end date (14 days after [exposure_end_dt] )	How was DC health notified of the case?	Caller/Provider FIRST name	Caller/Provider LAST name	Caller/Provider Phone Number	Caller/Provider Email	Caller/Provider Affiliation/Organization	College/University/School Name:	If other, please specify	FIRST name	LAST name	MIDDLE Intial	Date of birth (in the format of MM-DD-YYYY)	Age (years)	Patient sex	Please describe other sex:	Patient sex at birth	Patient currently pregnant?	How many weeks pregnant?	Estimated due date
2	1		Healthcare Provider / Redcap / ELR	Someone	Some	202000000	someone@someth.org	Someone's Somewhene			NSOemc	Npepmfe		########	12	Male
3	3		Healthcare Provider / Redcap / ELR	Some	Something	202-000-000		MW jwpjdmce			vomoe	NCPmpr	C	########	55	Male
4	6		Patient								cmepfmwpef	MEPmce		########	8	Female			No
Sheet1

For the final combined table, first order should be from first csv file (tmp_1) followed by next order tmp_2, tmp_3 etc. and redcap ID should be only in first column not from each table. Thank you.
Note: I have about csv tables (tmp_1 ....tmp_24).

 

[Enna]

Don't see any Patient ID noted in either picture. Care to identify for us? What about an upload we can work with?

I posted the sample 2 tables. Thank you so much!

 

[Enna]

I see no ID in the Capture1.png -- since you are not showing us a workable file, I will offer up this video so that you can do this on your own. I am out.

Thank you. This video is nice but not helpful at all. I provide the tables. Could you work on the tables. Thank you.

 

[Enna]

I see no ID in the Capture1.png -- since you are not showing us a workable file, I will offer up this video so that you can do this on your own. I am out.

Hello.
The first table is this:

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	REDCap ID		Submitter Affiliation	Point of Care affiliation	If Other, please provide affiliation	Submitter Full Name	Name of Submitter	Notification Date	Notification Time	Is this a:	Was DC Health Notified through DGMQ / Epi-X?	DGMQ ID / Epi-X ID	Please select mode of travel where exposure could have occurred based on DGMQ notification	Flight Number	Cruise Name	Passport Country	Passport Number	Key contact related to this person included in DGMQ	Date arrived in U.S.	Last possible exposure date (Day 0)	Date Monitoring by DC Health Started (may not be Date for Day 1)
2	1	4/1/2020	ZZ Health			Someone Someone		########	15:30	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No
3	3	3/26/2020	ZZ Health			Someone Someone		########	9:39	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No
4	6	4/6/2020	ZZ Health			Someone Someone	Someone S			<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No
Sheet1

The next table is this:

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	REDCap ID	Monitoring end date (14 days after [exposure_end_dt] )	How was DC health notified of the case?	Caller/Provider FIRST name	Caller/Provider LAST name	Caller/Provider Phone Number	Caller/Provider Email	Caller/Provider Affiliation/Organization	College/University/School Name:	If other, please specify	FIRST name	LAST name	MIDDLE Intial	Date of birth (in the format of MM-DD-YYYY)	Age (years)	Patient sex	Please describe other sex:	Patient sex at birth	Patient currently pregnant?	How many weeks pregnant?	Estimated due date
2	1		Healthcare Provider / Redcap / ELR	Some	Nnnr	2020000000	grafvr@fzsb.org	Somemsb gb ggi' fvrvr			bxdt	ft		########	12	Male
3	3		Healthcare Provider / Redcap / ELR	Somes	Nobt	202-000-0000		KCmrmv rdvebrdy			gchrdt	xfgn	C	########	55	Male
4	6		Patient								fgnndr	xftj		########	8	Female			No
Sheet1

Final desired combined table is below:

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	REDCap ID		Submitter Affiliation	Point of Care affiliation	If Other, please provide affiliation	Submitter Full Name	Name of Submitter	Notification Date	Notification Time	Is this a:	Was DC Health Notified through DGMQ / Epi-X?	DGMQ ID / Epi-X ID	Please select mode of travel where exposure could have occurred based on DGMQ notification	Flight Number	Cruise Name	Passport Country	Passport Number	Key contact related to this person included in DGMQ	Date arrived in U.S.	Last possible exposure date (Day 0)	Date Monitoring by DC Health Started (may not be Date for Day 1)	Monitoring end date (14 days after [exposure_end_dt] )	How was DC health notified of the case?	Caller/Provider FIRST name	Caller/Provider LAST name	Caller/Provider Phone Number	Caller/Provider Email	Caller/Provider Affiliation/Organization	College/University/School Name:	If other, please specify	FIRST name	LAST name	MIDDLE Intial	Date of birth (in the format of MM-DD-YYYY)	Age (years)	Patient sex	Please describe other sex:	Patient sex at birth	Patient currently pregnant?	How many weeks pregnant?	Estimated due date
2	1	4/1/2020	ZZ Health			Someone Someone		########	15:30	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Healthcare Provider / Redcap / ELR	Some	Nnnr	2020000000	grafvr@fzsb.org	Somemsb gb ggi' fvrvr			bxdt	ft		########	12	Male
3	3	3/26/2020	ZZ Health			Someone Someone		########	9:39	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Healthcare Provider / Redcap / ELR	Somes	Nobt	202-000-0000		KCmrmv rdvebrdy			gchrdt	xfgn	C	########	55	Male
4	6	4/6/2020	ZZ Health			Someone Someone	Someone S			<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Patient								fgnndr	xftj		########	8	Female			No
Sheet1

Those are just 2 sample tables. There are 24 csv files. Thank you so much!

 

[alansidman]

With Power Query which is available to you as a free add-in for XL2010 your can merge (outer join) your two tables on the common ID field.

let
    Source = Table.NestedJoin(Table1, {"REDCap ID"}, Table2, {"REDCap ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1", "Submitter Affiliation", "Point of Care affiliation", "If Other, please provide affiliation", "Submitter Full Name", "Name of Submitter", "Notification Date", "Notification Time", "Is this a:", "Was DC Health Notified through DGMQ / Epi-X?", "DGMQ ID / Epi-X ID", "Please select mode of travel where exposure could have occurred based on DGMQ notification", "Flight Number", "Cruise Name", "Passport Country", "Passport Number", "Key contact related to this person included in DGMQ ", "Date arrived in U.S.", "Last possible exposure date (Day 0)", "Date Monitoring by DC Health Started (may not be Date for Day 1)", "Monitoring end date (14 days after [exposure_end_dt]  )", "How was DC health notified of the case?", "Caller/Provider FIRST name", "Caller/Provider LAST name", "Caller/Provider Phone Number", "Caller/Provider Email", "Caller/Provider Affiliation/Organization", "College/University/School Name:", "If other, please specify", "FIRST name", "LAST name", "MIDDLE Intial", "Date of birth (in the format of MM-DD-YYYY)", "Age (years)", "Patient sex", "Please describe other sex:", "Patient sex at birth", "Patient currently pregnant?", "How many weeks pregnant?", "Estimated due date"}, {"Column1", "Submitter Affiliation", "Point of Care affiliation", "If Other, please provide affiliation", "Submitter Full Name", "Name of Submitter", "Notification Date", "Notification Time", "Is this a:", "Was DC Health Notified through DGMQ / Epi-X?", "DGMQ ID / Epi-X ID", "Please select mode of travel where exposure could have occurred based on DGMQ no", "Flight Number", "Cruise Name", "Passport Country", "Passport Number", "Key contact related to this person included in DGMQ ", "Date arrived in U.S.", "Last possible exposure date (Day 0)", "Date Monitoring by DC Health Started (may not be Date for Day 1)", "Monitoring end date (14 days after [exposure_end_dt]  ).1", "How was DC health notified of the case?.1", "Caller/Provider FIRST name.1", "Caller/Provider LAST name.1", "Caller/Provider Phone Number.1", "Caller/Provider Email.1", "Caller/Provider Affiliation/Organization.1", "College/University/School Name:.1", "If other, please specify.1", "FIRST name.1", "LAST name.1", "MIDDLE Intial.1", "Date of birth (in the format of MM-DD-YYYY).1", "Age (years).1", "Patient sex.1", "Please describe other sex:.1", "Patient sex at birth.1", "Patient currently pregnant?.1", "How many weeks pregnant?.1", "Estimated due date.1"})
in
    #"Expanded Table2"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
1	REDCap ID	Monitoring end date (14 days after [exposure_end_dt] )	How was DC health notified of the case?	Caller/Provider FIRST name	Caller/Provider LAST name	Caller/Provider Phone Number	Caller/Provider Email	Caller/Provider Affiliation/Organization	College/University/School Name:	If other, please specify	FIRST name	LAST name	MIDDLE Intial	Date of birth (in the format of MM-DD-YYYY)	Age (years)	Patient sex	Please describe other sex:	Patient sex at birth	Patient currently pregnant?	How many weeks pregnant?	Estimated due date	Column1	Submitter Affiliation	Point of Care affiliation	If Other, please provide affiliation	Submitter Full Name	Name of Submitter	Notification Date	Notification Time	Is this a:	Was DC Health Notified through DGMQ / Epi-X?	DGMQ ID / Epi-X ID	Please select mode of travel where exposure could have occurred based on DGMQ no	Flight Number	Cruise Name	Passport Country	Passport Number	Key contact related to this person included in DGMQ	Date arrived in U.S.	Last possible exposure date (Day 0)	Date Monitoring by DC Health Started (may not be Date for Day 1)	Monitoring end date (14 days after [exposure_end_dt] ).1	How was DC health notified of the case?.1	Caller/Provider FIRST name.1	Caller/Provider LAST name.1	Caller/Provider Phone Number.1	Caller/Provider Email.1	Caller/Provider Affiliation/Organization.1	College/University/School Name:.1	If other, please specify.1	FIRST name.1	LAST name.1	MIDDLE Intial.1	Date of birth (in the format of MM-DD-YYYY).1	Age (years).1	Patient sex.1	Please describe other sex:.1	Patient sex at birth.1	Patient currently pregnant?.1	How many weeks pregnant?.1	Estimated due date.1
2	1		Healthcare Provider / Redcap / ELR	Some	Nnnr	2020000000	grafvr@fzsb.org	Somemsb gb ggi' fvrvr			bxdt	ft		39224	12	Male						4/1/2020	ZZ Health			Someone Someone		43905	0.645833333	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Healthcare Provider / Redcap / ELR	Some	Nnnr	2020000000	grafvr@fzsb.org	Somemsb gb ggi' fvrvr			bxdt	ft		39224	12	Male
3	3		Healthcare Provider / Redcap / ELR	Somes	Nobt	202-000-0000		KCmrmv rdvebrdy			gchrdt	xfgn	C	23462	55	Male						3/26/2020	ZZ Health			Someone Someone		43906	0.402083333	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Healthcare Provider / Redcap / ELR	Somes	Nobt	202-000-0000		KCmrmv rdvebrdy			gchrdt	xfgn	C	23462	55	Male
4	6		Patient								fgnndr	xftj		40830	8	Female			No			4/6/2020	ZZ Health			Someone Someone	Someone S			<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Patient								fgnndr	xftj		40830	8	Female			No
Merge1

If you want to consolidate all the csv files then do this for each and then in Power Query, append each. Then when you make any change to one, it will be updated in the "Master" upon a Refresh. It may be a bit laborious to set up, but will be almost instantaneous when updated.

 

[Enna]

With Power Query which is available to you as a free add-in for XL2010 your can merge (outer join) your two tables on the common ID field.

let
    Source = Table.NestedJoin(Table1, {"REDCap ID"}, Table2, {"REDCap ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1", "Submitter Affiliation", "Point of Care affiliation", "If Other, please provide affiliation", "Submitter Full Name", "Name of Submitter", "Notification Date", "Notification Time", "Is this a:", "Was DC Health Notified through DGMQ / Epi-X?", "DGMQ ID / Epi-X ID", "Please select mode of travel where exposure could have occurred based on DGMQ notification", "Flight Number", "Cruise Name", "Passport Country", "Passport Number", "Key contact related to this person included in DGMQ ", "Date arrived in U.S.", "Last possible exposure date (Day 0)", "Date Monitoring by DC Health Started (may not be Date for Day 1)", "Monitoring end date (14 days after [exposure_end_dt]  )", "How was DC health notified of the case?", "Caller/Provider FIRST name", "Caller/Provider LAST name", "Caller/Provider Phone Number", "Caller/Provider Email", "Caller/Provider Affiliation/Organization", "College/University/School Name:", "If other, please specify", "FIRST name", "LAST name", "MIDDLE Intial", "Date of birth (in the format of MM-DD-YYYY)", "Age (years)", "Patient sex", "Please describe other sex:", "Patient sex at birth", "Patient currently pregnant?", "How many weeks pregnant?", "Estimated due date"}, {"Column1", "Submitter Affiliation", "Point of Care affiliation", "If Other, please provide affiliation", "Submitter Full Name", "Name of Submitter", "Notification Date", "Notification Time", "Is this a:", "Was DC Health Notified through DGMQ / Epi-X?", "DGMQ ID / Epi-X ID", "Please select mode of travel where exposure could have occurred based on DGMQ no", "Flight Number", "Cruise Name", "Passport Country", "Passport Number", "Key contact related to this person included in DGMQ ", "Date arrived in U.S.", "Last possible exposure date (Day 0)", "Date Monitoring by DC Health Started (may not be Date for Day 1)", "Monitoring end date (14 days after [exposure_end_dt]  ).1", "How was DC health notified of the case?.1", "Caller/Provider FIRST name.1", "Caller/Provider LAST name.1", "Caller/Provider Phone Number.1", "Caller/Provider Email.1", "Caller/Provider Affiliation/Organization.1", "College/University/School Name:.1", "If other, please specify.1", "FIRST name.1", "LAST name.1", "MIDDLE Intial.1", "Date of birth (in the format of MM-DD-YYYY).1", "Age (years).1", "Patient sex.1", "Please describe other sex:.1", "Patient sex at birth.1", "Patient currently pregnant?.1", "How many weeks pregnant?.1", "Estimated due date.1"})
in
    #"Expanded Table2"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
1	REDCap ID	Monitoring end date (14 days after [exposure_end_dt] )	How was DC health notified of the case?	Caller/Provider FIRST name	Caller/Provider LAST name	Caller/Provider Phone Number	Caller/Provider Email	Caller/Provider Affiliation/Organization	College/University/School Name:	If other, please specify	FIRST name	LAST name	MIDDLE Intial	Date of birth (in the format of MM-DD-YYYY)	Age (years)	Patient sex	Please describe other sex:	Patient sex at birth	Patient currently pregnant?	How many weeks pregnant?	Estimated due date	Column1	Submitter Affiliation	Point of Care affiliation	If Other, please provide affiliation	Submitter Full Name	Name of Submitter	Notification Date	Notification Time	Is this a:	Was DC Health Notified through DGMQ / Epi-X?	DGMQ ID / Epi-X ID	Please select mode of travel where exposure could have occurred based on DGMQ no	Flight Number	Cruise Name	Passport Country	Passport Number	Key contact related to this person included in DGMQ	Date arrived in U.S.	Last possible exposure date (Day 0)	Date Monitoring by DC Health Started (may not be Date for Day 1)	Monitoring end date (14 days after [exposure_end_dt] ).1	How was DC health notified of the case?.1	Caller/Provider FIRST name.1	Caller/Provider LAST name.1	Caller/Provider Phone Number.1	Caller/Provider Email.1	Caller/Provider Affiliation/Organization.1	College/University/School Name:.1	If other, please specify.1	FIRST name.1	LAST name.1	MIDDLE Intial.1	Date of birth (in the format of MM-DD-YYYY).1	Age (years).1	Patient sex.1	Please describe other sex:.1	Patient sex at birth.1	Patient currently pregnant?.1	How many weeks pregnant?.1	Estimated due date.1
2	1		Healthcare Provider / Redcap / ELR	Some	Nnnr	2020000000	grafvr@fzsb.org	Somemsb gb ggi' fvrvr			bxdt	ft		39224	12	Male						4/1/2020	ZZ Health			Someone Someone		43905	0.645833333	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Healthcare Provider / Redcap / ELR	Some	Nnnr	2020000000	grafvr@fzsb.org	Somemsb gb ggi' fvrvr			bxdt	ft		39224	12	Male
3	3		Healthcare Provider / Redcap / ELR	Somes	Nobt	202-000-0000		KCmrmv rdvebrdy			gchrdt	xfgn	C	23462	55	Male						3/26/2020	ZZ Health			Someone Someone		43906	0.402083333	<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Healthcare Provider / Redcap / ELR	Somes	Nobt	202-000-0000		KCmrmv rdvebrdy			gchrdt	xfgn	C	23462	55	Male
4	6		Patient								fgnndr	xftj		40830	8	Female			No			4/6/2020	ZZ Health			Someone Someone	Someone S			<font color= 'red'>Person under investigation (PUI; symptomatic/asymptomatic)</font>	No												Patient								fgnndr	xftj		40830	8	Female			No
Merge1

If you want to consolidate all the csv files then do this for each and then in Power Query, append each. Then when you make any change to one, it will be updated in the "Master" upon a Refresh. It may be a bit laborious to set up, but will be almost instantaneous when updated.

Thank you. Can it be a vba code without listing all column names from each csv file? Since some column does not have a header too and there are about 600 variables.

 

[alansidman]

You will have to wait for someone with more VBA skills than me. I cannot address that issue. I have provided you with what I have available in my skill bank. I will be leaving this thread for others since you are looking for a different solution.