Combining many .CSV files with a ";" delimiter into a single .XLS file with header row

Dear all

I have a bunch of data in many different .CSV files that I am trying to combine into a single .xls file using a macro. Each .CSV file have a unique name representing the data source and consists of 3 columns: A) timestamp, B) logged/recorded value, and C) Unit (i.e. Volts)

Now to the part that gives me trouble: I would like to create a single .XLS file that contains all the data from the different .CSV files added in consecutive columns. And with the .CSV filename added to an inserted first/top row.

I have found and modified two different macros that will either combine the data into a single .XLS file (sheet) or inset a header for each dataset in the first row of a . XLS sheet, but I have no idea how to make both macros work together as one.

Here is my first macro that adds filenames above each dataset:

Please Login or Register  to view this content.

My next little macro that combines the .CSV files into a single .XLS file looks like this:

Please Login or Register  to view this content.

I sorry if my syntax if offensive or cluttered, but I hope it makes sense?

Best regards, Emil

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Hi emilx3
I am not too familiar with the code from Jerry Beaucaire, or the others. They do come close to what you want. I cannot easily modify them for you as the .csv Files of yours seem to come up in Excel differently depending how I open them. I guess Excel guesses separators to some extent which may work for or against you depending on the weather and the day of the week. Exactly what method you then use complicates the matter.


But i have imported .csv, ( or rather .txt ) files into an Excel Spreadsheet using a fairly simple VBA Code. I have often used the__ ; __ as delimiter in my text files.


If you had those CSV Files as simple .txt files ( In Notebook for example ) with the data looking like this

Start Tid;Forbrug;Enhed
2014-01-01 00:00:00 +0100;31.6999999999971;kWh
2014-01-01 01:00:00 +0100;31.6000000000058;kWh
2014-01-01 02:00:00 +0100;30.8999999999942;kWh

Then, if you run either of the codes i give below, ( you will be asked which text file to select ), after selecting a .txt file, and giving the separator that the text file uses , then you get this which will have as top left the Active cell at the start of running the code. ( here i selected cell C3 arbitrarily before running the code. )

Using Excel 2007

Row\Col	C	D	E
3	Start Tid	Forbrug	Enhed
4	2014-01-01 00:00:00 +0100	31.7	kWh
5	2014-01-01 01:00:00 +0100	31.6	kWh
6	2014-01-01 02:00:00 +0100	30.9	kWh

Ark1

_............

Your approach to using this method would be to incorporate this code within your Dir Loop, such as that in the first code you showed in Post #1
If you wish to consider this approach and have difficulty in doing that then upload some .txt files with just a few rows in, and hand then fill in an excel File showing me exactly how you want the data in the excel file to look based on that data, and i will take a look.

Alan

