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?

asked 09 Jan, 04:47

Sako's gravatar image

Sako
1.0k405595
accept rate: 25%

edited 09 Jan, 05:34


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

permanent link

answered 10 Jan, 09:32

Mark%20Culp's gravatar image

Mark Culp
24.4k9137289
accept rate: 40%

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

(10 Jan, 11:23) Sako

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;
permanent link

answered 09 Jan, 06:54

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

edited 09 Jan, 08:16

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, 07:28) Sako
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, 07:08) Sako

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, 07:45) Volker Barth

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

(10 Jan, 07:48) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17
×8
×6

question asked: 09 Jan, 04:47

question was seen: 176 times

last updated: 10 Jan, 11:23