Bulk Uploading File Attachments to Quickbase

Application Note for QuNect ODBC for QuickBase

To bulk upload files to a file attachment field in Quickbase you want to make sure that you have your DSN configured with File Attachment Fields -> Represent as: set to Text field with URL to file attachment in Quickbase If you are using a connection string instead of a DSN make sure you are NOT using the FILE=BINARY or the FILEPATH=C:\Folder1\Folder2 connection string parameters. The file attachment field in Quickbase is presented via QuNect ODBC for QuickBase as a text field. The text field contains the URL to the file attachment itself. If you set this text value using either an INSERT or an UPDATE statement to the fully qualified path of a file on the computer where QuNect ODBC for QuickBase is installed, QuNect ODBC for QuickBase will upload that file into the file attachment field. For example the following SQL statement will create a new record placing the file C:\autoexec.bat into the file attachment field called Batch Files

INSERT INTO "File Table bepza6nb3" ("Batch Files") Values ('C:\autoexec.bat')

You could upload the C:\autoexec.bat file to all records that did not already have an attached file in the Batch Files field like this:

UPDATE "File Table bepza6nb3" SET "Batch Files"='C:\autoexec.bat' WHERE "Batch Files"=''

Both of these examples are not that useful. But they serve to illustrate the basic capablity. To do a bulk upload you need to have preexisting records to upload the files into. The records need to have enough information in them to let you know which file belongs in which record. So in the simplest case if there was a text field called File Name that contained the name of the file that needed to be uploaded to the Picture field, then you could write a SQL statement like this to upload a folder full of pictures. Let's assume the folder full of pictures is D:\Employees\Pictures\

UPDATE "Employees beqra6nb3" SET "Picture"={fn CONCAT('D:\Employees\Pictures\', "File Name")} WHERE "Picture"='' and "File Name" <> ''

If the File Name contained only the prefix of the file name but not the file extension then you could append the file extension like this:

UPDATE "Employees beqra6nb3" SET "Picture"={fn CONCAT({fn CONCAT('D:\Employees\Pictures\', "File Name")}, '.jpg')} WHERE "Picture"='' and "File Name" <> ''

If you don't have preexisting records then you can create them with the filename in a text field by running a directory listing of the folder that contains the files you want to upload. If you pipe this directory listing to a file you can import the directory listing file into the Quickbase table where you want to bulk upload the file attachments. Now you have preexisting records with the filenames in a regular text field. The DOS command to create a directory listing (of the directory C:\Employees\Pictures) in a file (called filewithfilenames.csv) looks like this:

C:\Employees\Pictures>dir /b > filewithfilenames.csv