Quote string values before export with xp_write_file

[Former Member]

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 Member · 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 Member · 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 Member · 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 Member · 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 Member · 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 Member · 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...?