Automatically Import .CSV, Edit, Export as .CSV?

[shottsn]

Howdy howdy.

Any help I could get would be great. I've spent hours trying to figure this one out but I just can't.

This is what I am trying to do to an inventory .CSV

1. Automatically download a .CSV from a URL (I figured out how to do this with Mac's Automator)
2. Open .CSV, remove columns, convert data using formulas
3. Export as a new, edited .CSV

Right now I'm stuck in the 2 and 3 areas. With 2, I have a separate Excel document that populates and performs calculations based on the .CSV that I'm downloading. Because the quantity of inventory items is constantly changing, I have formulas that extend down 1500 rows. When I go to export as the newly edited .CSV, the .CSV includes "" for the empty rows.

This may be a tall order, but how can I automate this entire process? Download, Edit, Export? Any help would be greatly appreciated. Thank you.

[Bernie Deitrick]

Don't fill the 1500 rows - use one cell with a formula, and copy it to match your data, along the lines of this, to match column C to column B of the active sheet (C2 has the formula to copy):

Range("C2").Copy Range("C3:C" & Cells(Rows.Count,"B").End(xlUp).Row)

[shottsn]

Thank you for your response. Is that a script I need to use? I'm not sure how to use that bit of code?

[Bernie Deitrick]

Sorry - it would be a macro:

Please Login or Register  to view this content.

But I honestly don't know a thing about using macros with Excel for the Mac.

[shottsn]

I made the macro that you posted but I still don't understand how it's used. Am I going to need a macro for every column?

[Bernie Deitrick]

No, you don't need separate macros. You did not describe where your data is, and where your formulas are, so if you do that I can modify the macro to fill all the columns with formulas, like this example which will fill 13 columns (N to Z) with formulas to match the data in A to M (based solely on the amount of data in column A)

Sub MatchFormulas2()
Range("N2:Z2").Copy Range("N3:Z" & Cells(Rows.Count,"A").End(xlUp).Row)
End Sub

[shottsn]

Row 1 = Titles
Row 2-~900 is filled with entries.
Right now, the formulas are referencing from another workbook and I download and replace every day to update the inventory.

The formulas used in A2-Q2 can all be used throughout their entire column. Except for Row 1. The formulas vary by column though, so the A formula cannot be used for B, vice versa.


Example formula
=IF(ISBLANK('Server HD:Users:me:Downloads:Inventory:[Inventory.xlsx]Sheet1'!A2),"",'Server HD:Users:me:Downloads:Inventory:[Inventory.xlsx]Sheet1'!A2)

[Bernie Deitrick]

You can convert the formulas to values and delete any cells that are blank prior to export, if your formulas return blanks only at the bottoms of columns:

Please Login or Register  to view this content.