Transfer Text to a Table 1

[Andel]

Here's my Data and I want it to put into a table with the following 5 columns: Drive, Filesys, Size, Free, Used.

Drive: [FileSys] [ Size ] [ Free ] [ Used ]
C$ NTFS 4095 1087 3008
D$ NTFS 4754 4246 508
E$ NTFS 8190 6717 1473
F$ NTFS 2048 720 1328

Any help/recommendations will be appreciated.

Thanks,

Andel



Andel
andel@barroga.net

http://andyb.8m.com

 

[gvphubli]

if its a text file then use DTS to import that file into SQL Database..else Create table and write insert statement to insert data...if this don't give soulution..post the question more clearly..

Cracky -= Developer/DBA =-
visti:

http://www.geocities.com/gvphubli

 

[Andel]

This data is already in SQL but in 1 column only with 5 rows. The column name is "DriveInfo" column. I want to break this 1 column into 5. Say "Drive" is the first column with the value of C$, D$, E$, and F$. The second column would be "FileSys", and so on...

The table that I would like to be is like this:

Drive varchar(5)
Filesys varchar(10)
Size int
Free int
Used int


Thanks again..

Andel








Andel
andel@barroga.net

http://andyb.8m.com

 

[SQLSister]

SInce you said the table you want to conver has only 5 rows. First step create teh new table structure
Step two type the data into the table using enterprise Manager
Why go through the pain of creating a conversion process when it will take a minute at most to type this data in?

 

[Andel]

I wouldn't post the question here if it's that simple. The problem here is that I have 500+ SQL servers. The data I showed here is just a sample data.

I have a cmd utility that gathers drive information such as Drive letter, Size, Free space, and used size. I can then get the result of the utility into SQL. The problem is that it only give me 1 column.

I can't use "xp_fixeddrives" because this only gives the free space.

Is there any other way I can get the data I want?

Thanks,

Andel


Andel
andel@barroga.net

http://andyb.8m.com

 

[SQLSister]

Yes you can filter this data if you have a way to tell when one set of data begins and one ends, what diveds the data - spaces, commas?

or is it set up so each data field is fixed length and thus you only need to count to a particular number to see where the data for a field starts?

Once we now the structure that needs to be parsed it can be parsed

Assume each begins at a specific place

Insert into table1 (col1, col2, Col3, col4, col5)
Select left(Field1, 2), substring (field1, 3, 4), substring (field1, 7,4), substring (field1, 11, 4), substring (field1, 15, 4) from table 2

If they are separated by a comma or space then you would use the charindex or pathindex function to find where to start the substring and maybe where to end it.

 

[Andel]

Thanks SQLSister,

Yes, I can use substring, charindex or pathindex. The only problem though is that there's no fix lenght. The delimeter is a combination of tabs and spaces. I can replace the tab with spaces using the replace command but again the number of spaces varies. Is there a way I can replace x number of spaces (# of spaces varies) into a single space only? If there's a way, then I can use substring, charindex or pathindex.



Andel
andel@barroga.net

http://andyb.8m.com

 

[SQLSister]

Ugh! I had to do something like this once in a WOrd document. I would say replace all the spaces with tabs or vice versa first. Then do a replace to replace two tabs or two spaces with one and iterate through the record until you only have one left. This will prbably call for some kind of a where loop or cursor. Then run the insert statemetn using the char index to come up with the locations for the substings to start and stop. I'll think on it some more and try to come up with a way to avoid the looping, but right now I can't think of a way. Good luck. I'll never understand why the people who program these systems (telephone switch data is particulary bad in this respect) never seem to want to put it in a format that is simple to parse to actually use the data. I swear it never occurs to them that people might want to use the data they collect.

 

[Andel]

Very smart idea SQLSister. Here's what I did...

while 1=1
begin
update DiskInfo
set OutputValue = replace(outputvalue, ' ', ' ')
if (select count(1) from DiskInfo where OutputValue like '% %') = 0
break
end





Andel
andel@barroga.net

http://andyb.8m.com