Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Compile daily CSV files into Excel sheet on a specific tab with headers.  Possible?

Posted on 2012-08-22
16
Medium Priority
?
677 Views
Last Modified: 2012-08-24
Hi Experts!  Novice here...

I have a daily CSV export (Email by country.csv) with the column names.  Everyday I manually paste the new data into an XLS document (Email.xlsx).

I was wondering if I can automate this process by dropping the CSV into a folder and when I open the XLS, have a script (macro) run that checks a folder and imports csv data below the existing data in the XLS.

The other catch is that the data is on the second tab of the Excel sheet.

I'm running Office Excel 2007.

Thanks in advanced for any assistance!
Email.xlsx
Email-by-Country.csv
0
Comment
Question by:trixits
  • 9
  • 6
16 Comments
 
LVL 1

Author Comment

by:trixits
ID: 38323102
Sorry, just to clarify further the complications I see...

1.  The CSV has headers.  I don't want to keep adding headers to the destination worksheet.  I just want the data.  So the import would start from row 2 of the CSV file.

2.  The destination worksheet is on the second tab of the workbook (Email.xls)

3.  I have the DATE as the first column on the destination worksheet.  The date column is NOT in the CSV file.  So the CSV columns A thru H, need to go into the XLS columns B thru I.

4.  It would be extra spiffy if the Date could be added automatically too.  :D
0
 
LVL 18

Expert Comment

by:Rartemass
ID: 38323184
The easiest way to do this is to record a macro.
Open Excel and go to the Developer tab on the ribbon.
To make the Developer tab visible go into the Excel Options and check "Show Developer tab in the Ribbon" in the Popular section.

Click Record Macro and name the macro something meaningful. Enter a shortcut key (eg ctrl+m). Store the macro in your personal macro workbook. This should enable the macro for any workbook you open. Click OK then perform the following:
-Open the XLS file.
-Open the CSV file.
-Perform the copy and paste operations manually as normal.
-Save the XLS file.
-Close the CSV file and the XLS file.
-Click Stop recording

Now when you have a new CSV file (ensure it has the same name each day) open excel and press ctrl+m. The macro will perform the operation for you.

What the above requires is that the CSV file has the same name, the same layout everytime, and the cells to copy are all the same range.
To allow for a change in the number of rows, simply select a number of rows that exceeds your expected maximum row count. For example, if you expect rows to be between 10 and 200, then when recording the macro select 200 rows, even if they are blank. This way it will grab all the data.
0
 
LVL 1

Author Comment

by:trixits
ID: 38323216
Thanks, Rartemass.  

I guess I was hoping for a solution that allowed me to drop a CSV into a folder and open the file and run a macro.  The reason being is that this is a really simplified version of my situation.

My Excel document has many tabs, with many CSVs exported from various companies in different formats, with BI charts on a dashboard on the first tab.  I was hoping to adapt this solution to use for all my CSVs at once.  Rather than having to run a macro one at a time for each company.  

Is that still the best possible solution?  I am comfortable adding VB modules and running macros.
0
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!

 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38323225
Hi, trixits.

Please see attached (code below).

I have taken the liberty of making some changes...
(1) I reckoned that you might want to open the file sometimes without adding a CSV, so instead of the automatic update, I've given you a Blue button on the "Email" sheet.
Of course, I can make it automatic if that's what you want.
(2) The "Email by Country" sheet has a number of extra columns with formulas - so the macro writes these for each line added from the CSV file. Also, two of these formulas gave rise to Divide by Zero errors for some entries so I put a test in to avoid that.
(3) The last line in the CSV file is a "Grand Total". I assumed that you didn't want that (it'd cause double counting in your totals) so I simply drop the last line.
(4) The macro updates the totals in row 1 of the "Email by Country" sheet to reflect the new last line. (Please note that the totals in G1 and L1 were adding ratios and were therefore meaningless so I dropped them.)

The code...
Option Explicit

Sub Process_CSV()
Dim xCSVFileName  As Variant
Dim xCSVFile      As Workbook
Dim xOutput       As Worksheet
Dim xCSV_Last_Row As Long
Dim xOut_Last_Row As Long
Dim xMagic        As Long

