Importing from a larger csv

[Guest]

Our main corporate system produces a csv containing approx 80 columns of
data, pretty much everything you would ever want to know about our
transactions.

We need a couple of easy-to-use management reports that would require/be
based on only about 20 of these fields.

We have set this can set up the formats in Excel, no problem. The challenge
lies in filtering the csv data when it is imported into Excel. It is a real
pain to individually select all those 60-odd columns that we do not need, and
strip them out.

Is there any way of automating this, as part of the import (get external
data) process, rather than doing it manually every time we feed Excel with
the updated data?
Many thanks
CW

 

[Guest]

CW,

You could write an XLA that deletes/hides the columns of data you do not
desire.

 

[Guest]

Galimi -
I guess I could, maybe, if I knew what an XLA was !!!
I looked it up in the inbuilt Help but it didn't clarify it.
Please could you explain / describe what this is and how it is done?
Many thanks
CW

 

[Guest]

An XLA is essentially a hidden spreadsheet which can be loaded behind the
scenes, typically exposing a command bar with a menu of options. In your
case, you could build a button that would execute a subroutine in the XLA
that would cycle through the columns of data and delete/hide the ones you do
not want. Following is an example of the code that would hide the columns
you don't desire:

Sub remove()
For Each cl In Range("a1:z1")
If cl.Value = "Unwanted" Then
ActiveSheet.Columns(cl.Column).Hidden = True
End If
Next
End Sub

I would modify the condition where Unwanted is tested to compare against a
list of headings for the columns you do not want. Cycling through a
collection would work.

 

[Dave Peterson]

I'd do this:

Start a new workbook
turn on the macro recorder
(tools|macro|record new macro)

Open your csv file
delete the 60 columns you don't want
continue any other formatting you need (headers/filters/page setup/etc)
stop recording the macro

Close the newly opened CSV file.

Add a button from the Forms toolbar to the first sheet of this macro workbook.
Assign the button to the recorded macro.

Save this workbook with a nice name:
ManagementReportDataImporter.xls

Now click that button and see if it does what you want.

 

[Earl Kiosterud]

CW,

If you're re-importing the same named file, you need only mark the unwanted
columns as "skip" in the Import specs. Then the next time you want to
import it, use Refresh (right-click in the data area, choose "Refresh"). If
it's a different named file with the same layout (same skipped column),
right-click the data area, choose "Edit Text import" and change the file
name. You can also turn on "Prompt for file name on refresh" in "Data Range
Properties, and it'll prompt for the file to be imported each time you
import.