open the csv file and merge them into sheet

1) Here is a link to a free utility you can use to split any text/csv file into smaller files, manageable in Excel.
http://www.fxfisherman.com/forums/fo...csv-files.html

2) Here is a link to a macro that will import all the CSV files in a single folder into an Excel workbook putting each imported csv on a sheet of its own in the workbook:
https://sites.google.com/a/madrocket...csvs-to-sheets

So your file is not a CSV. It's a text file. I guess I'd have to see a couple of them to know what to suggest.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Here's the 2nd macro with a line added to split the imported data using the TextToColumns function of the Data tab. Notice the "array()" entries? Each one tells Excel how to interpret each incoming field.

1=General, do what appears correct
2=Treat as Text
3=Treat as Date

For now only array items 6 & 7 are treated as dates, items 12 & 13 are specifically treated as text. The rest are General. You can edit those as needed if you spot odd display results in your output.

Option Explicit

Sub ImportCSVTextFiles()
'Author:    Jerry Beaucaire
'Date:      8/16/2010
'Summary:   Import all CSV files from a folder into separate sheets
'           named for the CSV filenames
'Update:    2/8/2013   Macro replaces existing sheets if they already exist in master workbook
Dim fPath   As String
Dim fCSV    As String
Dim wbCSV   As Workbook
Dim wbMST   As Workbook

Set wbMST = ThisWorkbook
fPath = "C:\Temp\CSV\dolphino\"                  'path to CSV files, include the final \
Application.ScreenUpdating = False  'speed up macro
Application.DisplayAlerts = False   'no error messages, take default answers
fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    On Error Resume Next
    Do While Len(fCSV) > 0
        Set wbCSV = Workbooks.Add(fPath & fCSV)                     'open a CSV file, then split the data by ~
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="~", _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 3), _
            Array(7, 3), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 2), Array(13, 2), _
            Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
            Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), _
            Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
            Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1)), TrailingMinusNumbers:=True
        wbMST.Sheets(ActiveSheet.Name).Delete                       'delete sheet if it exists
        ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count)    'move new sheet into Mstr
        Columns.AutoFit             'clean up display
        fCSV = Dir                  'ready next CSV
    Loop
  
Application.ScreenUpdating = True
Set wbCSV = Nothing
End Sub