Menu ▾ ▴

#9 -t option on csv2odf

v1.0 (example)
closed-fixed
Larry Jordan
None
5
2017-02-09
2017-01-26
Rob Kinman
No

I have a .csv file generated by an internal report generator, which lists the report parameters in the first n rows, then the header row, then the data rows. I would like to extract the parameters into the first worksheet, and the data into a second worksheet.

I have successfully extracted the data into separate workbooks, but cannot extract it into 2 worksheets in the same workbook. I had assumed that I could use the -t option to do this, but have not had any luck so far.

Can you help?

Related

Bugs: #9

Discussion

  • Larry Jordan

    Larry Jordan - 2017-01-26

    Rob,

    What you need to do is two seperate runs of csv2odf. The first run will process the parameters into the first worksheet. For example, if the parameters are in the first 10 rows:

    csv2odf -e 10 input.csv template.ods output1.ods

    The second run will insert the data into the second worksheet:

    csv2odf -s 11 -t 2 input.csv output1.ods output2.ods

    Notice this time we used output1.ods as the template.

    Try that and see if it works.

    Thanks.
    Larry

     

    • Rob Kinman

      Rob Kinman - 2017-01-27

      Hi Larry

      Many thanks for your prompt reply.

      I have tried your suggestion, but have had no success.

      FYI I am running this under Windows 8.1, using python 2.7 and Excel 2016.

      Step 1 (Putting Report Data into Worksheet 1)

      python csv2odf RobSampleData.csv RobExampleTemplate.xlsx
      RobExampleReport.xlsx -s 12 -t 1 -S 2 --comment="Manufacturing Analysis"

      This produces a correctly formatted report using data from line 12 onwards
      in the first worksheet

      Note : I do get the following message when opening the output Report in
      Excel, which I would like to eliminate

      Step 2 (Putting Parameter Data into Worksheet 2)

      python csv2odf RobExampleData.csv RobParameterTemplate.xlsx
      RobExampleReport.xlsx -s 3 -e 9 -t 2 -S 1 --comment="Manufacturing Analysis"

      This overwrites the data in the first worksheet, with the heading from the
      Parameter Template and nothing else

      Individually, both processes work correctly if I specify -t 1, but I have no
      success trying to populate 2 worksheets in the same workbook by using -t 2.

      I have attached my sample data, and the two templates, if this will help.

      Kind regards

      Rob

      From: Larry Jordan [mailto:w322@users.sf.net]
      Sent: 27 January 2017 12:54 AM
      To: [csv2odf:bugs] 9@bugs.csv2odf.p.re.sf.net
      Subject: [csv2odf:bugs] #9 -t option on csv2odf

      Rob,

      What you need to do is two seperate runs of csv2odf. The first run will
      process the parameters into the first worksheet. For example, if the
      parameters are in the first 10 rows:

      csv2odf -e 10 input.csv template.ods output1.ods

      The second run will insert the data into the second worksheet:

      csv2odf -s 11 -t 2 input.csv output1.ods output2.ods

      Notice this time we used output1.ods as the template.

      Try that and see if it works.

      Thanks.
      Larry

      [bugs:#9] https://sourceforge.net/p/csv2odf/bugs/9/ -t option on csv2odf

      Status: open
      Group: v1.0 (example)
      Created: Thu Jan 26, 2017 02:09 PM UTC by Rob Kinman
      Last Updated: Thu Jan 26, 2017 02:09 PM UTC
      Owner: Larry Jordan

      I have a .csv file generated by an internal report generator, which lists
      the report parameters in the first n rows, then the header row, then the
      data rows. I would like to extract the parameters into the first worksheet,
      and the data into a second worksheet.

      I have successfully extracted the data into separate workbooks, but cannot
      extract it into 2 worksheets in the same workbook. I had assumed that I
      could use the -t option to do this, but have not had any luck so far.

      Can you help?

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/csv2odf/bugs/9/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/subscriptions/

       
      RobExampleTemplate.xlsx
      RobParameterTemplate.xlsx
      RobSampleData.csv
      alternate
      Thumbnail
      image003.png

  • Larry Jordan

    Larry Jordan - 2017-01-28

    Rob,

    For step 1, use a template that has two tabs. For this step, data is only inserted onto the first tab. The output will be used in step 2 and is only temporary.

    For step 2, use the output of step 1 as the template and insert data to the second tab.

    Let us know if that works.

    Larry

     

    • Rob Kinman

      Rob Kinman - 2017-01-29

      Thanks Larry

      As suggested, I added a second tab to the first template (see
      RobExampleTemplate.xlsx)

      • Sheet1 has the format template
      • Sheet2 is blank

      I then ran

      python csv2odf RobSampleData.csv RobExampleTemplate.xlsx
      RobExampleReport.xlsx -s 12 -t 1 -S 2 --comment="Manufacturing Analysis"

      csv2odf creates an output .xlsx spreadsheet with the correct data in Sheet1
      and a blank Sheet2 (see RobExampleReport(Interim).xlsx)

      I then ran the following (using RobParameterTemplate.xlsx)

      python csv2odf RobSampleData.csv RobParameterTemplate.xlsx
      RobExampleReport.xlsx -s 3 -e 9 -t 2 -S 1 --comment="Manufacturing Analysis"

      csv2odf seems to just overwrite Sheet1 with the template data and remove
      Sheet2 (see RobExampleReport.xlsx)

      Note that I also tried adding a Sheet2 to the second template and running
      the same command line. csv2odf put the template on Sheet1 and the data onto
      Sheet2, but still wiped out the original data.

      I have spent a couple of hours trying various combinations of the above, but
      have not found one that works.

      • Do I have a problem understanding the basics?
      • Do you think that it is going to be possible to fulfil my needs with
        csv2odf?

      Many thanks for your help to date

      Regards

      Rob Kinman

      From: Larry Jordan [mailto:w322@users.sf.net]
      Sent: 28 January 2017 02:51 AM
      To: [csv2odf:bugs] 9@bugs.csv2odf.p.re.sf.net
      Subject: [csv2odf:bugs] #9 -t option on csv2odf

      Rob,

      For step 1, use a template that has two tabs. For this step, data is only
      inserted onto the first tab. The output will be used in step 2 and is only
      temporary.

      For step 2, use the output of step 1 as the template and insert data to the
      second tab.

      Let us know if that works.

      Larry

      [bugs:#9] https://sourceforge.net/p/csv2odf/bugs/9/ -t option on csv2odf

      Status: open
      Group: v1.0 (example)
      Created: Thu Jan 26, 2017 02:09 PM UTC by Rob Kinman
      Last Updated: Thu Jan 26, 2017 10:54 PM UTC
      Owner: Larry Jordan

      I have a .csv file generated by an internal report generator, which lists
      the report parameters in the first n rows, then the header row, then the
      data rows. I would like to extract the parameters into the first worksheet,
      and the data into a second worksheet.

      I have successfully extracted the data into separate workbooks, but cannot
      extract it into 2 worksheets in the same workbook. I had assumed that I
      could use the -t option to do this, but have not had any luck so far.

      Can you help?

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/csv2odf/bugs/9/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/subscriptions/

       

      Related

      Bugs: #9

      RobExampleReport(Interim).xlsx
      RobExampleReport.xlsx
      RobExampleTemplate.xlsx
      RobParameterTemplate.xlsx
      RobSampleData.csv
      alternate

  • Larry Jordan

    Larry Jordan - 2017-01-29

    I dont't think I can explain it better, so here are the commands I used and the files are attached...

    python csv2odf RobSampleData.csv LarryExampleTemplate.xlsx LarryIntermediate.xlsx -s 12 -t 1 -S 2 --comment="Manufacturing Analysis"

python csv2odf RobSampleData.csv LarryIntermediate.xlsx LarryExampleReport.xlsx -s 3 -e 9 -t 2 -S 1 --comment="Manufacturing Analysis"
     
    LarryExampleReport.xlsx
    LarryExampleTemplate.xlsx
    LarryIntermediate.xlsx
    RobSampleData.csv

    • Rob Kinman

      Rob Kinman - 2017-01-30

      Hi Larry

      Lightbulb moment :) (I hadn't understood the significance of the
      intermediate spreadsheet.) It works as we need it now.

      Many thanks for your patience.

      I am still getting the following message, when I open the final spreadsheet
      in Excel 2016

      'We found a problem with some content in 'LarryExampleReport.xlsx'. Do you
      want us to try to recover as much as we can? If you trust the source of this
      workbook, click Yes'

      I click Yes, and the dataset is OK. Do you have any idea what causes the
      message? Could it be the content, the version of Excel etc.?

      As the final report will be generated by, and automatically sent to, fairly
      unsophisticated users, it would be useful if we could eliminate this
      message.

      Once again thanks

      Rob

      From: Larry Jordan [mailto:w322@users.sf.net]
      Sent: 29 January 2017 10:51 PM
      To: [csv2odf:bugs] 9@bugs.csv2odf.p.re.sf.net
      Subject: [csv2odf:bugs] #9 -t option on csv2odf

      I dont't think I can explain it better, so here are the commands I used and
      the files are attached...

      python csv2odf RobSampleData.csv LarryExampleTemplate.xlsx
      LarryIntermediate.xlsx -s 12 -t 1 -S 2 --comment="Manufacturing Analysis"

      python csv2odf RobSampleData.csv LarryIntermediate.xlsx
      LarryExampleReport.xlsx -s 3 -e 9 -t 2 -S 1 --comment="Manufacturing
      Analysis"

      Attachments:

      [bugs:#9] https://sourceforge.net/p/csv2odf/bugs/9/ -t option on csv2odf

      Status: open
      Group: v1.0 (example)
      Created: Thu Jan 26, 2017 02:09 PM UTC by Rob Kinman
      Last Updated: Sat Jan 28, 2017 12:50 AM UTC
      Owner: Larry Jordan

      I have a .csv file generated by an internal report generator, which lists
      the report parameters in the first n rows, then the header row, then the
      data rows. I would like to extract the parameters into the first worksheet,
      and the data into a second worksheet.

      I have successfully extracted the data into separate workbooks, but cannot
      extract it into 2 worksheets in the same workbook. I had assumed that I
      could use the -t option to do this, but have not had any luck so far.

      Can you help?

      Sent from sourceforge.net because you indicated interest in
      https://sourceforge.net/p/csv2odf/bugs/9/

      To unsubscribe from further messages, please visit
      https://sourceforge.net/auth/subscriptions/

       
      alternate

  • Rob Kinman

    Rob Kinman - 2017-01-30

    Hi Larry

    Just a quick note to let you know that I have sorted out the content
    problems mentioned below.

    In the template, I had formatted three of the columns as 0.00%, but the
    stored procedure was actually writing then out as text fields (with the %
    sign embedded). If I redefine the template columns as xxxxx, the issue goes
    away.

    Once again, many thanks for your effort and patience.

    Regards

    Rob

    From: Rob Kinman [mailto:rob@mettisinteractive.com]
    Sent: 30 January 2017 11:25 AM
    To: '[csv2odf:bugs] ' 9@bugs.csv2odf.p.re.sf.net
    Subject: RE: [csv2odf:bugs] #9 -t option on csv2odf

    Hi Larry

    Lightbulb moment :) (I hadn't understood the significance of the
    intermediate spreadsheet.) It works as we need it now.

    Many thanks for your patience.

    I am still getting the following message, when I open the final spreadsheet
    in Excel 2016

    'We found a problem with some content in 'LarryExampleReport.xlsx'. Do you
    want us to try to recover as much as we can? If you trust the source of this
    workbook, click Yes'

    I click Yes, and the dataset is OK. Do you have any idea what causes the
    message? Could it be the content, the version of Excel etc.?

    As the final report will be generated by, and automatically sent to, fairly
    unsophisticated users, it would be useful if we could eliminate this
    message.

    Once again thanks

    Rob

    From: Larry Jordan [mailto:w322@users.sf.net]
    Sent: 29 January 2017 10:51 PM
    To: [csv2odf:bugs] <9@bugs.csv2odf.p.re.sf.net
    9@bugs.csv2odf.p.re.sf.net >
    Subject: [csv2odf:bugs] #9 -t option on csv2odf

    I dont't think I can explain it better, so here are the commands I used and
    the files are attached...

    python csv2odf RobSampleData.csv LarryExampleTemplate.xlsx
    LarryIntermediate.xlsx -s 12 -t 1 -S 2 --comment="Manufacturing Analysis"

    python csv2odf RobSampleData.csv LarryIntermediate.xlsx
    LarryExampleReport.xlsx -s 3 -e 9 -t 2 -S 1 --comment="Manufacturing
    Analysis"

    Attachments:

    [bugs:#9] https://sourceforge.net/p/csv2odf/bugs/9/ -t option on csv2odf

    Status: open
    Group: v1.0 (example)
    Created: Thu Jan 26, 2017 02:09 PM UTC by Rob Kinman
    Last Updated: Sat Jan 28, 2017 12:50 AM UTC
    Owner: Larry Jordan

    I have a .csv file generated by an internal report generator, which lists
    the report parameters in the first n rows, then the header row, then the
    data rows. I would like to extract the parameters into the first worksheet,
    and the data into a second worksheet.

    I have successfully extracted the data into separate workbooks, but cannot
    extract it into 2 worksheets in the same workbook. I had assumed that I
    could use the -t option to do this, but have not had any luck so far.

    Can you help?

    Sent from sourceforge.net because you indicated interest in
    https://sourceforge.net/p/csv2odf/bugs/9/

    To unsubscribe from further messages, please visit
    https://sourceforge.net/auth/subscriptions/

     

    Related

    Bugs: #9

    alternate

  • Larry Jordan

    Larry Jordan - 2017-01-30

    Very good. Glad it is working.

     

  • Larry Jordan

    Larry Jordan - 2017-02-09
    • status: open --> closed-fixed
     

Log in to post a comment.

MongoDB Logo MongoDB