Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Is there a faster way? Access Database (ACCDB) from csv

Meteor

My company uses a tool that will only ingest Access Databases. The part of the team I work on usually has larger access files that we make a lot of changes throughout the day. Using the output data tool to access is a very slow process (15 mins). Has anyone found a faster way to export to access using any of the other tools (python??).


 

Highlighted
Community Operations Manager
Community Operations Manager

@jeh 

 

What driver are you using? If you are using a 32bit driver, then the time you are giving is probably right, especially if it's a lot of data. The drivers are what is going to slow down the Output tool not Alteryx. We suggest using a 64bit driver. The 32bit is like trying to drink a milkshake out of a cocktail straw, where 64bit is a big straw. It could also be connection speeds between the driver and the database, so you will want to check the speeds there as well.

 

DanM

Highlighted
Meteor

I am already using the 64 bit access database driver. The csv file (10-30 million rows) can take upwards of 15 mins to write using the built in alteryx output tool. We can get the file to write much faster (1-2 mins) using the python tool using the code below, however, we end up with missing data because we cant find the right way to type the data using the access database driver in python. 

 

import pyodbc

conn_str = (    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'    r'DBQ=C:/Users/useraccount/Desktop/HWN_2020/Sales History HWN.accdb;'
)cnxn = pyodbc.connect(conn_str)crsr = cnxn.cursor()strSQL = ("SELECT * INTO [Sales History Template] FROM [text;HDR=Yes;FMT=Delimited(,);" + \
          "Database=C:/Users/useraccount/Desktop/HWN_2020/History/Retail].HWN_History_Retail_Sales.csv;")crsr.execute(strSQL)cnxn.commit()cnxn.close()

 

Labels