' Find Output sheet and its last row...
Set xOutput = ThisWorkbook.Sheets("Email by Country")
xOut_Last_Row = xOutput.Range("A1").SpecialCells(xlLastCell).Row
xMagic = xOut_Last_Row + 1

' Prompt user for the name of the CSV file...
xCSVFileName = Application.GetOpenFilename(filefilter:="CSV Files (*.csv), *.csv", MultiSelect:=False)
If xCSVFileName = False Then
    MsgBox "No file selected. Run terminated."
    Exit Sub
End If

' Open the CSV file and find its last row...
Set xCSVFile = Workbooks.Open(xCSVFileName)
xCSV_Last_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

' Copy CSV cells to Output...
ActiveSheet.Range("A2:H" & xCSV_Last_Row - 1).Copy xOutput.Range("B" & xMagic)
xOutput.Range("A" & xMagic) = Format(Now(), "DD/MM/YYYY")
xOutput.Range("A" & xMagic).Copy xOutput.Range("A" & xMagic & ":A" & xOut_Last_Row + xCSV_Last_Row - 2)

' Setup formulas for the new cells...
xOutput.Range("J" & xMagic).Formula = ""
xOutput.Range("K" & xMagic).Formula = "=F" & xMagic & "*$J$2"
xOutput.Range("L" & xMagic).Formula = "=IF(E" & xMagic & "=0,"""",K" & xMagic & "/E" & xMagic & ")"
xOutput.Range("M" & xMagic).Formula = "=IF(C" & xMagic & "=0,"""",D" & xMagic & "/C" & xMagic & ")"
xOutput.Range("J" & xMagic & ":M" & xMagic).Copy xOutput.Range("J" & xMagic & ":M" & xOut_Last_Row + xCSV_Last_Row - 2)
xMagic = xOut_Last_Row + xCSV_Last_Row - 2
xOutput.Range("F1").Formula = "=SUM(F3:F" & xMagic & ")"
xOutput.Range("K1").Formula = "=SUM(K3:K" & xMagic & ")"

' Close the CSV File...
xCSVFile.Close

End Sub

Open in new window

Regards,
Brian.Email-V2.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38323233
trixits,

Crossing posts. If my code is essentially correct then it can be easily modified to handle multiple CSV files. The prompt would then allow multiple selections.

Regards.
Brian.
0
 
LVL 1

Author Comment

by:trixits
ID: 38323259
Thanks, Brian!  I'll try this now.

I assume I just have to have the CSV files in the same folder as the XLS?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38323271
trixits,

I assume I just have to have the CSV files in the same folder as the XLS?
No, it's a standard "File Open" dialogue, so you can navigate to the required folder.

Edit: BTW, should the data from the "Grand Total" row be copied to the "Email" sheet?

Regards,
Brian.
0
 
LVL 1

Author Comment

by:trixits
ID: 38326282
Okay, THANK YOU, Brian!  This answers my question stated.

It's going to take me a little bit to see if I can modify this further to accommodate my situation.  I'm going to take a swing at it and see where I break the code.  :P
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38326499
trixits,

Take your time - as long as you're having fun!

