Importing data from CSV into Excel

[cr6196]

Hi,

I have been sent some data of daily currency prices however, the quotes have been split up into a cross-section format with each day of prices in a separate .csv file. To use this data, I need it in a time series in one continuous file. So to clarify I would need to somehow extract a price from each .csv file for a certain currency pair and paste it into a new file which would represent the time series of the changes in price of the currency pair at daily intervals over the year.

I can't see where you can add attachments though???

 

[lucigen]

hey cr6196,

so I gather you are basically trying to open .csv files in excel and perform some kind of operation on them. The good news is that an easy (not necessairly fast) solution is that you can just open .csv files with excel (I have mine set to excel by default). If you are performing a number of operations one multiple csv files you can use a macro to open them with the Workbooks.Open("FILEPATH") for each individual or use dir to iterate through files in a certain folder. I am not sure if for the macro you need to have them open by default with excel, but I doubt it. If you make an excel object and force it to open a .csv, it will open

Not really sure what you were talking about with your attachments, but does that solve your problem?

 

[cr6196]

hey cr6196,

so I gather you are basically trying to open .csv files in excel and perform some kind of operation on them. The good news is that an easy (not necessairly fast) solution is that you can just open .csv files with excel (I have mine set to excel by default). If you are performing a number of operations one multiple csv files you can use a macro to open them with the Workbooks.Open("FILEPATH") for each individual or use dir to iterate through files in a certain folder. I am not sure if for the macro you need to have them open by default with excel, but I doubt it. If you make an excel object and force it to open a .csv, it will open

Not really sure what you were talking about with your attachments, but does that solve your problem?

I was hoping to add an attachment so you could see the file I was talking about.

I think that is most of what I needed to know. Could you expand on "use dir to iterate through files in a certain folder" as there are far too many files to do manually (there are around 200)? I have very basic VBA skills so I would appreciate as much detail as possible.

 

[lucigen]

here is some example code. I actually just discovered this in the last 2 weeks, so there may be/probably is a better way to solve this. But this should work. Let me know how it goes for you, and if you have any questions

Private Sub example()
Dim sPath, sFilename As String
Dim xlApp As Excel.Application
Dim xlSht As Excel.Worksheet
sPath = 'Folder containing the .csv files
'adds a \ if it doesnt have one at the end
If Right(sPath, 1) <> "\" Then
    sPath = sPath & "\"
End If
'sets directory and grabs first .csv file (will skip any other file extension for first file
 sFilename = Dir(sPath & "*.csv")
 'while moving thru files
 Do Until sFilename = ""
    Set xlApp = New Excel.Application
    xlApp.Workbooks.Open (sPath & sFilename)
    Set xlSht = xlApp.Sheets(1) 'Sheet index, if its a .csv im guessing its just 1
    
    'from here do whatever you need, ex:
    'this copies from opened csv to the thing running the macro
    'probably don't need Thisworkbook part
    
    A1val = xlSht.Range("A1").Value
    ThisWorkbook.Sheets(1).Range("A1").Value = A1val
    
    'Quit exits so you dont have 200 excel files running
    xlApp.Quit
    'Dir iterates, do NOT put an argument on it
    sFilename = Dir
Loop
'Dump from memory
Set xlSht = Nothing
Set xlApp = Nothing
End Sub