Solved

Export to csv file

Posted on 2013-01-02
18
286 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
  • 5
  • 3
  • 2
  • +3
18 Comments
 
LVL 12

Expert Comment

by:Jeff Darling
Comment Utility
does your xml have more than 1 table?

I'm not seeing the attachment.
0
 

Author Comment

by:tracim
Comment Utility
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
Comment Utility
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
 

Author Comment

by:tracim
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tracim
Comment Utility
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
Comment Utility
0
 
LVL 28

Expert Comment

by:Pravin Asar
Comment Utility
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 28

Assisted Solution

by:Pravin Asar
Pravin Asar earned 200 total points
Comment Utility
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
Comment Utility
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:teylyn
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now