cancel
Showing results for 
Search instead for 
Did you mean: 

input statement supresses heading whitespaces

Baron
Participant
0 Kudos
1,369

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?

Accepted Solutions (0)

Answers (2)

Answers (2)

MarkCulp
Participant

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

Baron
Participant
0 Kudos

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

VolkerBarth
Contributor

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;
Baron
Participant
0 Kudos

NOSTRIP Clause supresses trailing blank stripping.

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

FORMAT FIXED brings totally unexpected results!

Baron
Participant
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

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