• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database View to CSV and XML

 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyone,

I have a task to create a batch which converts a given (database) view to XML and CSV and sends the resulting file by mail or FTP. Many APIs are available for this conversion on the net, however I am required to find the number of rows contained in the view and write it in a log (it is compulsory). This cannot be done with the tools I have found so far, there is no possibility to get the number of rows, only the number of columns.

Besides, the view might change after the query, so a count may not give a reliable result.

In addition, the view may have a complex SQL query (with several joins, maybe) and the number of records I have to deal with can be quite large(thus the XML conversion shouldn't use DOM).

Do you know of any useful APIs, so that I don't have to reinvent the wheel? Is there already an efficient solution for such situations or do I have to code one myself?

Thank you,

Manuela
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you have the XML or CSV in memory after using the tool? If so, you could do an XPath expression or line count to see how many rows there are.
 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, I can't have the XML/CSV in memory, because it can reach big dimensions (the number of query results can be huge).
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you store it on the file system? Same idea: xpath/line counting from there.
 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I do create XML/CSV files on the disk, but I cannot parse them, because they may have a really big size.
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe I'm missing something, but why don't you count the rows as you're writing them to the CSV or XML file? Presumably you are running some kind of SELECT query against the view to fetch the data to put it into CSV format, so this would ensure that your count reflects the number of record processed from your view. Or maybe you could use a LineNumberReader to find the total no of lines in the CSV file and subtract 1 for the header row?

You could also run a SELECT COUNT(*) FROM [your view] query, although this effectively means reading your view twice - once to build the CSV and once to count the rows in the view. This might be a slow, but you can make both queries part of a single transaction to ensure read consistency.
 
Manuela Grindei
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:Maybe I'm missing something, but why don't you count the rows as you're writing them to the CSV or XML file? Presumably you are running some kind of SELECT query against the view to fetch the data to put it into CSV format, so this would ensure that your count reflects the number of record processed from your view. Or maybe you could use a LineNumberReader to find the total no of lines in the CSV file and subtract 1 for the header row?

You could also run a SELECT COUNT(*) FROM [your view] query, although this effectively means reading your view twice - once to build the CSV and once to count the rows in the view. This might be a slow, but you can make both queries part of a single transaction to ensure read consistency.


I actually did count the rows while writing them to the file (I made my own implementation in the meantime). What I wanted to know was if there was already an API adapted to these requirements. The tools I saw on the net did not have any way to find the number of rows.

Thank you all for your support!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic