?
Solved

Combine .csv

Posted on 2014-11-12
25
Medium Priority
?
154 Views
Last Modified: 2014-12-19
Hi Experts,

Need a piece of code which combine all .csv file in one Import.csv file from one location (Except header i.e only header from 1st csv file & from rest ignore)

csv files location is "D:\AmiBroker Data\NSEeq" there are 500 files.

thanks
0
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 6
  • 3
  • +1
25 Comments
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 1000 total points
ID: 40437018
What are the constraints and means? Are you only interested in getting the result file, or do you need to embed that into Excel VBA or the like?
E.g. PowerShell:
$src = 'D:\AmiBroker Data\NSEeq\*.csv'
$dst = 'D:\AmiBroker Data\NSEeq Compbined.csv'
$skiplines=0
remove-item $dst
get-childitem $src | % {
  get-content $_ | select -skip $skiplines | out-file -append $dst
  $skiplines=1
}

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40437060
Need excel VBA code from which I execute code. Code combine all .csv file from location in to import.csv. import.csv if exist then add data from available blank row else Crete New and add.

Thanks
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40437127
I don't get "import.csv if exist then add data from available blank row". Can you try to explain?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Author Comment

by:Naresh Patel
ID: 40437629
Mr.Qlemo,

Forget that part I need on button click code execute -
which open or whatever .csv files & create one import.csv file which include all data of all .csv files.
header required from 1st file and from rest of the files header need to be excluded. one more thing after combing sort the data for column A.I had attached sample files and final output file requesting you to look in.

mine csv files location is "D:\AmiBroker Data\NSEeq"

Thank you
1.csv
2.csv
3.csv
4.csv
import.csv
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40437636
Apology For Delay
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40438165
Need more clarifications?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40438192
File 4 could be an issue. A CSV file should be homogenous in its structure (i.e. always provide values for all columns).
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40438252
ok then consider file 4 is same as others....
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40438358
Is the amount of data in each file representative? The sort is an issue here - we require to load the data into Excel for sorting, or use DOS tools to perform it. Both might lead to hitting some memory limits, or perform very bad.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40438451
Prefer to load data in to excel...yes I know "Both might lead to hitting some memory limits, or perform very bad. "
after importing data in to import.csv sort it column A.

Thanks
0
 
LVL 8

Accepted Solution

by:
khobson earned 1000 total points
ID: 40439004
Change wb.Path to your folder path without the "\" and see if this helps.

Sub GetCSVData()
    Dim sFilename As String, sql As String
    Dim tCell As Range, wb As Workbook
    
    Set wb = ThisWorkbook
    On Error GoTo EndSub
    sFilename = Dir(wb.Path & "\*.csv")
    Do While sFilename <> ""
        sql = "SELECT * FROM " & """" & sFilename & """"
        Set tCell = Range("B" & Rows.Count).End(xlUp).Offset(1)
        GetTextFileData sql, wb.Path, tCell
        If tCell <> Empty Then Range("A" & tCell.Row).Value2 = GetBaseName(sFilename)
        sFilename = Dir()
    Loop
    FillColumnA
    ActiveSheet.UsedRange.Columns.AutoFit
EndSub:
  
End Sub
 
 'http://www.exceltip.com/st/Import_data_from_a_text_file_%28ADO%29_using_VBA_in_Microsoft_Excel/430.html
Sub GetTextFileData(strSQL As String, strFolder As String, _
    rngTargetCell As Range, Optional hdr As Boolean = False)
     'Tools, References and select Microsoft ActiveX Data Objects x.x Object Library.
     ' example: GetTextFileData "SELECT * FROM filename.txt", _
    "C:\FolderName", Range("A3")
     ' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
    "C:\FolderName", Range("A3")
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
    If rngTargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=" & strFolder & ";" & _
    "Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    If hdr Then
         ' the field headings
        For f = 0 To rs.Fields.Count - 1
            rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
        Next f
        rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
    Else
        rngTargetCell.CopyFromRecordset rs
    End If
     'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Sub FillColumnA()
  Dim s As String, cell As Range, i As Long
  s = Range("A2").Value2
  For i = 2 To ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    If Range("A" & i).Value2 = "" Then
      Range("A" & i).Value2 = s
      Else: s = Range("A" & i).Value2
    End If
  Next i
End Sub
 
Sub MakeTXTFile(filePath As String, str As String)
    Dim hFile As Integer
     'If Dir(GetFolderName(filePath), vbDirectory) = "" Then
     '  MsgBox filePath, vbCritical, "Missing Folder"
     '  Exit Sub
     'End If
     
    hFile = FreeFile
    Open filePath For Output As #hFile
     'If str <> "" Then Write #hFile, , str
    If str <> "" Then Print #hFile, str
    Close hFile
End Sub
 
Function GetFileName(Filespec As String)
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    GetFileName = FSO.GetFileName(Filespec)
End Function
 
Function GetFolderName(Filespec As String) 'Returns path with trailing "\"
     'Requires GetFileName() function above
    GetFolderName = Left(Filespec, Len(Filespec) - Len(GetFileName(Filespec)))
