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. 


'Option Explicit'Comment Out For Simplified code.
Sub SHimpGlifiedBasicMacroTextToExcel()
On Err GoTo TheEnd
  DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,")
    If DtaFleNm = False Then Exit Sub
   Sp = Application.InputBox("Enter a separator character.", Type:=2)
    If Sp = vbNullString Then Exit Sub
 StCm = ActiveCell.Column
 Rw = ActiveCell.Row
  Open DtaFleNm For Input As 3
    Do Until EOF(3)
    Line Input #3, DtaFleLn
    If Right(DtaFleLn, 1) <> Sp Then DtaFleLn = DtaFleLn & Sp
     Cm = StCm
     TxtPs = 1
     SpPs = InStr(TxtPs, DtaFleLn, Sp)
      While SpPs >= 1
       TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs)
       ActiveSheet.Cells(Rw, Cm).Value = TxtDta
       TxtPs = SpPs + 1
       Cm = Cm + 1
       SpPs = InStr(TxtPs, DtaFleLn, Sp)
      Wend
     Rw = Rw + 1
    Loop
TheEnd:
  Close 3
End Sub
'
'
'

Sub BasicMacroTextFileToExcel() '     http://www.mrexcel.com/forum/excel-questions/795675-run-macro-txt-file.html#post3917591
' Very Simply alternative to the Spreadsheet way using Wizzard, Opening as text File in Excel  etc
'
'First Bit  can  be ommited if Fielnames are always known. It basically gets all the File info.
'Note text File And Excel File must not be in the same Directory as you are being asked for it here.
On Err GoTo TheEnd ' If anything goes wrong go to the end rather than crasching!
'
'First Bit: TextDataFileName and seperator input
Dim Sp As String ' Assign an initial location where further info about the string will be added later
Dim DtaFleNm As Variant 'The DataFilename itself is a string. But a Variant is specified as you may have a Boolean input in the Dialogue box if the user cancels
  DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,") 'The Application Method GetOpenFilename Displays the standard Open dialog box. The optional arguments:= writes a name,limits type of files looked for
    If DtaFleNm = False Then Exit Sub 'Stops Porgram if User Cancels (which gives the boolean False)
  Let Sp = Application.InputBox("Enter a separator character.", Type:=2) 'prompts user for separator character
    If Sp = vbNullString Then Exit Sub 'Stops Porgram if User Cancels (which gives an empty string)
'End First Bit TextDataFileName and seperator input
'
'    'If you want to skip the first bit, delete it or comment it out and type in the text File path and seperator in lines like this:
'    Dim DtaFleNm As String
'    Let DtaFleNm = ThisWorkbook.Path & "\TextDataFile.txt"
'    Dim Sp As String
'    Let Sp = "|"
'    'Note: If you do it this way you should put the Active Excel File and Data Text File in the same Directory
'
'Start Main Bit: Get data from text file  line by line, then for each line go through every Column. As each data bit is found it is put in the approriate place in the Excel File
'Before you start You should select cell
Dim Rw As Long, StCm  As Long, Cm As Long 'Whole Numbers for rows and columns where the Split data line from the Text file goes.  Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.
Let StCm = ActiveCell.Column 'where you want to start data to come in. Column is then reset back to left in loop
Let Rw = ActiveCell.Row 'Row increases downwards so the initial and loop count can be same variable
Dim DtaFleLn As String 'Using our method we bring in a line from the text File at a time
Dim TxtPs As Byte 'text character position in text file looking form left
Dim SpPs As Byte 'position of seperater lookinf from left
Dim TxtDta As String 'String used temporarily for each Text data value
  Open DtaFleNm For Input As 3 ' Sort of opens up a data highway and gives it route number (here 3) to distinguisch it from any others currently open. (If you do not know how many Highways are already open and have a number, then use instead of a number someting like #HgWyNm here, and HgWyNm everyhwere where the number is. This gives it the next available number (Remember to Dim it at the start also.))
    Do Until EOF(3) 'Do until going your at the end of the data highway
    Line Input #3, DtaFleLn 'Bring in a line from text file(Automatically goes to next line or next part of highway after this command. So we need mo row count for the data file.)
    If Right(DtaFleLn, 1) <> Sp Then DtaFleLn = DtaFleLn & Sp 'Glue a seperator on the end of the data file line if there is not one there
    Let Cm = StCm 'go to first column
    Let TxtPs = 1 ' Initial Data Text Position is at 1 (Ist Left poisition in text file line
    Let SpPs = InStr(TxtPs, DtaFleLn, Sp) 'Find position of next seperator
      While SpPs >= 1 'As long as a next Seperator position is found, we go througth columns looking for data
      Let TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs) 'this gives the next data value
      Let ActiveSheet.Cells(Rw, Cm).NumberFormat = "@" 'This is not allways necerssary but can help avoid annoying problems with data turning into times or dates
      Let ActiveSheet.Cells(Rw, Cm).Value = TxtDta 'Put Text data in excel cell
      Let ActiveSheet.Cells(Rw, Cm).Value = Trim(ActiveSheet.Cells(Rw, Cm).Value) 'Most not necerssary but can help to clean up data if any extra spaces came in
      Let TxtPs = SpPs + 1 'Start of next data is just after next seperator
      Let Cm = Cm + 1 'Increase Excel Column number
      Let SpPs = InStr(TxtPs, DtaFleLn, Sp) 'Get the next Seperator position by starting at the current data and going right until the next seperator is found
      Wend 'go to next text data to the right in the data file Line
    Let Rw = Rw + 1 'Increase row number for Excel(The tecxt file automatically goes to the next row after reading a line
    Loop 'Go to the next Row in the Data Text file
TheEnd:
  Close 3 'Very important to do this. Shuts highway Off. There can be strange errors if you do not do that. (It is here so that even after an error it will be done
End Sub 'BasicMacroTextFileToExcel()