PDA

View Full Version : copy contents of csv files



obriensj
08-20-2008, 02:14 AM
Hi,

Not sure if this can be done but thought I would ask anyway!
I need to be able to open up numerous csv files everyday and copy the contents, minus the header row into a master spreadsheet everyday.
The issue being that everyday the amount of data in these csv files will never be the same. The columns will though, A:S for example
For instance on Monday there may be 50 rows in one file, 70 in the next csv file and say 5 in the next csv file and so on.
On Tuesday there could be 35 rows, 40 in the next and say 10 in the next one.

I need some bit of code that will copy the contents (not the header row) of these csv files into the master spreadsheet and then sort the master spreadsheet by column A.
So on Monday there could be a total of 300 rows, Tuesday a total of 550 rows and so on.

The problem as I see it is that I cannot record a macro to do this because each day I don?t know how many rows I will be copying over into the master spreadsheet and as such I would need to leave a gap of say 5000 rows before I paste in the next csv file which is not really workable.

Any ideas?

Thanks

Bob Phillips
08-20-2008, 02:39 AM
You can't, but it is simple to determine the last row with



With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

obriensj
08-21-2008, 03:22 AM
Thanks. Is it possible to select the last row and all rows above it upto row 2?

Bob Phillips
08-21-2008, 04:44 AM
With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = Range("A2").Resize(LastRow - 1)
End With

obriensj
08-21-2008, 05:26 AM
Thanks

T-J
08-22-2008, 08:50 AM
Hi,

Not sure if this can be done but thought I would ask anyway!
I need to be able to open up numerous csv files everyday and copy the contents, minus the header row into a master spreadsheet everyday.
The issue being that everyday the amount of data in these csv files will never be the same. The columns will though, A:S for example
For instance on Monday there may be 50 rows in one file, 70 in the next csv file and say 5 in the next csv file and so on.
On Tuesday there could be 35 rows, 40 in the next and say 10 in the next one.

I need some bit of code that will copy the contents (not the header row) of these csv files into the master spreadsheet and then sort the master spreadsheet by column A.
So on Monday there could be a total of 300 rows, Tuesday a total of 550 rows and so on.

The problem as I see it is that I cannot record a macro to do this because each day I don?t know how many rows I will be copying over into the master spreadsheet and as such I would need to leave a gap of say 5000 rows before I paste in the next csv file which is not really workable.

Any ideas?

ThanksThis is quite easy to do with ADO. Please see example below with 3 hard coded test files:
Set a reference to ADO library (Tools>References>Microsoft ActiveX Data Objects 2.8 Library)

Sub main()
Cells.Clear

Read_CSV_File "test1.csv"
Read_CSV_File "test2.csv"
Read_CSV_File "test3.csv"

Range("A1").Sort _
Key1:=Worksheets("Sheet1").Columns("A"), Header:=xlGuess

End Sub

Sub Read_CSV_File(strCSVFile As String)
Dim strPathtoTextFile As String
Dim objRecordset As ADODB.Recordset
Dim ws As Worksheet
Dim LastRow As Long
Dim sConnection As String
Dim sSQL As String

Set ws = ThisWorkbook.Worksheets("sheet1")

strPathtoTextFile = ThisWorkbook.Path & "\"

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""

sSQL = "SELECT * FROM " & strCSVFile

Set objRecordset = New ADODB.Recordset

objRecordset.Open sSQL, sConnection, adOpenStatic, adLockReadOnly, adCmdText

If Not objRecordset.EOF Then
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Cells(LastRow + 1, 1).CopyFromRecordset objRecordset
End If

objRecordset.Close
Set objRecordset = Nothing
End Sub

obriensj
08-25-2008, 05:24 AM
Thanks TJ will take a look.