Copy and Export to CSV of Pivot Table output results

[newuser17]

Hi experts,

i am an newbie for the VBA and Power Query..

i will have to download reports from our old system and upload to new system everyday.. the downloaded file will have an pivot table output as below after cleaning data. what i need to be do is copy each output based on the kit no and Device to a separate CSV file. which is a duplicative task and the pivot table out put may go up 5000 rows..

each CSV files are names as KIT No & Device eg: AECWC00001 MCY

will any experts may help me to write an VBA/Macro to do this task ? copy each output group to separate CSV file in any folder ?

KIT NO	Device	Sequence	Sum of Try
AECWC00001	MCY	9400003597193	78
SECWC00135	MCY	9400003262732	66
		9400003262763	9
		9400003262855	4
		9400003262862	10
		9400003262879	2
		9400003262893	10
		9400003370581	13
		9400003386384	3
		9400003391791	22
		9400003816652	11
SECWC00136	MCY	9400003262732	3
		9400003262749	2
		9400003262763	25
		9400003262770	12
		9400003262787	3
		9400003262794	14
		9400003262855	13
		9400003262862	1
		9400003262893	12
		9400003370581	6
		9400003391791	9
		9400003816652	3

this will be the output of the CSV file.. for eg output for SECWC000135 MCY

Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10
9400003262732		66
9400003262763		9
9400003262855		4
9400003262862		10
9400003262879		2
9400003262893		10
9400003370581		13
9400003386384		3
9400003391791		22
9400003816652		11

is this doable in power query ? or VBA ? either way i am more than happy to have a solution.

thanks in advance for your precious time and effort.

[werafa]

Hi,

You will find this to be a 'mid-level experience' task, but it is quite doable.

You have two steps.
First you have to read the data, and paste it into a new 'ordinary' excel worksheet

to do this, google 'excel vba pivot tables' to see how to read the pivot table with vba.
then write the data into a new sheet (try googling 'excel vba copy paste new sheet')
Referencing pivot tables is not very intuitive unless you are used to working with tables - so be prepared to do a little research on this.

Step two is to save the new sheet as csv
I think the command you need is 'application.filedialogue', so google 'excel vba filedialogue save as csv'
you will also need to create your save-as filename, (create a string by concattenating the required input cell values, eg myName = "A1" & " " & "A2")
(the '&' symbol allows you to create one string out of two or more)


Break your code down into bite sized chunks so you can get it going step-by-step, and post it back on this forum if you need specific help.

good luck

[newuser17]

hi werafa,

hanks for your reply. i understand what you are saying clearly. i hope i can do the first part with some research, try and errors. but the second step is a way confusing to me. not just save as csv but dealing with the dynamic range that i need to copy.

for eg according to the source table i need to have 3 separate csv files. where i need to find the first cell of the sequence column as well as the last cell of the sum of try column related to that one particular kit no. and do the same for next data set here the copy range is always moving and

i hope which is need to done by looping or similar function. and i couldn't think in method to do it programmatically.

will you be able to give me an code examples to this part ?

i know this is not a "give me the code" site but your help can save my day and i will be so great full..

thanks in advance.

[werafa]

Hi again,

Managing dynamic ranges is easy once you know how. I'd advise you strongly to get familiar with range objects for a start. they are roughly analogous to a named range in 'standard' excel, but way more powerful - and let you see what is happening as you step through code.

to ID and capture the required range, you need to ID the first and last cell in the range.
You can use 'find' but for now, I suggest you use a loop so you can step through it and see how the code is responding.

something like

dim myRow as long
dim lastRow as long
dim myCol as long
dim lastCol as long
dim myRange as range
dim newSheet as worksheet
dim mySheet as range

  ' like to set all the main initialisation stuff in a block up front
  set mysheet = thisworkbook.worksheets("Sheet 1")
  mycol = 1
  set newsheet = thisworkbook.worksheets.create
  lastRow = mySheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' this is a fast and relatively error free way of finding the last row

