Output Access DB query data to a csv file using Java

Question

I am able to open the database file and get the query name and the statement. How do I output the query data to a csv? I thought he below exportWriter would do it but it doesn't work.

Database db = Database.open(new File(args[0]));    
    for(Query query : db.getQueries()) {
        System.out.println(query.getName() + ": " + query.toSQLString());
        if query.getName() = "thequerytooutput" {
            BufferedWriter csvOut = new BufferedWriter(new OutputStreamWriter(System.out));
    ExportUtil.exportWriter(db, query.getName(), csvOut, true, null, '"', 
        SimpleExportFilter.INSTANCE);
        }
    }

Please describe how it does not work like error message, or if no error then the actual output. — joseph
– joseph, Commented Aug 13, 2020 at 19:35
This is the error message I'm getting Exception in thread "main" java.lang.NullPointerException at com.healthmarketscience.jackcess.Cursor$Id.<init>(Cursor.java:1490) at com.healthmarketscience.jackcess.Cursor$TableScanCursor.<init>(Cursor.java:1334) at com.healthmarketscience.jackcess.Cursor$TableScanCursor.<init>(Cursor.java:1322) at com.healthmarketscience.jackcess.Cursor.createCursor(Cursor.java:107) at com.healthmarketscience.jackcess.ExportUtil.exportWriter(ExportUtil.java:271) at DumpQuery.main(Unknown Source) — zjb
– zjb, Commented Aug 13, 2020 at 20:30

jtahlborn · Accepted Answer · 2020-09-09 18:20:07Z

1

ExportUtil does not run queries, it only dumps tables. You need to provide a valid table name as the second parameter. Jackcess does not have the ability to execute sql queries, as noted here.

answered Sep 9, 2020 at 18:20

jtahlborn

53.8k5 gold badges80 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JeroSquartini · Accepted Answer · 2020-08-13 20:19:00Z

0

I think a better option would be to first store your values from the database into a "two dimensional" list of Strings( I'm assuming you know how to do that[if not, tell me and I'll clarify more]). And then use FileWriter to write the array into a CSV file.

This example was taken from stackabuse.com

List<List<String>> rows = Arrays.asList(
    Arrays.asList("Jean", "author", "Java"),
    Arrays.asList("David", "editor", "Python"),
    Arrays.asList("Scott", "editor", "Node.js")
);

FileWriter csvWriter = new FileWriter("new.csv");
csvWriter.append("Name");
csvWriter.append(",");
csvWriter.append("Role");
csvWriter.append(",");
csvWriter.append("Topic");
csvWriter.append("\n");

for (List<String> rowData : rows) {
    csvWriter.append(String.join(",", rowData));
    csvWriter.append("\n");
}

csvWriter.flush();
csvWriter.close();

edited Aug 13, 2020 at 20:19

answered Aug 13, 2020 at 20:10

JeroSquartini

3671 gold badge2 silver badges13 bronze badges

5 Comments

zjb Over a year ago

Thank you for your suggestion. I should have included the imports that I use in the script import com.healthmarketscience.jackcess.query.Query; import com.healthmarketscience.jackcess.Database; import com.healthmarketscience.jackcess.ExportUtil; import com.healthmarketscience.jackcess.SimpleExportFilter; I think if I can get the query rows to a <List> then I should be able to do what you suggested.

JeroSquartini Over a year ago

Okay great, try that. If you can't manage to do it, I'll help you later. Right now I'm heading off from home so I won't touch my computer for the next 2-3 hours.

JeroSquartini Over a year ago

@zjb Hi, I'm back home. Could you do it?

zjb Over a year ago

No. I have not. :( still trying

JeroSquartini Over a year ago

Oh, don't worry, I'm sure you'll be able to do it. Now I'm in bed and about to go to sleep but I'll help you tomorrow so you can sort this out. In the meantime, can you clarify the query you are using to access the database and how many columns and rows the ResultSet has? :)

Collectives™ on Stack Overflow

Output Access DB query data to a csv file using Java

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related