FTP Quickbooks Integration: 2 Easy Steps

One of the most significant factors for a business is handling its accounts promptly. Clubbed with Dynamic Data changes, there is often a requirement to relay large amounts of data to ensure your accounts are kept on track. While Quickbooks is a great application to take care of the accounting requirements of businesses, integration with data exported via FTP can help bridge the gap in delay due to frequent or real-time data changes.

In this article, you will read about Quickbooks and its key features and the significance of FTP as a robust protocol for file transfer. You will also be guided through easy steps for FTP Quickbooks Integration and how automation can further streamline the process.  

What is Quickbooks?

Quickbooks is a premium accounting software that helps small and medium-sized businesses organize their accounting data. It offers a complete range of services for Prompt Accounting, Invoicing, Tax Management, Payroll Processing, Expense Tracking, and much more. It also offers various advanced features at the enterprise level, as illustrated below:

Key Features of Quickbooks

Some key features of Quickbooks work in great synchronization with your business for long-term growth and ease of Accounting Management. These include:

• Instant access and processing of critical financial information for your business is a key feature. Quickbooks allows you to customize reports and visualize your finances.
• Easy invoice creating and management for the professional and streamlined handling of cash flow is also made possible. You can even design your custom invoice with consolidated customer data.
• Get professional estimates, prompt service tax and VAT computations, and easy handling of foreign currencies all within a single platform. 
• Quickbooks also allows you to track expenses with a vivid overview of your company’s finances as a yearly snapshot.

What is FTP?

FTP or File Transfer Protocol is a fast and robust networking protocol that works upon the Client-Server model. Businesses prefer to use FTP for file transfers that are either operating between Remote Servers or are too bulky in size. FTP ensures quick File Transfers, Multi-Directional Transfers, and Transfer Scheduling. Active and Passive versions of FTP operate as shown:

How to Setup FTP Quickbooks Integration?

Integrating your CSV files to Quickbooks via FTP involves two major processes. First, you need to export CSV files from FTP and then import them to Quickbooks. You can follow these steps to carry out an FTP Quickbooks Integration:

Step 1: Export CSV Files from FTP

Step A: You can schedule an hourly CSV log file to be extended to your FTP server using LogicMachine. (Use “IP 192.168.1.11 login ‘ftplogin’ password ‘ftppassword’ ”)  You can also download the file manually from the /home/ftp directory. 

Step B: Start by scheduling a script with Scripting -> Schedule. Input the following code in the script editor.

require('socket.ftp')
 
-- ftp file
ftpfile = string.format('ftp://ftplogin:ftppassword@192.168.1.11/%s.csv', os.date('%Y-%m-%d_%H-%M'))
-- get past hour data (3600 seconds)
logtime = os.time() - 60 * 60
 
-- list of objects by id
objects = {}
 
-- objects with logging enabled
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
 objects[ tonumber(object.address) ] = {
   datatype = tonumber(object.datatype),
   name = tostring(object.name or ''),
 }
end
 
-- csv buffer
buffer = { '"date","address","name","value"' }
 
-- get object logs
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
 object = objects[ tonumber(row.address) ]
 
 -- found matching object and event type is group write
 if object and row.eventtype == 'write' then
   datatype = object.datatype
 
   -- check that object datatype is set
   if datatype then
     -- decode data
     data = knxdatatype.decode(row.datahex, datatype)
 
     -- remove null chars from char/string datatype
     if datatype == dt.char or datatype == dt.string then
       data = data:gsub('%z+', '')
     -- date to DD.MM.YYYY
     elseif datatype == dt.date then
       data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
     -- time to HH:MM:SS
     elseif datatype == dt.time then
       data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
     end
   else
     data = ''
   end
 
   -- format csv row
   logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
   csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))
 
   -- add to buffer
   table.insert(buffer, csv)
 end
end
 
-- upload to ftp only when there's data in buffer
if #buffer > 1 then
 result, err = socket.ftp.put(ftpfile, table.concat(buffer, 'rn'))
end
 
-- error while uploading
if err then
 alert('FTP upload error: %s', tostring(err))
end

Step C: Proceed with writing an existing file on FTP. The example below shows how you can do the same for a file called log.csv:

require('socket.ftp')
 
logtime = os.time() - 60 * 60
objects = {}
 
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
 objects[ tonumber(object.address) ] = {
   datatype = tonumber(object.datatype),
   name = tostring(object.name or ''),
 }