'find the first row
  for myrow = 1 to lastrow
    if mysheet.cells(myrow,myCol).value = "KIT NO" then exit for
  next myrow
  if myrow > lastrow then 'debug - search string was not found
    msgbox("debug 1")
    exit sub
  end if

  'find last column. you can use the code for lastrow (change the searchorder), or
  do until mysheet.cells(myrow,mycol+1).value = ""
    mycol = mycol+1
  loop

  with mysheet  'the with statement makes the code easier to read - it applies to anything with a '.' in front
    Set myRange = .range(.cells(myrow,mycol),.cells(lastrow,lastcol)
  end with

now you can loop through your source data range ( for lRow = 1 to myrange.rows.count ) and copy selected row data into your new sheet
once this is done, google "Excel vba save as csv" and you should find the code to do this.

hope this helps

Werafa

[werafa]

ps, do this multiple times for multiple output ranges.

you can clean up with newsheet.delete
you can also pass objects to other subs

eg

ChildSub(myRange as range)
end sub

or 

myFunction(myRange as range) as something
end function

this lets you compartmentalise things and make it easier to read and debug

[p45cal]

i will have to download reports from our old system and upload to new system everyday.. the downloaded file will have an pivot table output as below after cleaning data.

Are these pivot tables normal 'old' excel pivot tables or Power Pivot tables?

Is the cleaning data process something you do to the source data of the pivot tables (do you perhaps create the pivot tables yourself? (because if you have the source data there may be faster ways of getting the output you need, without pivot tables, for example with Advanced Filter or just a simple query))

Even assuming you have these pivot tables supplied to you 'as is', vba can manipulate and get data from pivot tables quite well and we should be able to use to that produce these csv files quite easily.
One more question; you have the Sum of Try column in the Values area of the pivot, is the sum in fact always only one record from the source data?

A workbook with your pivot (and your source data too if possible) would be really. really useful and answer a lot of questions. See if you can't supply one here.

[newuser17]

Are these pivot tables normal 'old' excel pivot tables or Power Pivot tables?

Is the cleaning data process something you do to the source data of the pivot tables (do you perhaps create the pivot tables yourself? (because if you have the source data there may be faster ways of getting the output you need, without pivot tables, for example with Advanced Filter or just a simple query))

Even assuming you have these pivot tables supplied to you 'as is', vba can manipulate and get data from pivot tables quite well and we should be able to use to that produce these csv files quite easily.
One more question; you have the Sum of Try column in the Values area of the pivot, is the sum in fact always only one record from the source data?

A workbook with your pivot (and your source data too if possible) would be really. really useful and answer a lot of questions. See if you can't supply one here.

hi,

its just a regular old pivot table. not a power pivot. cleaning data means that i do add some columns and remove blank rows manually and get into to a normal table. the source table has the duplicating kit no, device, sequence and no of try in no order. so basically its a groping based on the kit no, device and sequence to get the total no of try to see how many times tried in the with device.


my source data looks like the below table

Source.jpg

thanks for your attention

[p45cal]

OK, I'd like to experiment with several solutions, so help me out with one pivot table and one source table feeding that pivot in a workbook (rather than a picture of one)… you'll get a much faster answer without me guessing wrongly all sorts of things. The file will also tell me which version of Excel you're using. Do you have Power Query aka a Get & Transform Data section in the Data tab of the ribbon in Excel?

[newuser17]

OK, I'd like to experiment with several solutions, so help me out with one pivot table and one source table feeding that pivot in a workbook (rather than a picture of one)… you'll get a much faster answer without me guessing wrongly all sorts of things. The file will also tell me which version of Excel you're using. Do you have Power Query aka a Get & Transform Data section in the Data tab of the ribbon in Excel?

Thanks for your reply.
I will be able to upload the both file here. In a short while.
The file is used in different computers and different versions of excel. However I will be able to use the power query. Excel 2016. Yes I have power query. I am already using few power query files without any problems.

[newuser17]

hi p45cal,

i have uploaded the sample source data and output pivot table and final output csv files.

the actual source data have more than 40 columns that are not relevant to this pivot table.. so i have removed them and kept only relevant columns.

thanks for your precious time.

AECBA00018 MCY.csvAECWC00001 MCY.csvSample Data.xlsx

[newuser17]

ps, do this multiple times for multiple output ranges.

you can clean up with newsheet.delete
you can also pass objects to other subs

eg

ChildSub(myRange as range)
end sub

or 

myFunction(myRange as range) as something
end function

this lets you compartmentalise things and make it easier to read and debug

hi werafa,

thanks for your time and effort to to help me out. i n the meantime i am trying to understand and figure out the codes.
i am trying to work this codes with my own pace.. hope to get this done. thanks again.

[p45cal]

i have uploaded the sample source data and output pivot table and final output csv files.

Right, I can work with these. I'll be looking at this on and off. In the interim, you've confused me a bit with your AECWC00001 MCY.csv file; there are 2 records, both for 9400003597193 in the Header1 (Sequence ID?) field and 2 values in the Header3 field (Try count?) of 1 and 3. Could you confirm this is a typo or mistake just so I'm sure I'm not missing anything. I expected this csv file to contain only one record, 9400003597193 and 71 under the respective headers??

The other other csv file is empty, which is reasonable since the sample data contains no data for that Kit No./Device combination; do you want to produce such empty files too?

[newuser17]

i am sorry for the wrong file AECWC00001 MCY. actually its a type error.

i have attached the corrected file again herewith. i should have been clear with the empty kit no in the beginning it self. if you look at the source table for the kit no AECWC00001 there are two devices. which are MCY & RDR actualy these two devices are belongs to the same kit no of AECWC00001.

when i a prepared the pivot table i have inserted a blank line after each item to make human readable easy. in the scenario like this the csv file name should be with kit no and the device id of maximum sequence try. eg: AECWC00001 kit no has two devices, and out that two devices MCY has the maximum of no of try so the file name is look like AECWC00001 MCY.

but i felt if i need to include this scenario to get a solution it may complicate things so decided to omit this completely since it is an non frequent so may be able to do the adjustment manually before generate the csv file. i kindly request you to omit this scenario and continue.

thanks again.

AECWC00001 MCY.csv

[p45cal]

AAAAgggghhh!
So the file is named after the Max no. of tries per sequence which in this case for AECWC00001 is MCY, but instead of 71 for MCY you want the total of all Devices, 78 in this case?!

Kit no. AECWC00001 is the only one to have a mix of DEVICEs, but it's also the only one to have only one Sequence ID.

Surely, you have Kit no.s which have a mix of DEVICEs AND multiple Sequence IDs?!

So that I can understand exactly what you want, let's have another data.xlsx file where you include in your sample data at least one (preferably more) such Kit No and the csv file(s) that would result from those one or more Kit No.s.

Sorry about this.

[newuser17]

hi p45cal,

extremely sorry for the confusing. i have attached a new sample data file.

long story short here the whole scenario is

same kit no might have multiple sequence ids
same devices might have multiple sequence id
multiple devices might have same sequence id which is very very rare.
csv file name named after kit no and device
same kit no might have multiple devices which is very rare
if more than one device found in same kit no the file named after kit no and device id of max no of try.

i hope i am clear enough this time. please find the csv file and new sample data

Sample Data new .xlsx AECMC00002 MCY.csv

thanks again

[p45cal]

The attached has a button. Pay attention to the button's caption.
This solution is clunky; I'm experimenting with various approaches and was aware that you needed some sort of response sooner rather then later.
It's slow and I'm sure I can speed it up (later) to just a few seconds.

What this offering does:
Creates a pivot table in the same workbook as the button. This pivot table is used to create file names (the Kit No and the most popular Device according to no. of tries).
It runs down the pivot table picking up Kit Nos and for each, uses MS Query to create a temporary sheet, then a workbook, which is saved as a .csv file in a subfolder called CSVFiles under the foder containing the source xlsx file, and closed. If there is already a file with the same name, it asks if you want to overwrite it. You can prevent this question popping up by deleteing all .csv files in that subfolder before running the macro.
Note that the resultant csv files may contain records for Sequence IDs for more than the Device mentioned in the csv file's name; there is no Device name data within the csv files.
The Pivot table sheet is then deleted.

There are comments within the code too.

[p45cal]

Please pay attention to the rules regarding cross posting (you've posted the same question at MrExcel), all sites like these have the same/similar rules about this.
You're supposed to supply links to everywhere you've cross posted the same question to.
Have a read of http://www.excelguru.ca/content.php?184 , it explains the whys and wherefores.
Ultimately it's to your own benefit (you won't find your questions ignored).