Importing data in csv files, from excel files and web pages are often required. Each software vendor have their own tools to import such data. In SAP's SQL Anywhere 17 you can import data in text files using the Interactive SQL tool.
For working with this post it is assumed you have installed SAP's SQL Anywhere 17 and you have an available csv file. For this post I will be using a text file created using the export-import wizard tool from a table in the Northwind database on a SQL Server 2012. You can of course use a suitable text file. Generating the FFD.txt file is described here (http://hodentekmsss.blogspot.com/2016/12/creating-csv-file-from-sql-server-2012.html).
The following is the procedure to import the text file into sql Anywhere 17.
From All Programs in Windows 10 click on Interactive SQL under SQL Anywhere 17.
This brings up the window 'Connect to a SQL Anywhere Database'.
Fill in the authentication information (dba\sql) and click Browse on the ODBC Data Source name and pick SQL Anywhere 17 Demo.
Click Tools and Test Connection to ensure you are connected.
SAP_1
Click Connect and you are connected to the demo database. Your personal server is running now as shown.
SAP_2
The Interactive SQL window should now be up with connection established to the demo database as shown.
SAP_3
Click Data | Import... to open the Pop-up window Import Wizard as shown.
SAP_4
Choose the default 'In a text file' and click Next to open the 'Select the file to import' page of the Import Wizard as shown.
SAP_5
Browse and locate the text file on your computer.
Click Next.
Click Next. In the next window use Comma as the field separator and insert check mark for 'The first line contains column names' as shown.
Click Next. The 'Use this panel to associate a database column with each imported
column. Change datatype of the two date columns to 'time' from 'varchar'. You may highlight a column and change the data type, if it is not what you want. Here in is the drop-down for the Order Date.
Click Next. The "Where do you want to save data?" window is now open with two options. Choose to get the data into a new table and change default name of table to FFDImport.
Click Import. The data gets imported.
The text data gets imported into the table FFDImport.
Close the Import Wizard.
The Interactive SQL window shows the SELECT * from the new table as shown.
SAP_11
Although the import was successful, the time related columns did not come through correctly. In the next post we shall see if we can correct this. For this post the 'varchar' data type for the OrderDate and RequiredDate were replaced by the 'time' data type in the Interactive SQL's, import data wizard.
The table is now visible in the SQL Explorer as shown.
SAP_12.png
For working with this post it is assumed you have installed SAP's SQL Anywhere 17 and you have an available csv file. For this post I will be using a text file created using the export-import wizard tool from a table in the Northwind database on a SQL Server 2012. You can of course use a suitable text file. Generating the FFD.txt file is described here (http://hodentekmsss.blogspot.com/2016/12/creating-csv-file-from-sql-server-2012.html).
The following is the procedure to import the text file into sql Anywhere 17.
From All Programs in Windows 10 click on Interactive SQL under SQL Anywhere 17.
This brings up the window 'Connect to a SQL Anywhere Database'.
Fill in the authentication information (dba\sql) and click Browse on the ODBC Data Source name and pick SQL Anywhere 17 Demo.
Click Tools and Test Connection to ensure you are connected.
SAP_1
Click Connect and you are connected to the demo database. Your personal server is running now as shown.
SAP_2
The Interactive SQL window should now be up with connection established to the demo database as shown.
Click Data | Import... to open the Pop-up window Import Wizard as shown.
SAP_4
Choose the default 'In a text file' and click Next to open the 'Select the file to import' page of the Import Wizard as shown.
Browse and locate the text file on your computer.
Click Next. In the next window use Comma as the field separator and insert check mark for 'The first line contains column names' as shown.
Click Next. The "Where do you want to save data?" window is now open with two options. Choose to get the data into a new table and change default name of table to FFDImport.
Click Import. The data gets imported.
The text data gets imported into the table FFDImport.
Close the Import Wizard.
The Interactive SQL window shows the SELECT * from the new table as shown.
Although the import was successful, the time related columns did not come through correctly. In the next post we shall see if we can correct this. For this post the 'varchar' data type for the OrderDate and RequiredDate were replaced by the 'time' data type in the Interactive SQL's, import data wizard.
The table is now visible in the SQL Explorer as shown.
No comments:
Post a Comment