Question Re: input statement supresses heading whitespaces in Technology Q&A

input statement supresses heading whitespaces

Baron — Thu, 09 Jan 2020 04:47:20 GMT

I am trying to read the results of SYSTEMINFO in a table, and then distribute each line in columns.

xp_cmdshell 'systeminfo > c:\\temp\\ssss.txt';
input into mytest from 'c:\\temp\\ssss.txt';

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?

Re: input statement supresses heading whitespaces

VolkerBarth — Thu, 09 Jan 2020 06:54:44 GMT

Is there a way to import the whitespaces as they are?

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;

Re: input statement supresses heading whitespaces

Baron — Thu, 09 Jan 2020 07:28:18 GMT

NOSTRIP Clause supresses trailing blank stripping.

In my case I need to supress the stripping of Leading blanks.

FORMAT FIXED brings totally unexpected results!

Re: input statement supresses heading whitespaces

Baron — Fri, 10 Jan 2020 07:08:45 GMT

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\ ' as delimiter

Re: input statement supresses heading whitespaces

VolkerBarth — Fri, 10 Jan 2020 07:45:45 GMT

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.

Re: input statement supresses heading whitespaces

VolkerBarth — Fri, 10 Jan 2020 07:48:02 GMT

It has worked despite format text did not work in SQL Anywhere 10 (TEXT Format is the default format here I think).

Ah, I see, for v10 it was still called "FORMAT ASCII" although the encoding could already be non-ASCII...

Re: input statement supresses heading whitespaces

MarkCulp — Fri, 10 Jan 2020 09:32:58 GMT

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().

Re: input statement supresses heading whitespaces

Baron — Fri, 10 Jan 2020 11:23:15 GMT

Yes, I used this procedure to read the file 'binary'