[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

CSV file Insert Header

I have a csv file which does not have a header, but some time I would like a header for the file. I create this csv file on the fly use xslt file as my stly sheet is there a way I can add a header using the stly sheet or will I have to use vb.net code. example of csv file below. The xslt currently work fine but it does not add header to the file. I have attached the xslt for your review.

12408      333333333      0      666      666      1      FALSE                  1015            36      25      0
12508      336666666      0      88      885      1      FALSE      3.5                                    0
12508      222222222      0      88      523      1      FALSE      58                                    0

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>
 
  <xsl:template match="Employees">
    <xsl:for-each select="Time">
      <xsl:apply-templates select="*"/>
      <xsl:text><!--new line at the end of the information-->   </xsl:text>
    </xsl:for-each>
  </xsl:template>
 
  <xsl:template match="*"><!--put out elements content in qutes-->
    <xsl:if test="position()>1">,</xsl:if>
    <!--more than one being processed-->
    <xsl:text/>"<xsl:apply-templates/>"<xsl:text/>
  </xsl:template>
</xsl:stylesheet

Open in new window

0
daruffin
Asked:
daruffin
  • 7
  • 6
1 Solution
 
Geert BormansInformation ArchitectCommented:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
   
    <xsl:template match="Employees">
        <xsl:for-each select="Time/*">
            <xsl:value-of select="name()"/>
            <xsl:if test="not(last())">,</xsl:if>
        </xsl:for-each>
        <xsl:text>&#13;</xsl:text>
        <xsl:apply-templates select="Time"/>
    </xsl:template>
   
    <xsl:template match="Time">
            <xsl:apply-templates select="*"/>
            <xsl:text><!--new line at the end of the information-->&#13;</xsl:text>
    </xsl:template>
   
    <xsl:template match="*"><!--put out elements content in qutes-->
        <xsl:if test="position()>1">,</xsl:if>
        <!--more than one being processed-->
        <xsl:text>"</xsl:text><xsl:apply-templates/>"<xsl:text>"</xsl:text>
    </xsl:template>
</xsl:stylesheet
0
 
Geert BormansInformation ArchitectCommented:
note that I got rid of the for-each Time,
it is better to use apply-templates there
(let XSLT do the work instead of yourself :-)

this construct is wrong
<xsl:text/>"
the " should be inside the xsl:text element as in my code
0
 
daruffinAuthor Commented:
This code does not move the data or give a header for the file. It changes the sheet name but it did not work
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Geert BormansInformation ArchitectCommented:
> It changes the sheet name

are you looking at this in Excel?
I allready found it weird that you had ',' in the code and none in your so called output

can you post a bit of the XML please, so I can test this
It worked with the XML I created myself, but it could be different from yours
0
 
daruffinAuthor Commented:
Here is the xml code below, my old code will convert it to a csv, but it does not add the header to the file

<?xml version="1.0" ?>
- <Employees>
- <Time>
  <TransferDate>012508</TransferDate>
  <EmployeeNumber>333333333</EmployeeNumber>
  <SalesOrderNum>0000000</SalesOrderNum>
  <WorkeTicketSeq>888</WorkeTicketSeq>
  <WorkeTicketStep>888</WorkeTicketStep>
  <SequenceNum>1</SequenceNum>
  <OverTime>false</OverTime>
  <HoursWorked />
  <RecordType />
  <StartTime>0915</StartTime>
  <EndTime />
  <ActivityCode />
  <Department />
  <Flag>0</Flag>
  </Time>
  </Employees>
0
 
daruffinAuthor Commented:
by the way I'm looking at the file in excel, but my customer will use excel to changes some of the data
0
 
Geert BormansInformation ArchitectCommented:
likely the quotes are important for you,
so I also added them to the title row

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
   
    <xsl:template match="Employees">
        <xsl:for-each select="Time/*">
            <xsl:text>"</xsl:text>
            <xsl:value-of select="name()"/>
            <xsl:text>"</xsl:text>
            <xsl:if test="not(position() = last())">
                <xsl:text>,</xsl:text>
            </xsl:if>
        </xsl:for-each>
        <xsl:text>&#xA;</xsl:text>
        <xsl:apply-templates select="Time"/>
    </xsl:template>
   
    <xsl:template match="Time">
            <xsl:apply-templates select="*"/>
            <xsl:text><!--new line at the end of the information-->&#xA;</xsl:text>
    </xsl:template>
   
    <xsl:template match="*"><!--put out elements content in qutes-->
        <xsl:if test="position()>1">,</xsl:if>
        <!--more than one being processed-->
        <xsl:text>"</xsl:text>
        <xsl:apply-templates/>
        <xsl:text>"</xsl:text>
    </xsl:template>
</xsl:stylesheet>

I also corrected some errors

Note that in CSV there is no real indication for a header row,
if there is one, it is the firt row,
nothing visibly distincts it from the other rows
so you need to specify that in your Excel import

There might be a possibility to clearly indicate that a certain row is the header row
if you used HTML or XML as the import for Excel
so if you can't tune Excel to pick the first row as the heade row,
we might have a look at other import solutions

cheers

Geert
0
 
daruffinAuthor Commented:
I will test it when I come back from lunch and let you know thanks so much for your help. I will let you
know after lunch if it worked
0
 
daruffinAuthor Commented:
Hello  Geert

This works fine. Just need for the user to know what  the fields our with-out typing them in each time.It add the header twice on the same line but I guess they can just remove the other section of header which has no data.
If you have any more suggestion out to have it stop after the first section of header it would be apperecited, if not then I can live with this out come
0
 
daruffinAuthor Commented:
Thanks alot Geert for your help.
0
 
Geert BormansInformation ArchitectCommented:
I see, I forgot to add a restriction on the header
(it works correctly if I only have one Time element)
You need to add a [1] in the for-each, to only access the first Time element, not all

    <xsl:template match="Employees">
        <xsl:for-each select="Time[1]/*">
            <xsl:text>"</xsl:text>
...
0
 
daruffinAuthor Commented:
Thanks so much Geert everythings works fine. I will stay in touch if I need anything else
0
 
Geert BormansInformation ArchitectCommented:
welcome
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now