input statement supresses heading whitespaces

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?

xp-cmdshell input load-table

asked 09 Jan '20, 04:47

Baron
2.1k●136●149●177
accept rate: 48%

edited 09 Jan '20, 05:34

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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 '20, 09:32

Mark Culp
24.9k●10●141●297
accept rate: 41%

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

(10 Jan '20, 11:23) Baron

flat view

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 '20, 06:54

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 09 Jan '20, 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 '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

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 '20, 07:48) Volker Barth

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

load-table ×18
xp-cmdshell ×8
input ×6

question asked: 09 Jan '20, 04:47

question was seen: 1,027 times

last updated: 10 Jan '20, 11:23

SAP SQL Anywhere

input statement supresses heading whitespaces

Follow this question

Related questions