Link to home
Start Free TrialLog in
Avatar of Kanwaljit Singh Dhunna
Kanwaljit Singh DhunnaFlag for India

asked on

Export Data to Different .csv Files

Hi Experts,

Kindly find the Sample Data for 8 files and 8 .csv sample Files attached.

In the attached Excel File
---Basic Data is in Columns E:AI
---Data is always Sorted as per Column AJ
---Basic data of each line is to be used for Exporting to two different .csv files, each having different number of columns
---Data Joined in the style and for the purpose of export to .csv Files is in the Columns AL, AM, AN and AO (Data will be either joined in Column AL and AN or Column AM and AO depending upon the 1st alphabet in Column AC)
---Header to be used as First Line for .csv files, are Fixed as per row 3 for each of the 4 Columns AL, AM, AN and AO as such
---File Name of each different range is combination of Value in Column A and Data Range being exported {For data in Column AL and AM, Value of Column A will be appended with -B whereas for data in Column AN and AO, Value of Column A will be appended with -I }
---No of Rows of data to be exported to different .csv files is determined on the basis of Value in Column A. (There are 4 ranges in the sample file exported to 8 different .csv files)

Regards
Kanwal
Files-Creation.zip
Files-Creation-002.xlsm
Avatar of JesterToo
JesterToo
Flag of United States of America image

Ok, I've found the sample files and workbook.

I assume you have a question regarding these files... could you please specify what you want?  Don't make us guess.  The description you've posted is very unclear, at least to me.  I wouldn't want to waste a bunch of time and effort on something that wasn't what you wanted.  The workbook has 3 sheets and you haven't even mentioned how/if they relate.
Avatar of Kanwaljit Singh Dhunna

ASKER

Thanks for the Honest Assessment and comments !

There are three Worksheets in the file. Sheet Named "FileNames" and "Validations" is irrelevant for this question.
Relevant Sheet is 192550-AM. Infact I have given only one Sheet here but there will be more than one such sheets in actual Scenario.
So I have uploaded a new File which contains Two such sheets.

Ultimate Objective
I need to upload two quarterly statements to a site in xml format. I have to use a utility which generates the xml file based on a pre-defined schema. The data can be entered in Utility either manually or by importing from TWO separate csv files prepared in a pre-defined sequence and format. Two .csv files are required by utility to create one XML file and so I need Two + Two .csv files to prepare 2 xml files per quarter.

What I have
I have the basic data with me in a SINGLE Pipe Separated file which can not be directly imported into Two .csv files. Apart from that the columns in the pipe separated file are not in the same sequence in which they should be as per the .csv format for importing in the Utility. So basically I have two such Pipe Separated files to prepare Two + Two .csv files per quarter

What I presently do is
--import the Two Pipe Separated files in Excel one by one (Columns E:AI)
--Join all the columns in 2+2 = 4 new columns in the required sequence and format by using comma as separator (Columns AL:AO)
--enter the specified Header in row 3 in required format by using comma as separator (AL3:AO3)
--Copy the Header and the Rows from the Excel and paste the same save in a TWO separate .csv file (Sample Files)
--Upload the .csv files in Utility and create a single xml file for upload.
---------Column A is the Name of the Report. So using Data filter, I 1st copy the Header and then the Data in a new .csv files.
---------I have to copy the data separately for each of the reports as data is in four separate columns
---------Even otherwise I cannot copy both the Header and data directly from filtered rows to .csv file because even the filtered rows are copied in     such a way


Excel File Contents
Presently I am to file reports for more than 1 Quarter so there are two quarters in sample file. But there may be more quarters in some cases. Also I will be using this file to add data in future.

Using the Header from Row 3 and respective columns
--AL4:AL7 is data for 3rd Quarter (1st XML file) - 1st .csv file
--AN4:AN7 is data for 3rd Quarter (1st XML file) - 2nd.csv file

--AM8:AM11 is data for 3rd Quarter (2nd XML file) - 1st .csv file
--AO8:AO11 is data for 3rd Quarter (2nd XML file) - 2nd.csv file

--AL12:AL15 is data for 4th Quarter (1st XML file) - 1st .csv file
--AN12:AN15 is data for 4th Quarter (1st XML file) - 2nd.csv file

--AM16:AM19 is data for 4th Quarter (2nd XML file) - 1st .csv file
--AO16:AO19 is data for 4th Quarter (2nd XML file) - 2nd.csv file

Quarter No is given in Column C