end
 
buffer = {}
 
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
 object = objects[ tonumber(row.address) ]
 
 if object and row.eventtype == 'write' then
   datatype = object.datatype
 
   if datatype then
     data = knxdatatype.decode(row.datahex, datatype)
 
     if datatype == dt.char or datatype == dt.string then
       data = data:gsub('%z+', '')
     elseif datatype == dt.date then
       data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
     elseif datatype == dt.time then
       data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
     end
   else
     data = ''
   end
 
   logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
   csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))
   table.insert(buffer, csv)
 end
end
 
if #buffer > 1 then
 data = table.concat(buffer, 'rn')
 res, err = socket.ftp.put({
   host = '192.168.1.40',
   user = 'ftp',
   password = 'ftp',
   command = 'appe',
   argument = 'log.csv',
   source = ltn12.source.string(data)
 })
end
 
if err then
 alert('FTP upload error: %s', tostring(err))
end

Step D: Next for FTP Quickbooks Integration, create a scheduled script that will run in 10 min intervals with the required object values as shown:

data = storage.get('myobjectlogs', {})
 
table.insert(data, {
 ['time'] = os.time(),
 ['counter-1'] = grp.getvalue('1/1/1'),
 ['counter-2'] = grp.getvalue('1/1/2'),
})
 
storage.set('myobjectlogs', data)

Step E: Make the following changes to the script shown above for local FTP Storage.

First, replace the lines – 

require('socket.ftp')
-- ftp file
ftpfile =
string.format('ftp://ftplogin:ftppassword@192.168.1.11/%s.csv', os.date('%Y-%m-%d_%H-%M'))

with these lines of code:

ftpfile = string.format('/home/ftp/%s.csv', os.date('%Y-%m-%d_%H-%M'))

Also, replace these lines/sections of code-

-- upload to ftp only when there's data in buffer
if #buffer > 1 then
 result, err = socket.ftp.put(ftpfile, table.concat(buffer, 'rn'))
end
 
-- error while uploading
if err then
alert('FTP upload error: %s', tostring(err))
end

With these following lines of code:

if #buffer > 1 then
 data = table.concat(buffer, 'rn')
 io.writefile(ftpfile, data)
end

Step F: For daily updates in FTP Quickbooks, you need to trigger a script that would run once a day and remove your old logs. Run the following script to do so:

function cleanup(dir, days)
 local now, files, mtime, delta
 
 now = os.time()
 files = io.ls(dir) or {}
 
 for _, file in ipairs(files) do
   file = dir .. '/' .. file
   _, mtime = io.stat(file)
 
   if mtime then
     delta = (now - mtime) / 86400
     if delta > days then
       os.remove(file)
     end
   end
 end
end
 
cleanup('/home/ftp', 60)

With these steps, your CSV data is ready to be exported via FTP periodically. You can even schedule the script in advance to make the export as and when needed. The next step for the FTP Quickbooks Integration involves importing the files to Quickbooks.

Step 2: Import files to Quickbooks 

If you’re using Quickbooks online for Cloud-based accounting for your business, follow these steps to import data that you have exported via FTP:

• Step A: Sign in to your Quickbooks account. 
• Step B: Select Settings -> Import Data.
• Step C: Click on Products and Services -> Browse -> Open. To upload from existing Google Sheets, you can select Connect -> Select. 
• Step D: Select Next. 
• Step E: To map the information, go to each Field and select the correct match to a field in Quickbooks Online through the drop-down menu.
• Step F: Click on Next, and you might see some red cells indicating invalid matches. Check each cell and enter a valid match. Follow Step 5 again to import the data.
• Step G: Select ‘Overwrite all values for each product or service’ to import with the identical name. 
• Step H: Select Import to finish the process. Thus, your FTP Quickbooks integration is carried through, and data has been imported to your account.

Note – To import files to Quickbooks, the IIF file format is the one that offers the best control. Use the CSVIIF utility to convert your exported data to IIF format. You can read more about this file conversion process here.

Conclusion

Using Quickbooks offers a variety of features for prompt accounting and finance management for your business. An easy FTP Quickbooks Integration helps import large files under a robust network. You can integrate many such platforms using Hevo Data.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share with us your experience in FTP Quickbooks Integrations. Let us know in the comments section below!

FAQs