This section explains the step to load the Alteryx text file into the SQL Server table. In the Alteryx Read Text File article, we presented the process to read the text file and consider 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.
Connect the Input Data to 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 host, and SQL authentication. Don’t forget to click the test button to check the connection is successful or not.
We selected the SQLTEST database as the default database.
Click OK to close the SQL Server 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.
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 messages.
As you can see, Alteryx loaded all the 15 records and then deleted the customers whose occupation is clerical.
