How to extract data from a CSV file?

[hce]

hi everyone

almost everyday i need to open 3 csv files in excel to extract som
data from them. The CSV files contains no headings hence when i opene
them in excel, the first row or first column would already hav
contained the data. I have written a command in excel to extract al
the data from the 3 csv files and paste them individually in eac
worksheet. after running the command, i would them need to go to eac
worksheet and copy the one or two columns of data and then paste the
in another worksheet. I would do the same for the other 2 csv files
this is very tedious and repetitive. in addition, because the csv file
are very big files, once i run the command it would take at least 1
minutes for all the data to be pasted into excel. I have tried for day
to try and figure a command to add to my existing one but i a
unsuccessful. is it possible for me to specify only the column i nee
from each file and only get this column to be pasted into the workshee
i want? i would really appreciate if anyone can help...

cheer

 

[macropod]

Hi hce,

If the data in the csv files always have the same names and layouts, you
could simply you formula references to link to them directly - the same as
when using formulae to reference external workbooks. Then you'd only need to
open the excel workbooks and update the links, which can be automated via
Tools|Options.

Cheers

 

[Dave Peterson]

I'd just open the 3 .csv files and copy them to the correct spot.

You didn't say what columns got copied and where they were pasted. So I copied
columns A:B and pasted in the next available column (based on the last filled in
column in row 1 of the activesheet). I put all the CSV files in the same
folder.

It might give you some more ideas (and you may want to turn calculation to
manual and screenupdating to false. Then run the macro, then turn them back
on. You might see an improvement in speed:

Option Explicit
Sub testme01()

Dim CalcMode As Long
Dim mstrWks As Worksheet
Dim CSVNames As Variant
Dim CSVWkbks() As Workbook
Dim iCtr As Long
Dim myPath As String
Dim DestCell As Range

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With

myPath = "C:\my documents\excel"
If Right(myPath, 1) <> 1 Then
myPath = myPath & "\"
End If

CSVNames = Array("book1.csv", "book2.csv", "book3.csv")
ReDim CSVWkbks(LBound(CSVNames) To UBound(CSVNames))

Set mstrWks = ActiveSheet

For iCtr = LBound(CSVNames) To UBound(CSVNames)
'in case you need to refer to it later
Set CSVWkbks(iCtr) = Workbooks.Open(Filename:=myPath & CSVNames(iCtr))
With mstrWks
Set DestCell = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
End With

With CSVWkbks(iCtr).Worksheets(1)
.Range("a:b").Copy _
Destination:=DestCell
End With

CSVWkbks(iCtr).Close savechanges:=False
Next iCtr

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub