?
Solved

Export to csv file

Posted on 2013-01-02
18
Medium Priority
?
296 Views
Last Modified: 2013-09-30
I have created an xml file that I want to export to excel.  The file has 50+ columns accross the top.  It is currently only allowing 18 columns.  Does anyone know how to allow more columns?  I have attached my file
0
Comment
Question by:tracim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
18 Comments
 
LVL 13

Expert Comment

by:Jeff Darling
ID: 38738258
does your xml have more than 1 table?

I'm not seeing the attachment.
0
 

Author Comment

by:tracim
ID: 38738312
I have attached it below - not sure what you mean more than one table.  I'm a newbie with xml..........
CSVLCSPerformanceReport.xsl
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 38739660
Hi,

well aparently you are taking a good approach... nested XML to csv is best helped by using XSLT.

The XSLT however is very hardcoded, leaving you you little option of extending the data in the XML.

Can you please attach the source XML and indicate the extra columns you need?
I am happy to rewrite this XSLT for you
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:tracim
ID: 38740359
Hi,

I have attached two files below.  The first one is a screenshot of the report that shows all of the columns that are needed.  The 2nd file is my source file called CSVLCSPerformanceReport.xsl.  I appreciate your help.
DSPI-Carillon.docx
CSVLCSPerformanceReport.xsl
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 38740386
the 2nd file is the XSLT that does the transformation to CSV.
What I need is the source XML that is the input to the XSLT process
0
 

Author Comment

by:tracim
ID: 38740530
Hi there,

The attached below is the file that produces the report.  The only other files that are used for the report are the stored procedures. Let me know if you need the stored procedures too.  Thanks!
LCSPerformanceReport.xsl
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 38740727
right,
let me explain something.
XSLT (you have sent a bunge of XSLT files already)
is a programming language that transforms an XML source into something else
this means that as a starting point you can only have XML.
I need to see that XML in order to change your XSLT process (or at least to test it)

It could be that the XML is dynamically generated from a database
and that the query result is an XML object that is directly fead into an XSLT process

Can you give some more insight on that?

Does the LCSPerformanceReport.xsl give you the report as I can see it in the .docx file?
It seems to be the case.
That could help me to generate the CSV for you

some info on database type, platform, ... would definitely build the understanding
0
 

Author Comment

by:tracim
ID: 38740770
Thanks for your patience.  I am new at this.  Yes, the xml is generated from the two stored procedures that I have attached.  Yes, the LCSPerformance Reports gives the report in the .docx file.  The csv file should match that report.

The database is SQL Server.  Please let me know if you have any questions.
rptLCSPerformanceGoal.sql
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 38775543
Which version of Microsoft Office you are using ?

Is it 2010 or 2007 ?

With Excel 2010, you can open XML File, as  XML Table.
It can auto create XSD.
0
 
LVL 29

Assisted Solution

by:Pravin Asar
Pravin Asar earned 800 total points
ID: 38775642
Also consider adding the option for XML output.

E.g.


FOR XML PATH('RECORD'), ROOT('RECORDSET');

This add appropriate wrapper tags to each record and entire record set
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1200 total points
ID: 38781305
Agree with pravinasar...

Your Stored Procedures are using XML AUTO and that is not going to work for you.

You need a more traditional XML construct and the easiest way to achieve that is to use at least the PATH() and ROOT() qualifiers instead of AUTO.

I see you are using the row_number() function, so, no problems using PATH() and ROOT().

For example (and doing a small example manually)

if object_id('tempdb..#my_xml_test','U') is not null drop table #my_xml_test

create table #my_xml_test (id int, code varchar(10), description varchar(100))

insert #my_xml_test values (1,'AR-5381','Adjustable Race')
insert #my_xml_test values (2,'BA-8327','Bearing Ball')
insert #my_xml_test values (3,'BE-2349','BB Ball Bearing')
insert #my_xml_test values (4,'E-2908','Headset Ball Bearings')

select * from #my_xml_test for xml auto

select * from #my_xml_test for xml path('rows'), root('data')

Open in new window


That will produce two results. Click on the first result content (ie the AUTO output) and it will open a new window where you will see something like :

<_x0023_my_x005F_xml_test id="1" code="AR-5381" description="Adjustable Race" />
<_x0023_my_x005F_xml_test id="2" code="BA-8327" description="Bearing Ball" />
<_x0023_my_x005F_xml_test id="3" code="BE-2349" description="BB Ball Bearing" />
<_x0023_my_x005F_xml_test id="4" code="E-2908" description="Headset Ball Bearings" />

Open in new window


Now click on the second one (the PATH() and ROOT()) and you will see something like :

<data>
  <rows>
    <id>1</id>
    <code>AR-5381</code>
    <description>Adjustable Race</description>
  </rows>
  <rows>
    <id>2</id>
    <code>BA-8327</code>
    <description>Bearing Ball</description>
  </rows>
  <rows>
    <id>3</id>
    <code>BE-2349</code>
    <description>BB Ball Bearing</description>
  </rows>
  <rows>
    <id>4</id>
    <code>E-2908</code>
    <description>Headset Ball Bearings</description>
  </rows>
</data>

Open in new window


Which does look a lot more like a traditional XML file.

You can then copy and paste into Notepad and save those to disk with the XML file type.

Double clicking on them will invoke the standard XML viewer (internet explorer normally) and the first one doesnt open, while the second one does open.

You can also try opening in Excel. The first one wont (error parsing), and the second one will (might prompt for a couple of things - just press enter). You dont actually need a schema. Note, for Excel to open, never use a ROOT of 'table' excel will try to do something different with that.

Now, your question asked for an export to CSV, so, will the above information solve your problem, or do you still want to export to CSV. Also, how are you running the procedures and can you produce the disk file ?
0
 
LVL 50
ID: 39498521
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39498522
The error is the stored procedure using XML AUTO

If that is changed to PATH() and ROOT() then a correctly formatted XML file is created.

That file can then be read directly by Excel.

The extra step of needing CSV was never confirmed and almost irrelevant considering the XML should now be properly formatted and readable.

Please see : http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/XML/Q_27982980.html#a38781305
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question