Quote string values before export with xp_write_file

[former_SQLA_member1694869]

I have a cursor that adds a lot of fields (tab separated) from a select in a declared variable (declared as long varchar). Then I use xp_write_file to export it to a text file. My question is how I can quote all strings in the variable as I can do with ex. "output to"?

[VolkerBarth · Accepted solution]

Rolle has asked in a comment:

This doesen't work:

UNLOAD select list(string(name,'\\x09'),char(09) order by column_number) from sa_describe_query('select * from #tmp') to 'c:\\temp\\test.txt' encoding 'windows-1252' delimited by '\\x09';

Everything is on the same row.

Well, this should unload the column headers, so it is expected to be only one row, isn't it?

Here's a sample with an unload from the system table sysusertype:

unload
   select list('''' || name || '''', ',' order by column_number)
      from sa_describe_query('select * from sys.sysusertype order by type_name')
   to 'C:\\MyFile.txt' quotes off escapes off;
unload
   select *
      from sys.sysusertype
      order by type_name
   to 'C:\\MyFile.txt' quotes on append on;

Note the "manual" quoting of the first unload: I have used list with single quotes around the column names to build the following result (which is one single string value, not a sequence of strings):

'type_id','creator','domain_id','nulls','width','scale','type_name','default','check','base_type_str','extended_base_type_str'

Then I have used the QUOTES OFF and ESCAPES OFF clause to unload that as-is - the default QUOTES ON would double the single quotes and would insert a leading and trailing quote (as the result set is just one single value), as seen here:

'''type_id'',''creator'',''domain_id'',''nulls'',''width'',''scale'',''type_name'',''default'',''check'',''base_type_str'',''extended_base_type_str'''

ESCAPES OFF is necessary when using QUOTES OFF as otherwise the comma is replaced by its hexadecimal representation:

'type_id'\\x2c'creator'\\x2c'domain_id'\\x2c'nulls'\\x2c'width'\\x2c'scale'\\x2c'type_name'\\x2c'default'\\x2c'check'\\x2c'base_type_str'\\x2c'extended_base_type_str'

Finally, with QUOTES OFF ESCAPES OFF and the second UNLOAD appended it should give something like:

'type_id','creator','domain_id','nulls','width','scale','type_name','default','check','base_type_str','extended_base_type_str'
1,'type_id',1,'smallint','smallint',2,0,2,,,'ut',24,1,'SYS','ISYSUSERTYPE','type_id',0,0,,
2,'creator',21,'unsigned int','unsigned int',4,0,4,,,'ut',24,2,'SYS','ISYSUSERTYPE','creator',0,0,,
3,'domain_id',1,'smallint','smallint',2,0,2,,,'ut',24,3,'SYS','ISYSUSERTYPE','domain_id',0,0,,

[former_SQLA_member1694869 · Accepted solution]

Works perfect! Thanks.

Another question.

When I use the OUTPUT TO becomes decimals ok in decimal field. But when I use the UNLOAD becomes decimals strange.

If a value is 0.175 in a field, it will be the same with OUTPUT TO. But with the TO UNLOAD appears value of .175. Why?

[VolkerBarth · Accepted solution]

IMHO, that's not really strange but just different formatting. "0.175" and ".175" are the same decimal values so no data is lost.

FWIW, DBISQL itself displays the same value diffently on my box with German-locale:

select cast(0.175 as numeric)

return "0,175" in DBISQL (note the decimal comma) and ".175" in dbisqlc.

---

That being said, I do not know how to influence OUTPUT and/or UNLOAD to use a different format for decimal data. Of course you are free to choose your own format preference by converting the data to string within the query yourself.

[former_SQLA_member1694869 · Accepted solution]

Strange...

This return 0,175

select cast(0.175 as numeric);
OUTPUT TO 'C:\\\\Temp\\\\test1.txt' WITH COLUMN NAMES encoding 'WINDOWS-1252' Delimited BY '\\x09';

This return .175

 UNLOAD select cast(0.175 as numeric) TO 'C:\\\\Temp\\\\test2.txt'  encoding 'WINDOWS-1252' Delimited BY '\\x09';

[former_SQLA_member1694869 · Accepted solution]

I do not understand how I can get UNLOAD to export 0,175 from

UNLOAD select cast(0.175 as numeric) TO 'C:\\\\Temp\\\\test2.txt'  encoding 'WINDOWS-1252' Delimited BY '\\x09';

[VolkerBarth · Accepted solution]

Why would you expect "0,175"? That's not a valid numerical value within SQL, as SQL expects the point as decimal delimiter. You would not be able to load such data back into SQL Anywhere...

If you need to export data with German formatting rules (i.e. decimal comma, point as thousand separator), confine that old FAQ and my answer there:

How do I format numbers in SQLA

[former_SQLA_member1694869 · Accepted solution]

I want to export the numeric value with comma decimal separator instead of the point that it is now. Is it impossible without a function?

[VolkerBarth · Accepted solution]

With UNLOAD, I don't think so - unless you would explicitly cast decimal data to string and then replace the decimal point with a comma within the select list, such as

select replace(cast(myNumber as varchar), '.', ','), ...

Frankly, for more than a few columns, a function containing that logic (like the cited one) would be way more comprehensible IMHO.

[former_SQLA_member1694869 · Accepted solution]

There's nothing else I can do to get the proper decimal delimiter, according to Danish standard when I used Unload? Standard is 0,175 and not .175 in Denmark.

[VolkerBarth · Accepted solution]

AFAIK, as stated, no, as UNLOAD is primarily meant to unload data that can be re-loaded later.

What application do you use to import the unloaded data? Tools like Excel can usually be configured to recognize decimal point vs. decimal comma and should accept missing zeroes before the decimal separator without problems. (In contrast, the stored function I had built as mentioned in one of my comments above was used with Word as that does not have such import options by default.)

[former_SQLA_member1694869 · Accepted solution]

I export the data to a text file that we later use in a external program where the file is imported. There, unfortunately, we have no ability to influence the decimal separator. Unload used in a Function...

Couldn't I use set nls_numeric_characters or something...?

[VolkerBarth]

So you add the results of the query explicitly row-by-row, column-by-column to a "result variable" (i.e. by concatenating the values)?

If so, I guess you will then have to add the quotes explicitly, too.

I have not really understand your requirementy, but if you want a result similar to ISQL's OUTPUT statement, but are within a stored procedure or the like where OUTPUT is not available, you should consider the UNLOAD statement. It has almost the same format options as OUTPUT (though with different syntax, here QUOTE and/or QUOTES ON/OFF), and it can be used anywhere.

Note, it can also be used to write the result set to a variable by using the UNLOAD ... INTO VARIABLE clause, in case you would need to modfy that before doing the output to a file.

[Breck_Carter]

consider the UNLOAD statement.

Yes... the alternative to OUTPUT is generally regarded as UNLOAD, not xp_write_file.

[former_SQLA_member1694869]

That's right. I have a Function with a cursor that I lopp row by row to a variable and then export it to a text file with xp_write_file.

How do I export to a text file via the UNLOAD function from a SQL including column names?

[former_SQLA_member1694869]

How can I get the column names in the smoothest way, when I use the UNLOAD. I have found this, but I do not understand how to get the column names in the exported file

unload select location, r_date, r_time, ct_codel_code_title from temp_data 
    TO 'C:\\files\\headerfile.txt'
    FORMAT TEXT
    DELIMITED BY '\\x09'
    QUOTE ''
    ENCODING 'UTF-8'

How to get this in the exported file?

select list(string('"',name,'"'),char(09) order by column_number)
from sa_describe_query('SELECT * FROM temp_data order by r_date, r_time');

[VolkerBarth]

What version do you use?

v17 has introduced the WITH COLUMNS NAMES clause to the UNLOAD statement.

For v16 and below there are basically two methods, methinks:

1. You use two UNLOAD statements, the first to select the column names (i.e. one single row, possibly based on the results of sa_describe_query for the real query) as in your sample. And a second one with the real query and option APPEND ON.

2. Or you use one UNLOAD statement and use an UNION ALL statement within, such as (the untested)

UNLOAD
   SELECT "location", "r_date", "r_time", "ct_codel_code_title"
   FROM
   (SELECT 0 as rowNo,
       "location", "r_date", "r_time", "ct_codel_code_title"
    UNION ALL
    SELECT ROW_NUMBER() OVER (ORDER BY location),
       location, r_date, r_time, ct_codel_code_title
    FROM temp_data) dt
   ORDER BY rowNo
TO ...

Note, for the 2nd approach, you will need to use a method to number the rows within result set so the row with the column names is unloaded first. Here this is done with derived query and the ROW_NUMBER() OLAP function.

[former_SQLA_member1694869]

We are using version 16, so it is the first option that is possible when the SQL includes over 80 columner. Do you have a working example where sa_describe_query used? With two UNLOAD statements and APPEND ON on the second...

[former_SQLA_member1694869]

This doesen't work:

UNLOAD select list(string(name,'\\x09'),char(09) order by column_number) from sa_describe_query('select * from #tmp')  to 'c:\\temp\\test.txt' encoding 'windows-1252' delimited by '\\x09';

Everything is on the same row.

[VolkerBarth]

Just for the record, here's a sample how to use an UNION to select column headers and the "real data" in one go - note that because of the UNION's requirement for compatible types, you will have to cast any non-string data to string, which may or may not be appropriate.

unload select type_id, type_name from
  (select 0 as row_no, 'type_id' as type_id, 'type_name' as type_name
   union all
   select row_number() over (order by type_name), cast(type_id as varchar(255)) as type_id, type_name from sys.sysusertype) dt
order by row_no
to 'C:\\MyFileWithUnion.txt' quotes on;

will return something like:

'type_id','type_name'
'114','city_t'
'110','company_name_t'
'116','country_t'
'103','datetime'
'106','image'

Note that here all values are quoted (since they are selected as strings). The ROW_NUMBER() is handy to generate row numbers which are required to preserve the row order here.

(I guess the approach with two different UNLOADs with APPEND is way easier.)