What I Wish For
A Macro which does the following.
--Data from Used Defined Range in Column AL4:AO1000 is exported to separate .csv files as per the above requirement using the row 3 of the respective columns as Headers. (Named range V07DataRange in the attached file)
--The file name of the .csv files is as per Column A of the respective range (where -B is apended for files created by using data in Column AL and AN and -Inc is appended for files created by using data in column AM and AO
--It is done for all such worksheets in the Workbook except the Two irrelevant sheets.

Kindly inform if any other information is required.
Files-Creation-002.xlsm
Thank you for the explanation.  I think I have a better understanding now of what you want.  I think that an external VBscript app would be better suited for this task.  It would be easier to code and more useful in the long run than an embedded VBA solution.

It's an interesting problem/challenge and one that is similar to several that I've worked on over the past 48 years.

I will analyze the data and info you've provided in order to determine if this is something I think I can provide with not too great an effort. In the meanwhile, someone else may offer a solution.
Thanks a Lot Sir !

Waiting Patiently !
Using Excel 2013 I can find no named ranges in any of the worksheets
Never mind. I found them.  Didn't notice that the workbook was in protected mode.
I have several questions regarding this project...

1.  The spreadsheet states in AL1 that column A will contain file name
    to use for CSV file.  That column contains values that would be
    illegal as a file name.

2.  Your first project description states that header values to be
    used in the CSV files are in AL3, AM3, AN3, & AO3... which makes
    sense because those are the columns the data will also come from.
    However, those header cells all contain "#NAME?" which makes no
    sense to me.

3.  The actual data contained in columns AL thru AO to be extacted
    already contain commas.  When the real header values are present
    will they also contain the appropriate commas to match up with
    the data?  If not, how should this be handled in the script I'm
    attempting to develop?

4.  Will the workbook ever contain data for quarters 1 & 2?  If so,
    where are the specifications for processing them?

5.  All of the sample data you've provided indicates that the CSV
    files will each contain 4 data rows plus a header. will that
    always be the case?  I ask this because in your second project
    description you imply that there could be as many as 1000 rows
    to process by virtue of the reference to
    "Used Defined Range AL4:AO1000".

6.  Lastly, be advised that my scripting solution cannot use
    "Named Ranges".  That feature of the Excel Object Model seems
    to be unavailable for use by VBscript... at least, I can find
    no references that work with VBscript.  That implies that the
    workbook will need to be "static" in its structure or the
    VBscript would need to be modified whenever the workbook
    structure is altered.
Thanks for the Comments Sir !

1--What I tried to say that for files created using data exported from Column AL, File name should be 192550-AM-201516-11G-3-B.csv for 3rd quarter and 192550-AM-201516-11G-4-B for the 4th quarter (i.e., value in corresponding row in Column A followed with -B.)

Similarily for column AN data, the filename should be ending with -Inc using column A as file name. (192550-AM-201516-11G-3-Inc.csv for 3rd quarter and 192550-AM-201516-11G-4-Inc.csv for the 4th quarter)

I have given required file names in Column AP and AQ

2--That is because I have used the UDF MCONCAT built in Laurent Loungre's Morefunc.xll. Tried to upload that file too but EE does not allow to upload such files. It is however available at http://ccm.net/download/download-24205-morefunc-macro-add-in-for-excel.

3--I have uploaded a new file which contains only values instead of the formula and that has been amended to contain the appropriate commas to match up with the data (so you need not download the Add-in)

4--The files will surely contain the data for all the quarters, even though I had not given the sample for 1st and 2nd quarters. The newly uploaded file contain the sample data for all the quarters.

5--In real scenario a single quarter may have data consisting upto 5000 rows. Data will certainly vary for each quarter and for each year. That is the exact reason I mentioned in my original question that Data is always Sorted as per Column AJ as that certainly will group the data on quarterly basis which might come handy in creating a VBA solution.

6--I certainly don't know much about this part so not able to comment. But I feel there is a possible by-pass to this issue which I will discuss in my next comment (Along with the new File)
Avatar of Bill Prew
Bill Prew

With regard to #6, it is possible to access named ranges in VBA.  Here's a trivial example showing how I displayed the cells in named range TestRange.  Hope this helps...

Sub AccessRange()
    For Each c In Range("TestRange")
        Debug.Print c.Value
    Next c
End Sub

Open in new window

~bp
Thanks Bill

I knew how to do that in VBA code, but this is standalone VBscript and I haven't found the object model that allows similar code to do that.

Lynn
Thanks a Lot to both the Experts !

I must ask for 15 minutes time to post my amended workbook, which I expect will simplify the extraction process. Thanks a Lot !
Ah, okay, sorry, had the topic area in my vision, hadn't reread the thread again.  Hmmm, seems like we should be able to get at that, since we are automating Excel, and the sheet is open, etc.  I'll poke around a bit and see if I can uncover anything...

~bp
Thanks everyone... there's no rush... I'm here all day :)
This works fine for me in VBS...

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False

Set objMaster = objExcel.Workbooks.Open("c:\temp\r.xlsm", False, False)
Set objSheet = objMaster.Sheets("Sheet1")

For Each c In objSheet.Range("TestRange")
    Wscript.Echo c.Value
Next

objMaster.Close False
objExcel.Quit

Open in new window

