This section explains the steps to load the Alteryx text file into the SQL Server table. In the Alteryx Read Text File article, we presented the process of reading the text file and considered it a continuation.
Drag the Input Data and choose the Customers_text file. The following image shows the data within that file.
Steps to load Alteryx Text File into SQL Server
From the Favorites tab, drop the Output Data into the Canvas. Please connect the Input Data to the Output Data, and click the down arrow to write the data to a file or database.
Within the Data Connections window, Go to the Data Sources tab to select the required connection. Here, we want to load a text file to SQL Server, so select ODBC connection.
Please provide the unique connection name, Server Name or Hostname, Authentication (Windows or SQL), and the default Database name. If you choose the SQL Authentication, provide the username and password.
Here, we assigned SQLDBConnection as the connection Name, our Server name PRASAD as the host, and SQL authentication. Don’t forget to click the test button to check whether the connection is successful or not.
Please use the down arrow under the Default database section to choose the required database. Here, we selected the SQLTEST database as the default SQL Server database to load the Alteryx Text File. Click OK to close the Database Connection window.
Next, it will ask to enter the output table name. Here, we entered AlteryxCustomersText as the output SQL table name.
The following screenshot shows the configuration setting of the SQL output data.
Under the Output options, you have an opportunity to perform CRUD operations on the above table. For example, you can create a new table, drop and recreate it, append it to an existing table, update records, etc.
We left the option to create a table and hit the run button to Load the Alteryx Text File into the SQL Server. Within the Management Studio, you can see the result.
There are two essential options available in the SQL configuration tab, and we show you an example to use them.
- Pre Create SQL Statement: It runs before performing SQL operations. We used a drop statement to delete the existing table.
- Post Create SQL Statement: It runs after performing SQL operations. We used Delete Statement to delete customers whose occupation is Clerical.
Hit the run button and see the Load Alteryx Text File into SQL Server messages.
As you can see, Alteryx loaded all 15 records and then deleted the customers whose occupation is clerical.
