Naresh Patel
asked on
Combine .csv
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't get "import.csv if exist then add data from available blank row". Can you try to explain?
ASKER
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
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
ASKER
Apology For Delay
ASKER
Need more clarifications?
File 4 could be an issue. A CSV file should be homogenous in its structure (i.e. always provide values for all columns).
ASKER
ok then consider file 4 is same as others....
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.
ASKER
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
after importing data in to import.csv sort it column A.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Do u mean this
Considering D - AmiBrokerData - Test is path of .csv files
Thanks
sFilename = Dir(wb.Path & "\*.csv")
sFilename = Dir(D:\AmiBroker Data\Test& "\*.csv")
Considering D - AmiBrokerData - Test is path of .csv files
Thanks
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.
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.
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")
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.
This comment
'Tools, References and select Microsoft ActiveX Data Objects x.x Object Library.
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.
ASKER
Selected& this code run nothing happen as well as no error
Please suggest what to do or what mi missing.
Thanks
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
Please suggest what to do or what mi missing.
Thanks
ASKER
?
ASKER
Do u want me to close this question ?
Thanks
Thanks
Is the path used correct, and having CSV files in it?
Go thru the code stepwise (F8) to see what is happening.
Go thru the code stepwise (F8) to see what is happening.
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.
ASKER
Any aproch I don't mind....
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".
The question has either no comments or not enough useful information to be called an "answer".
ASKER
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
Thanks
ASKER
Thanks