Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing from a larger csv
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing from a larger csv
CW,
You could write an XLA that deletes/hides the columns of data you do not desire. -- http://HelpExcel.com 516-984-0252 "CW" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing from a larger csv
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 desi 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. -- http://HelpExcel.com 516-984-0252 "CW" wrote: 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 "galimi" wrote: CW, You could write an XLA that deletes/hides the columns of data you do not desire. -- http://HelpExcel.com 516-984-0252 "CW" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing from a larger csv
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. CW wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing from a larger csv
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. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "CW" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Importing information from 2 workbooks into a 3rd one | Excel Worksheet Functions | |||
Importing information from 2 workbooks into a 3rd one | Excel Discussion (Misc queries) | |||
Drop-down list Populated by a Subset of a larger list | Excel Worksheet Functions | |||
IMPORTING WORD DOCUMENT INTO EXCEL | Excel Discussion (Misc queries) |