Solved

Export to csv file

Posted on 2013-01-02
18
292 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 12

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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
 

Author Comment

by:tracim
ID: 38740777
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 200 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 300 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

Expert Comment

by:Ingeborg Hawighorst
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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