Basic “stand-alone Codes” : ( They are the same code, one is simplified and the other has detailed explaining ‘Comments.

Please Login or Register  to view this content.

Hi Alan

Thank you very much for your feedback. I have tried to run both your suggested macros, but didn't quite get there yet. As you also suggested I have tried to add code in the DIR loop in the code from my first post, yet I still do something wrong. It seems like no matter what I type I get a compiling error. I do wish to use that solution, but have great difficulty, so further help would be greatly appreciated

I have attached 3 new and much smaller .CSV files and a .XLS file that is manually made to look like how I would like the macro to make it look like. Notice how the filename appears as a header above each dataset in their consecutive columns. Again your help is very much appreciated.

Emil

PS I forgot to mention that my first code in post #1 does import the filenames as text in first row, but the data in the following rows have not been split up into columns. In my second code from post #1 the data from each files is split up and placed into consecutive columns like I wqanted to, but then the filenames are missing in the first row...

Hi Emil,
As I mentioned before, the problem for me is opening .csv Files. I do not know how to control the format it comes up in Excel. It comes up differently every time for me.
_...........................................................

However. If you Take any of your files, say the first one, and save it, but then open it with any Text editor such as Notepad then I get a file looking like this

Start Tid;Forbrug;Enhed
2014-01-01 00:00:00 +0100;31.6999999999971;kWh
2014-01-01 01:00:00 +0100;31.6000000000058;kWh
2014-01-01 02:00:00 +0100;30.8999999999942;kWh
2014-01-01 03:00:00 +0100;30.4000000000015;kWh
2014-01-01 04:00:00 +0100;32.5;kWh

If i then save that as a .txt File and then run any of my codes and select that .txt File, then in Excel I get this:

Using Excel 2007

Start Tid	Forbrug	Enhed
2014-01-01 00:00:00 +0100	31.7	kWh
2014-01-01 01:00:00 +0100	31.6	kWh
2014-01-01 02:00:00 +0100	30.9	kWh
2014-01-01 03:00:00 +0100	30.4	kWh
2014-01-01 04:00:00 +0100	32.5	kWh

Ark1

The rest to get all files as you wish to show in the excel Spreadsheet is fairly straight forward. I can do that for you, if you have difficulties, but my starting point will be to have all those Files as .txt Files

I could probably include in my code something to convert your .csv files to .txt files if that were acceptable.
It would also be a lot easier if all the .csv Files that you wish the program to access are typically held by you in the same Folder, and that there are no other .csv files or .txt files there. ( if there are you other files that should not be considered then I need at least to know their names ) . I do not need to know the name of the folder, but on the other hand if you know it ( the Full File Path and name like this sort of form.......
H:\Excel0202015Jan2016\MrExcel\TextFiles
___....)
Then so much the better.

Have another go at using my code to select a .txt File, such as the one I return. Then let me know what you want me to do further.
https://app.box.com/s/2nf4d2mtsppyr8vek0jttspz4ai057sy



Alan

Hi Emil,

I guess you may have this one done then in the meantime?
If you have it is always good to share the solution.
I just answered an almost identical Thread, jus a lot more complicated.
So while it was fresh in my head, I did a quick mod to it to get it to do what you want.
So i thought it would be a useful contribution to this thread.

_...........................................................

The code I give here is actually much too complicated with extra unnecessary bits that your application does not need. But I will leave it that form for now. The extra stuff may slow things down a bit, but as a prototype to get anyone going on a similar requirement it allows a bit of flexibility. I deliberately am using lots of different ways of doing things as a learning exercise ( for me!! ). I have not extensively tested it yet, but it takes the files you gave me and gives the results you wanted.

So the basic idea is.....

The requirement
Bring any number ( within reason ) of ( Excel ) .csv files in a folder that have a form something like this.............

Using Excel 2007

Row\Col	A	B	C
1	Start Tid	Forbrug	Enhed
2	2014-01-01 00:00:00 +0100	0.01	kWh
3	2014-01-01 01:00:00 +0100	0.01	kWh
4	2014-01-01 02:00:00 +0100	0.01	kWh
5	2014-01-01 03:00:00 +0100	0.01	kWh
6	2014-01-01 04:00:00 +0100	0.01	kWh

Data file 2

________...Into a Master Sheet in a Excel, so , for example, with Three such Files you end up with This

Row\Col	A	B	C	D	E	F	G	H	I	J
1	Data file 1			Data file 2			Data file 3
2	Start Tid	Forbrug	Enhed	Start Tid	Forbrug	Enhed	Start Tid	Forbrug	Enhed
3	2014-01-01 00:00:00 +0100	31.6999999999971	kWh	2014-01-01 00:00:00 +0100	0.0100000000002183	kWh	2014-01-01 00:00:00 +0100	1.0	kWh
4	2014-01-01 01:00:00 +0100	31.6000000000058	kWh	2014-01-01 01:00:00 +0100	0.0100000000002183	kWh	2014-01-01 01:00:00 +0100	1.0	kWh
5	2014-01-01 02:00:00 +0100	30.8999999999942	kWh	2014-01-01 02:00:00 +0100	0.0100000000002183	kWh	2014-01-01 02:00:00 +0100	1.0	kWh
6	2014-01-01 03:00:00 +0100	30.4000000000015	kWh	2014-01-01 03:00:00 +0100	0.00999999999930878	kWh	2014-01-01 03:00:00 +0100	1.0	kWh
7	2014-01-01 04:00:00 +0100	32.5	kWh	2014-01-01 04:00:00 +0100	0.0100000000002183	kWh	2014-01-01 04:00:00 +0100	1.0	kWh
8

Ark1

( That is a screenshot BTW taken for after a run if the code, so it seems to work. )

There can be any amount of columns and rows in the csv files ( within reason )
You are asked for the Folder containing the csv Files
You are asked for the separator ( In your files it was a __ ; ___ )

The code is currently set to put data in a master Worksheet of name “Ark1” in the same file as you put the code in.



The code is here:
http://www.excelforum.com/showthread...t=#post4326024
And here:
http://www.excelforum.com/showthread...t=#post4326025

Note it is all one code. ( I had to split it as it was too big for a single post. ) The second part should be copied directly under the first part in the same Module. That can be a normal Code Module or a Sheet Code module.

I may come back and edit / change it a bit as i notice anything as Ii work on the other Thread i am looking at )

Alan

P.s.
The basic idea wot the code is doing:

Rem 1) Rem 2)

Gets the folder and Separator Info from you

Rem 4 )
Makes a temporary copy of that Folder including all the files in it.

Rem 5 )
This is the “Dir” Loop, doing stuff for each of the csv files the Duh "chucks up"
So for each of them, ( it loops while “Dir” chucks something up )

Changes the .csv to a .txt File ( and Puts it in another Temporary folder ) ( Rem 5a) Rem 5b) )

Rem 5c)
This is the main doing stuff based closely on the original Codes I gave. Just has a Minor mod to put data for each inputted File at the next free column and puts the file name at the top left of each data set.

Hi Emil,
I am not at my Excel computer for a while. So it is difficult to follow your new code.

How about for now I give you the File that i have working. ( there is a lot of other junk in it , just ignore that.) The File should come up in sheet “Ark1”. ( That sheet is Empty. ) If that sheet does not come up then select that sheet.
If you save that File into the same Folder as your 3 Original Sample Files and run the code
Sub EmilDBlxFull()
Then it should give you the results i showed in the screen shots.
See how you get on. Let me know and i will try to give you more help if you need it, maybe around Saturday
Alan
_............................................

File:
https://app.box.com/s/wetlfbj6ua66a483dq7usx4261gtq3qt

Originally Posted by jindon

It should read all rows.

Hi Jindon

I think we are close to a solution Could I ask you to try a couple of my original files? I will need to combine 20 files like those I have uploaded again. There might be a slight problem with a thousand separator, but I think that is easy to fix once the rest is sorted.

Right now, when I run your script with the files I have now uploaded I get the following: billede 2.jpg

Thank you, Emil

Originally Posted by emilx3

.... Thank you very much to everybody that has helped me solve this problem....l

@ Emil
Glad you got there

@ Jindon
Thanks for the great and quick professional alternative. I learnt from your alternatives
Thanks
Alan