End Function
 
Function GetBaseName(Filespec As String)
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    GetBaseName = FSO.GetBaseName(Filespec)
End Function

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40440472
Do u mean this
sFilename = Dir(wb.Path & "\*.csv")

Open in new window


sFilename = Dir(D:\AmiBroker Data\Test& "\*.csv")

Open in new window


Considering D - AmiBrokerData - Test is path of .csv files

Thanks
0
 
LVL 8

Expert Comment

by:khobson
ID: 40441712
Sort of, you left out some quotes.  I like to add the Compile button to the VBE toolbar and compile programs before running them.  

I can test your example files if you like but what I posted should get you very close.

sFilename = Dir("D:\AmiBroker Data\Test" & "\*.csv")

Open in new window


Be sure to note my comments where I explain how to set the references to the objects used.  Otherwise, you will get a reference error.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40441947
i got error ....will you pls attaché file with code...errorThanks
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40446296
This comment
    'Tools, References and select Microsoft ActiveX Data Objects x.x Object Library.

Open in new window

immediately above the line showing the error is intended to tell you what you need to do, but is missing one line (I suppose). In VBA Editor, select References in the Tools menu and select Microsoft ActiveX Data Objects x.x Library, where x.x is a version number - best to choose the one with the highest version, unless you need to be compatible with other machines having to run the code, in which case you need to select the highest found on all machines in question.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40447809
SelectedAs you suggested& this code run nothing happen as well as no error
Sub GetCSVData()
    Dim sFilename As String, sql As String
    Dim tCell As Range, wb As Workbook
    
    Set wb = ThisWorkbook
    On Error GoTo EndSub
    sFilename = Dir("D:\AmiBroker Data\Test" & "\*.csv")
    Do While sFilename <> ""
        sql = "SELECT * FROM " & """" & sFilename & """"
        Set tCell = Range("B" & Rows.Count).End(xlUp).Offset(1)
        GetTextFileData sql, wb.Path, tCell
        If tCell <> Empty Then Range("A" & tCell.Row).Value2 = GetBaseName(sFilename)
        sFilename = Dir()
    Loop
    FillColumnA
    ActiveSheet.UsedRange.Columns.AutoFit
EndSub:
  
End Sub
 
 'http://www.exceltip.com/st/Import_data_from_a_text_file_%28ADO%29_using_VBA_in_Microsoft_Excel/430.html
Sub GetTextFileData(strSQL As String, strFolder As String, _
    rngTargetCell As Range, Optional hdr As Boolean = False)
     'Tools, References and select Microsoft ActiveX Data Objects x.x Object Library.
     ' example: GetTextFileData "SELECT * FROM filename.txt", _
    "C:\FolderName", Range("A3")
     ' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
    "C:\FolderName", Range("A3")
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
    If rngTargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=" & strFolder & ";" & _
    "Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    If hdr Then
         ' the field headings
        For f = 0 To rs.Fields.Count - 1
            rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
        Next f
        rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
    Else
        rngTargetCell.CopyFromRecordset rs
    End If
     'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Sub FillColumnA()
  Dim s As String, cell As Range, i As Long
  s = Range("A2").Value2
  For i = 2 To ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    If Range("A" & i).Value2 = "" Then
      Range("A" & i).Value2 = s
      Else: s = Range("A" & i).Value2
    End If
  Next i
End Sub
 
Sub MakeTXTFile(filePath As String, str As String)
    Dim hFile As Integer
     'If Dir(GetFolderName(filePath), vbDirectory) = "" Then
     '  MsgBox filePath, vbCritical, "Missing Folder"
     '  Exit Sub
     'End If
     
    hFile = FreeFile
    Open filePath For Output As #hFile
     'If str <> "" Then Write #hFile, , str
    If str <> "" Then Print #hFile, str
    Close hFile
End Sub
 
Function GetFileName(Filespec As String)
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    GetFileName = FSO.GetFileName(Filespec)
End Function
 
Function GetFolderName(Filespec As String) 'Returns path with trailing "\"
     'Requires GetFileName() function above
    GetFolderName = Left(Filespec, Len(Filespec) - Len(GetFileName(Filespec)))
End Function
 
Function GetBaseName(Filespec As String)
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    GetBaseName = FSO.GetBaseName(Filespec)
End Function

Open in new window


Please suggest what to do or what mi missing.

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40451432
?
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40455519
Do u want me to close this question ?


Thanks
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40455629
Is the path used correct, and having CSV files in it?
Go thru the code stepwise (F8) to see what is happening.
0
 
LVL 8

Expert Comment

by:khobson
ID: 40458638
I have been out of pocket traveling for work.  I did test with your files a bit and a similar approach but more simplified might work better for you.  It would still use ADO.  If you are still interested, I can work on that for you.  I have an idea for another approach or two as well.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40461766
Any aproch I don't mind....
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40509403
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 40509404
Sorry I don't understand much .....but it is not right to delete this question as some one devoted time in this. so apology administrator but I am accepting both comments as Answer.

Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question