Export accessdb tables to csv

Question

Been working on a snippet of java code to export a few tables from an accessdb to CSVs. I want to deploy this code as a Lambda function. I've tried using Jackcess, but the following

        try {
            String dateOfExtraction = LocalDateTime.now().toString();
            Database db = DatabaseBuilder.open(new File("java-runtime/src/access_db_file.accdb"));
            System.out.println(db.getTableNames());
            ExportUtil.exportFile(db, "table_name", new File("table_name" + dateOfExtraction + ".csv"));
        } catch (IOException e) {
            e.printStackTrace();
        }

throws the error: java.io.FileNotFoundException: given file does not exist: C:\Users\john.doe.ctr\Desktop\Work\table_name

I am running my code on a mac, this filepath is from the user that provided me with the DB. Is that some kind of permissions error? Should I just use UCanAccess instead? I can't use any of the UCanAccess command line tools, I have to run this in a lambda. The System.out.println(db.getTableNames()); line works exactly as expected, and prints a list of all of the tablenames in the accessdb.

Answer 1

There can be several problems in the code.

The first, you are using LocalDateTime.now().toString() as part of the filename of the CSV file in which the information will be saved. It will give you something like:

2021-05-02T23:42:03.282

In some operating systems - you mentioned MacOS but it should allow you to create a file with that name - this name can be a possible cause of problems; please consider use something less error prone like System.currentTimeMillis:

String filename = "table_name" + System.currentTimeMillis() + ".csv";
ExportUtil.exportFile(db, "table_name", new File(filename));

Having said that, please, be aware that in the AWS Lambda function you probably will need to store your results in external storage, typically S3: you have the ability to write to the filesystem but it is usually of relevance when working with temporary files, not with persistent storage. Please, consider for instance the following code snippet.

// Here you can have a problem as well when trying to access the filesystem
// to read the Access file, but the API does not give you another option
// Probably deploying (https://docs.aws.amazon.com/lambda/latest/dg/lambda-java.html)
// your lambda function as a container (https://docs.aws.amazon.com/lambda/latest/dg/java-image.html) 
// and include your database file
Database db = DatabaseBuilder.open(new File("java-runtime/src/access_db_file.accdb"));
System.out.println(db.getTableNames());
String filename = "table_name" + System.currentTimeMillis() + ".csv";
// Instead of writing to a file, write to a byte array through a writer
try (ByteArrayOutputStream output = new ByteArrayOutputStream();
     BufferedWriter writer = new BufferedWriter(
         new OutputStreamWriter(output));
) {
  // Dump data
  ExportUtil.exportWriter(db, "table_name", writer);
  // Just in case
  writer.flush();
  // Get actual information
  byte[] data = output.toByteArray();
  // Save data to S3: please, consider refactor and organize the code
  S3Client s3 = ...; // Initialize as appropriate
  String bucketName = "your-bucket";
  String objectKey = filename; // object key, same as filename, for example
  // Perform actual S3 request
  PutObjectResponse response = s3.putObject(
    PutObjectRequest.builder()
      .bucket(bucketName)
      .key(objectKey)
      .build(),
    RequestBody.fromBytes(data)
  );
} catch (IOException e) {
  e.printStackTrace();
}

From a totally different perspective, the problem can be caused because table_name is a linked table. When you create a linked table, you need to define the path to the linked information: in your case, probably this information is stored in C:\Users\john.doe.ctr\Desktop\Work\table_name in the original computer of your client.

If you have the MS Access program, you can verify if that is the actual problem with the help of Linked Table Manager.

If you do not have the MS Access program, you can use the Database class as well. Please, consider the following example:

Database db = DatabaseBuilder.open(new File("java-runtime/src/access_db_file.accdb"));
Table table = db.getTable("table_name");
boolean isLinkedTable = db.isLinkedTable(table);

If the table is linked you need two things: on one hand, the linked information itself and, on the other, you need to provide a convenient implementation of the LinkResolver interface, probably by extending CustomLinkResolver. This interface basically provides you the ability to map the location of a linked table to a different path. Please, consider review this test for a convenient example of such as implementation.

For instance, think in something like this:

public class RemapLinkResolver implements LinkResolver {

  // Maintain a correspondence between the original linked db file
  // and the same db in your new filesystem 
  private Map<String, String> dbLinkeeFileRemap = new HashMap<>();

  public void remap(String originalLinkeeFileName, String newLinkeeFileName) {
    this.dbLinkeeFileRemap.put(originalLinkeeFileName, newLinkeeFileName);
  }

  @Override
  public Database resolveLinkedDatabase(Database linkerDb,
                                        String linkeeFileName)
    throws IOException {
    // if linker is read-only, open linkee read-only
    boolean readOnly = ((linkerDb instanceof DatabaseImpl) ?
                       ((DatabaseImpl)linkerDb).isReadOnly() : false);
    String newLinkeeFileName = this. dbLinkeeFileRemap.get(linkeeFileName);
    if (newLinkeeFileName != null) {
      return new DatabaseBuilder(new File(newLinkeeFileName))
        .setReadOnly(readOnly).open();
    }

    // Fallback to default
    return LinkResolver.DEFAULT.resolveLinkedDatabase(linkerDb, linkeeFileName);
  }
}

Then, use it in your code:

Database db = DatabaseBuilder.open(new File("java-runtime/src/access_db_file.accdb"));
RemapLinkResolver linkResolver = new RemapLinkResolver();
linkResolver.remap(
  "C:\Users\john.doe.ctr\Desktop\Work\table_name",
  "java-runtime/src/table_name.accdb"
);
db.setLinkResolver(linkResolver);
// Continue as usual

I hope you get the idea, please, adapt the paths and, in general, the code as appropriate.

Answer 2

Jccs advice looks pretty solid. Can you try/confirm/elaborate on some of the following, too?

1. One thing is you said you the code on YOUR mac but the filepath is from ANOTHER user. Are you somehow implicitly or explicitly referencing a folder that's not available on your Mac and hence you can't access an invalid folder path? Jackcess would probably be OK creating a file in a folder, but if a chunk of the parent folder path for the export is missing, maybe it can't or doesn't build up the necessary sub-folders to create the file and throws error implicitly?

2. Is there extra configuration the Lambda needs to access the folder paths at play in the previous comment given that I imagine it runs in a cloud stack?

3. Does jackess require you initial/create the file in place before it opens it for file writing..? Doesn't seem like it from the API.

4.Can you temporarily hardcode new File("table_name" + dateOfExtraction + ".csv")" to instead be something simple like new File("steve.csv"). I am specifically curious to see if your error message updates accordingly to complain it can't access steve.csv in that folder.