(Sure you don't want me to add the multi-CSV processing?)

Regards,
Brian.
0
 
LVL 1

Author Comment

by:trixits
ID: 38326935
Is there any way that the Date in the first column can be a prompted value?  
And have the default value be today's date?
0
 
LVL 1

Author Closing Comment

by:trixits
ID: 38326942
Super fast and this works perfectly.  I'll try to adapt it to my exact situation (which include multiple tabs with multiple CSVs containing different data ranges).  THANK YOU!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38327102
Thanks, trixits!

(1) Yes, the InputBox function prompts the user for a string.

(2) I keep an eye on "my" closed questions for at least a couple of weeks, so if you have any queries/issues with (1) or anything else, please feel free to post here.

Regards,
Brian.
0
 
LVL 1

Author Comment

by:trixits
ID: 38327225
Oh thank you.  The InputBox function worked perfectly.

I do have another question.

I have countries in the CSV which contain all zero data (No Impressions).  
Is there an easy way to skip rows where Impressions = 0 (B column)?

Let me know if this needs to be a new question...
0
 
LVL 1

Author Comment

by:trixits
ID: 38327499
I added to sort the Impressions descending prior to copying the cell range...

ActiveSheet.Range("A2:H" & xCSV_Last_Row - 1).Sort Key1:=Range("A2:H" & xCSV_Last_Row - 1).Offset(0, 1).Resize(1, 1), Order1:=xlDescending, Header:=xlYes

Open in new window


Not sure if this is the best way to go about it...

How do I redefine the xCSV_Last_Row to check the B column, and stop when it hits a zero (0) value?

Alternatively, a null value in G or H column can also be used as this 'stop' parameter.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38329846
trixits,

Please see attached. The code is...
Option Explicit

Sub Process_CSV()
Dim xCSVFileName  As Variant
Dim xCSVFile      As Workbook
Dim xOutput       As Worksheet
Dim xCSV_Last_Row As Long
Dim xOut_Old_Last As Long
Dim xOut_New_Last As Long
Dim i             As Long

' Find Output sheet and its last row...
Set xOutput = ThisWorkbook.Sheets("Email by Country")
xOut_Old_Last = xOutput.Range("A1").SpecialCells(xlLastCell).Row
xOut_New_Last = xOut_Old_Last

' Prompt user for the name of the CSV file...
xCSVFileName = Application.GetOpenFilename(filefilter:="CSV Files (*.csv), *.csv", MultiSelect:=False)
If xCSVFileName = False Then
    MsgBox "No file selected. Run terminated."
    Exit Sub
End If

' Open the CSV file and find its last row...
Set xCSVFile = Workbooks.Open(xCSVFileName)
xCSV_Last_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

' Copy "non-zero" CSV cells to Output...
For i = 2 To xCSV_Last_Row
    If Cells(i, 2) <> 0 And Cells(i, 1) <> "Grand Total" Then
        xOut_New_Last = xOut_New_Last + 1
        Range("A" & i & ":H" & i).Copy xOutput.Range("B" & xOut_New_Last)
    End If
Next

If xOut_Old_Last = xOut_New_Last Then
    
    MsgBox ("No ""non-zero"" records found in " & xCSVFileName & ".")
    
Else

    ' Write Date to new records...
    xOutput.Range("A" & xOut_New_Last) = Format(Now(), "DD/MM/YYYY")
    xOutput.Range("A" & xOut_New_Last).Copy xOutput.Range("A" & xOut_Old_Last + 1 & ":A" & xOut_New_Last)
    
    ' Setup formulas for the new cells...
    xOutput.Range("J" & xOut_New_Last).Formula = ""
    xOutput.Range("K" & xOut_New_Last).Formula = "=F" & xOut_New_Last & "*$J$2"
    xOutput.Range("L" & xOut_New_Last).Formula = "=IF(E" & xOut_New_Last & "=0,"""",K" & xOut_New_Last & "/E" & xOut_New_Last & ")"
    xOutput.Range("M" & xOut_New_Last).Formula = "=IF(C" & xOut_New_Last & "=0,"""",D" & xOut_New_Last & "/C" & xOut_New_Last & ")"
    xOutput.Range("J" & xOut_New_Last & ":M" & xOut_New_Last).Copy xOutput.Range("J" & xOut_Old_Last + 1 & ":M" & xOut_New_Last)
    xOutput.Range("F1").Formula = "=SUM(F3:F" & xOut_New_Last & ")"
    xOutput.Range("K1").Formula = "=SUM(K3:K" & xOut_New_Last & ")"
    
    MsgBox (xOut_New_Last - xOut_Old_Last & " records added from " & xCSVFileName & ".")
    
End If

' Close the CSV File...
xCSVFile.Close

End Sub

Open in new window

Regards,
Brian.Email-V3.xlsm
0
 
LVL 1

Author Comment

by:trixits
ID: 38331319
Thank you, once again, Brian!

I asked a new question here... related to creating charts from this newly imported data.
If you or anyone can help, I'd greatly appreciate it!
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27841839.html
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 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