~bp
Thanks, Bill

 I think at the time I was attempting to retrieve the list of all named ranges and didn't see how to do that.

Once I get the new XLSM file, I'll give this a go and let everyone know how it works.  I think some of the "specifications" are changing just enough to need to revise my code anyway.
I have reassessed my present situation and future requirement.

Though I may be filing statements for multiple quarters presently but in the future it has to be one quarter at a time. So I need to make sure that if in the future I wish to keep all the data in the same sheet then there must be some way to stop creating .csv files for all the quarters. That certainly is tricky with the present data set-up.

So I have tried some changes.
1 - I have combined data for all the Companies in a Single sheet
2 - Used column AO and AP to create the required file names
3 - Added another sheet "ExportToCSV" for Filtering the data required for the Desired Year, Quarter and Form No. This Sheet contains only the columns which contains the data for export. As the column AN:AO are same in all respects so I have combined them in the same Column AN.
So there are 3 required columns which are now shifted to Column A, B and C of this sheet.
4 - Added a small macro to automate the advanced filter for required data.
5 - Desired file names are in Column F and G

Now we can use the ExportToCSV sheet for doing the export process. This way I can choose the Form No, Year and Quarter for creating the desired .csv files.

Hope the above is not much deviating from the original specifications.
Files-Creation-003.xlsm
Apologies !
The uploaded file contained an incorrectly defined range. I have corrected that in this file.
Files-Creation-003.xlsm
"Hope the above is not much deviating from the original specifications."

If I had a dollar for every time I've heard that in my career I could retire a second time!

Seriously, this is MAJORLY different from where we started.  Also, the last spreadsheet you sent does not match the "specifications" you included.

I've already expended much more time and energy on this than I should have.  I think I'm just going to bow out of this project.  Perhaps someone else can comprehend it well enough to help you.

Good luck.
I Can feel and understand every bit of what you wrote !

Myself to Blame !!!

Thanks for Every Single moment of yours Life you spent for me !!

May the Almighty Bless You with Health and Happiness !
One last question. I Could not pin point where I did not matched the specifications ?
Kanwal,

I was incorrect when I said the spreadsheet didn't match the specs... but I do think the example CSV files provided in your first post don't seem to match the specifications.

I have re-reviewed your last set of specs and the corresponding spreadsheet and I've created a script that may come close to providing the output you want... please try running it against your own (more complete) sample spreadsheets to determine its accuracy.  I know it does not take into account the path to where the output should be written (it would if the filenames in the ExportToCSV sheet included them).  I may be able to correct that with a bit more study, but I wanted to give you what I have to see if I'm even on the right track.

Please save the attached file (I've named it "ExportExcel.vbs", but you can rename it to whatever you like as long as it remains a .vbs extension).  There is a comment block at the top of the script that details how to run it.  Let me know how it works for you.

Lynn
ExportExcel.vbs
Thanks Lynn,

I couldn't have asked more !!!

It is working in the way it was supposed to. The only difference I noted was that for each set of data in (column B and D) or (Column C and D) it was supposed to create two different files for Column B, Column D (or Column C, Column ,as the case may be).

But it is creating only one file for both of them by combining the data from two columns

What was desired that for Range--->
B4:B7 there is one csv file named XXXXX-201516-11G-3-Basic.csv               (Header in Cell B3)
D4:D7 there is one csv file named XXXXX-201516-11G-3-Income.csv               (Header in Cell D3)
C8:C11 there is one csv file named XXXXX-201516-11H-3-Basic.csv               (Header in Cell C3)
D8:D11 there is one csv file named XXXXX-201516-11H-3-Income.csv               (Header in Cell D3)
B12:B15 there is one csv file named YYYYY-201516-11G-3-Basic.csv               (Header in Cell B3)
D12:D15 there is one csv file named YYYYY-201516-11G-3-Income.csv               (Header in Cell D3)
C16:C19 there is one csv file named YYYYY-201516-11H-3-Basic.csv               (Header in Cell B3)
D16:D19 there is one csv file named YYYYY-201516-11H-3-Income.csv               (Header in Cell D3)
Sorry for that Kanwal, I wasn't totally clear on that part of the spec.  I'll make an attempt to change the script and re-attach it.  I may have another question or two before I'm done with it, however.
Here is a new version of the script... it now produces twice as many files as before.  Let me know if it needs any more tweaking.

Lynn
ExportExcel.vbs
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a Lot Lynn !!!

That Indeed is Picture Perfect !!!

I have never dealt with VB Script before this question and don't understand how much it is different from VBA. I am going through the script and definitely will need help to understand the code. But right now I am enjoying what it is doing.

MAY the Almighly Bless You !!!
Hi Lynn,

I have posted a related question at the following link. Do have a look whenever you could spare a few precious moments.

https://www.experts-exchange.com/questions/29019508/VB-Script-to-specify-file-generation-Location.html

Thanks Again !!
Regards
Kanwal