What I am trying to do seems basic: Select a bunch of fields from a database table, and export them to a text file with a delimiter (comma, pipe, whatever). However, I cannot get this to work without doing the following set of steps:
1. Define the query in ADO Iterator
2. Define a variable for each field I am selecting
3. Assign each field to a variable
4. Trim every variable separately (left and right trim)
5. Write each record to the file by specifying the full line (variables and delimiters).
This is fine for a few fields, but I have one file that has well over 30 fields in it.
I tried doing a MSSQL Select and sending the output to a file, but there was no way to trim the fields before sending the output through to the file, and there was no way (that I found) to make the column widths variable in the file (i.e. have each column value take up space equal to its length, rather than have every field use a predetermined amount of space).
Please tell me I am missing something. Creating 30+ variables, then trimming 30+ fields one at a time, then specifying the entire line using variables and delimiters when all I want to do is to send the selected output to the file directly seems like a large hassle and one that will slow down the process considerably.
Any help/guidance is appreciated.
Thanks,
Jonathan
Hi Jonathan,
In the select query format the fields how you want at this stage into one combined field. The single column from the select statement then can be read into a variable and written to a file.
There are a number of string functions in SQL, such as Rtrim and Ltrim. The trick will be worrying about nulls, which you would have to handled in the FinalBuilder script as well.
This solution seems to work pretty well, as long as I look for the NULL values when forming the string. Thank you for the help!