Sequenchel has the option to import several file types into the database. First select the connection to which you wish to import the file and next open the Import window.
You can import Excel files, as well as XML files and text based files (*.txt; *.csv). If an Excel document has multiple sheets or an XML file has multiple data sets, you will see the number of sheets or data sets that have been imported. For every data set you can define whether or not the data should be uploaded to the database or you can select to upload only the currently visible data set. When uploading multiple data sets at once, make sure all data sets have the exact same columns (name and data type) as your target database table.
Enter the path and file name of the file you wish to import. You can also browse for a file.
Once you have entered the filename and set all the options, import the file by clicking “Import & Upload File”.
- “Text File Contains Headers” : The first row of the file has column names instead of data. If you do not select this option, the columns will be named col1 ~ coln and the first row will be imported as data.
- “Text File Contains Qouted Values” : The values in the text file are enclosed in double quotes (” “). If you do not select this option all double quotes are regarded as part of the data.
- “Text Field Delimiter” : The values in 1 row of data are separated by this specific character.
- “Convert all Data to Text” : When importing data all values, including dates and numeric values are regarded as charater (text) values.
- “Convert Empty values to DBNull” : All empty values like empty string or no data present is converted to database NULLs before upload.
If you have selected the option “Output to Screen”, the imported file will be displayed in the output screen.
If you have selected “Output to File” and have entered a filepath and name. The imported file will be exported into a file with the name you provided. Valid filename extensions are: .xlsx, .xml, .csv and .txt. Sequenchel will match the file type to the extension provided. Existing files will be overwritten without warning. Excel and XML can handle multiple data sets, but text files cannot. If you export to a text file only the first selected data set will be exported.
If you have selected the option “Output to Database”, the imported file will be uploaded into the tabel specified. Make sure the tabel has the exact same columns as the source file.
Servername, database name and table name are required fields. You can use Windows Authentication, which uses your windows login name and password to connect, or you can use SQL Authentication with a username and password provided by you.
Large files can be imported in batches to prevent out of memory errors. The size of the batch is dependent on the size of a single row in your datafile. Smaller batches use more overhead but a larger batch may contain too much data. Use trial and error to determine the best size for a batch for your file.
Create Target Table : This creates a table with the same columns as your import file. All fields will have a character data type.
Clear Target Table : Delete all data from the target table before importing new data.
Upload as XML : If you are uploading an XML file, the will be be uploaded to SQL Server without any alterations. If you are uploading a different file, the data will be converted to XML and uploaded as such. This requires a special formatted table. Sequenchel can create this table for you.
You can view every data set of the imported document before uploading and determine if the data should be uploaded. If you don’t want to process the selected data set, uncheck the “Upload This Table” option. if there are many data sets and you don’t want to check them all, you can click “Upload currently selected Table” to upload only the data set that is currently displayed. This option is recommended for exporting to text based files that do not support multiple data sets or for importing data from Excel documents with several different sheets.
If you have imported a file to the screen only or you have made adjustments in your options, you can next export it to a file or upload it to a database by pressing “(Re)Upload File”. This will upload the contents of the selected data set(s) to the selected (other) output options.
With this option you can first check the contents of the file before uploading it to a database or exporting it to a (different) file.
File Import is also available through the Command Line (SeqCmd).