I am trying to read the results of SYSTEMINFO in a table, and then distribute each line in columns.
Visually (on CMD) I can recognize the subresults, because whitespaces are added to each subresult, but, when I 'input' the results into a table then all heading whitespaces are supressed! So, the following lines (from ssss.txt): Hotfix(es): 7 Hotfix(e) installiert. [01]: KB4533002 Are imported in the table as: Hotfix(es): 7 Hotfix(e) installiert. [01]: KB4533002 Is there a way to import the whitespaces as they are? One more question, is there a way to eliminate the need of the temporary file (ssss.txt)? so that to capture the output of a command issued by xp_cmdshell? |
If you are wanting to read the file with no changes made to its content, a simple solution would be to use xp_read_file(). Yes, I used this procedure to read the file 'binary'
(10 Jan '20, 11:23)
Baron
|
I guess INPUT ... FORMAT FIXED or the NOSTRIP clause with the default TEXT format... Update: Hm, after some tests, both options do not work as I have expected but the LOAD TABLE statement with QUOTES OFF and STRIP OFF does: drop table if exists MySysInfo; create table MySysInfo (line char(255)); load table MySysInfo from 'c:\\Temp\\SystemInfo.txt' format text quotes off strip off ; select * from MySysInfo; NOSTRIP Clause supresses trailing blank stripping. In my case I need to supress the stripping of Leading blanks. FORMAT FIXED brings totally unexpected results!
(09 Jan '20, 07:28)
Baron
Replies hidden
Thanks for the help. It has worked despite format text did not work in SQL Anywhere 10 (TEXT Format is the default format here I think). I could also solve it in another way, as reading/importing the file as binary (the whole file comes on one row), and then split it to rows using sa_split_list with '\x0D\x0A' as delimiter
(10 Jan '20, 07:08)
Baron
With v11 and above, you could also use the OpenString operator in a FROM clause to read from a file and parse it accordingly. It has options similar to the LOAD TABLE statement.
(10 Jan '20, 07:45)
Volker Barth
Ah, I see, for v10 it was still called "FORMAT ASCII" although the encoding could already be non-ASCII...
(10 Jan '20, 07:48)
Volker Barth
|