I know this is probably easy for you, but what I am trying to do is do the
following:
I have 120 computers to support and I have generated the following type text
file recording some inventory stuff for each pc.
pc1.txt
Computer Name, PC01
IP Address, 144.5.2.1
Operating System, Microsoft Windows XP Home Edition 5.1.2600
etc etc
pc2.txt
Computer Name, PC02
IP Address, 144.5.2.2
Operating System, Microsoft Windows XP Home Edition 5.1.2600
etc etc
What I would like to do is:
Import these text files into a sql table with setup like:
Computer Name IP Address Operating System
PC01 144.5.2.1 Microsoft Windows XP Home Edition
5.1.2600
PC02 144.5.2.2 Microsoft Windows XP Home Edition
5.1.2600
etc etc
One row in a table for each computer.
How can you convert the rows in each text file to be a single row in a sql
table?
Can anyone steer me in the right direction here as to the best way to do
this?
I would really appreciate it.
--
---------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.comMichelle Hillard (mhillard@.craized.tv) writes:
> I have 120 computers to support and I have generated the following type
> text file recording some inventory stuff for each pc.
> pc1.txt
> Computer Name, PC01
> IP Address, 144.5.2.1
> Operating System, Microsoft Windows XP Home Edition 5.1.2600
> etc etc
> pc2.txt
> Computer Name, PC02
> IP Address, 144.5.2.2
> Operating System, Microsoft Windows XP Home Edition 5.1.2600
> etc etc
> What I would like to do is:
> Import these text files into a sql table with setup like:
>...
> One row in a table for each computer.
> How can you convert the rows in each text file to be a single row in a sql
> table?
First make the table wide enough to hold also the labels.
Next copy all files into one:
copy pc*.txt all_pcs.txt
(You may have to play around to get the exact syntax right.)
Then you can use BCP from the command prompt:
bcp db..tbl in all_pcs.txt -T -c -r\n -t\n
Then run this:
UPDATE tbl
SET computer_name = replace('Computer Name, ', ''),
ip_address = replace('IP Address, ', ''),
os = replace('Operating system, ', '')
Note that for the BCP to work there must not be any extra newlines
somewhere.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Michelle,
> I have 120 computers to support and I have generated the
> following type text file recording some inventory stuff for each
> pc.
> pc1.txt
> Computer Name, PC01
> IP Address, 144.5.2.1
> Operating System, Microsoft Windows XP Home Edition 5.1.2600
> etc etc
> What I would like to do is:
> Import these text files into a sql table with setup like:
> One row in a table for each computer.
> How can you convert the rows in each text file to be a single row
> in a sql table?
You can use the bcp command line utility to import your data.
Given your sample data and a table that looks like this:
create table pc (
computer_name varchar(8),
ip_address varchar(16),
operating_system varchar(60)
)
You can load and reformat yur data using a bcp format that looks
like this:
8.0
6
1 SQLCHAR 0 20 ", " 0 filler_1 ""
2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
3 SQLCHAR 0 20 ", " 0 filler_2 ""
4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
5 SQLCHAR 0 20 ", " 0 filler_3 ""
6 SQLCHAR 0 60 "\r\n" 3 operating_system ""
Save the bcp format file in the same directory as your data files.
Then open a command prompt window and run bcp in a loop to load all
files:
C:\Staging>for /F %i in ('dir /b /on pc*.txt') do bcp zap..pc in
%i -fpc.bcp -T -S.
Linda|||Erland, thank you so much for your help.
Will this convert the rows to a single row for each pc text file?
For example:
pc1.txt
Computer Name,
144.5.2.1,
Windows NT,
So that this becomes:
Computer Name, 144.5.2.1, Windows NT, in a sql table
Or,
Do I first have to somehow (god knows how), convert the text file into:
Computer Name, 144.5.2.1, Windows NT, in a sql table?
How does bcp know to do this?
Pardon my ignorance, and thank you so much for your help.
Simon
--
---------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94465F047A12Yazorman@.127.0.0.1...
> Michelle Hillard (mhillard@.craized.tv) writes:
> > I have 120 computers to support and I have generated the following type
> > text file recording some inventory stuff for each pc.
> > pc1.txt
> > Computer Name, PC01
> > IP Address, 144.5.2.1
> > Operating System, Microsoft Windows XP Home Edition 5.1.2600
> > etc etc
> > pc2.txt
> > Computer Name, PC02
> > IP Address, 144.5.2.2
> > Operating System, Microsoft Windows XP Home Edition 5.1.2600
> > etc etc
> > What I would like to do is:
> > Import these text files into a sql table with setup like:
> >...
> > One row in a table for each computer.
> > How can you convert the rows in each text file to be a single row in a
sql
> > table?
> First make the table wide enough to hold also the labels.
> Next copy all files into one:
> copy pc*.txt all_pcs.txt
> (You may have to play around to get the exact syntax right.)
> Then you can use BCP from the command prompt:
> bcp db..tbl in all_pcs.txt -T -c -r\n -t\n
> Then run this:
> UPDATE tbl
> SET computer_name = replace('Computer Name, ', ''),
> ip_address = replace('IP Address, ', ''),
> os = replace('Operating system, ', '')
> Note that for the BCP to work there must not be any extra newlines
> somewhere.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linda,
Thank you so much. Would you mind if I clarify something with you.
Let's just say I have 200 text files that have been uploaded to C:>Staging
pc1.txt
pc2.txt
etc etc
In format:
PC01,
144.5.2.1,
Windows NT,
etc etc
Correct me if Im wrong here please:
1) Create the table that will eventually hold all data:
> create table pc (
> computer_name varchar(8),
> ip_address varchar(16),
> operating_system varchar(60)
2)BEFORE importing into this table, I have to reformat all the pc*.txt files
like:
> 8.0
> 6
> 1 SQLCHAR 0 20 ", " 0 filler_1 ""
> 2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
> 3 SQLCHAR 0 20 ", " 0 filler_2 ""
> 4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
> 5 SQLCHAR 0 20 ", " 0 filler_3 ""
> 6 SQLCHAR 0 60 "\r\n" 3 operating_system ""
Linda, pardon my ignorance, do I simply do the above in notepad, what do I
save it as?
Is this the bcp formatting file?
3)Where in the below is this formatting file referenced? Im sorry you are so
more advanced than myself, but I am wanting to learn.
C:\Staging>for /F %i in ('dir /b /on pc*.txt') do bcp zap..pc in
%i -fpc.bcp -T -S.
Thank you so much in advance Linda.
--
---------------------
"Are you still wasting your time with spam?...
There is a solution!"
Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com
"lindawie" <lindawie@.my-deja.com> wrote in message
news:%23O33i%23TuDHA.3220@.tk2msftngp13.phx.gbl...
> Michelle,
> > I have 120 computers to support and I have generated the
> > following type text file recording some inventory stuff for each
> > pc.
> > pc1.txt
> > Computer Name, PC01
> > IP Address, 144.5.2.1
> > Operating System, Microsoft Windows XP Home Edition 5.1.2600
> > etc etc
> > What I would like to do is:
> > Import these text files into a sql table with setup like:
> > One row in a table for each computer.
> > How can you convert the rows in each text file to be a single row
> > in a sql table?
> You can use the bcp command line utility to import your data.
> Given your sample data and a table that looks like this:
> create table pc (
> computer_name varchar(8),
> ip_address varchar(16),
> operating_system varchar(60)
> )
> You can load and reformat yur data using a bcp format that looks
> like this:
> 8.0
> 6
> 1 SQLCHAR 0 20 ", " 0 filler_1 ""
> 2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
> 3 SQLCHAR 0 20 ", " 0 filler_2 ""
> 4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
> 5 SQLCHAR 0 20 ", " 0 filler_3 ""
> 6 SQLCHAR 0 60 "\r\n" 3 operating_system ""
>
> Save the bcp format file in the same directory as your data files.
> Then open a command prompt window and run bcp in a loop to load all
> files:
> C:\Staging>for /F %i in ('dir /b /on pc*.txt') do bcp zap..pc in
> %i -fpc.bcp -T -S.
>
> Linda
>|||Michelle Hillard (mhillard@.craized.tv) writes:
> 2)BEFORE importing into this table, I have to reformat all the pc*.txt
> files like:
>> 8.0
>> 6
>> 1 SQLCHAR 0 20 ", " 0 filler_1 ""
>> 2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
>> 3 SQLCHAR 0 20 ", " 0 filler_2 ""
>> 4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
>> 5 SQLCHAR 0 20 ", " 0 filler_3 ""
>> 6 SQLCHAR 0 60 "\r\n" 3 operating_system ""
No. Linda's solution is absolutely brilliant, because this format files
describes the format of the file you posted.
Of course, if these files were entered by hand, they might all conform
to this exact format, and in this case, you need to rectify files that
for instance misses a comma, or have a space too much.
I suggested in my posting that you would need to concatenate the files
into one. I would probably do this if I did this myself, but I see that
Linda has a suggestion for this too:
> 3)Where in the below is this formatting file referenced? Im sorry you
> are so more advanced than myself, but I am wanting to learn.
> C:\Staging>for /F %i in ('dir /b /on pc*.txt') do bcp zap..pc in
> %i -fpc.bcp -T -S.
That's the -f option.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment