Trying to export data from a .csv file to a DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to export data from a .csv file to a DB

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2006-09-29 : 03:37:46

Hello,

I’ m trying to use an Insert script to export data from a .csv file to a database table, but I receive errors in Query Analyzer saying that the specified column names of .csv file aren’ t valid, but the column names are correct.. This is the script:


Use market
GO
INSERT INTO [SystemPerformance] ( 
     [DateTimeCounter] 
     ,[Total_ProcessorTime]
     ,[Total_processorQueueLength]
     ,[AvailableMbytes]
     ,[Pages/second]
     ,[Avg. Disk Queue Length]
     ,[Avg. Disk sec/Transfer]
     ,[Disk Time]
     ,[Network Bytes Received/sec] 
     ,[Network Bytes Sent/sec] 
     ,[Network Bytes Total/sec] 
     ,[Server Bytes Received/sec]
     ,[Server Bytes Transmitted/sec] 
     ,[Server Bytes Total/sec] 
) 
SELECT 
     [(PDH-CSV 4#0) (Pacific Standard Time)(480)]
     ,cast([\Processor(_Total)\% Processor Time] as float) 
     ,cast([\System\Processor Queue Length] as float) 
     ,cast([\Memory\Available Mbytes] as float) 
     ,cast([\Memory\Pages/sec#] as float) 
     ,cast([\PhysicalDisk(_Total)\Avg# Disk Queue Length] as float) 
     ,cast([\PhysicalDisk(_Total)\Avg# Disk sec/Transfer] as float) 
     ,cast([\Physical Disk(_Total)\% Disk Time] as float) 
     ,cast([\Network Interface(Broadcom NetXtreme Gigabit Ethernet Driver)\Bytes Received/sec #] as float) 
     ,cast([\Network Interface(Broadcom NetXtreme Gigabit Ethernet Driver)\Bytes Sent/sec#] as float) 
     ,cast([\Network Interface(Broadcom NetXtreme Gigabit Ethernet Driver)\Bytes Total/sec#] as float) 
     ,cast([\Server\Bytes Received/sec#] as float) 
     ,cast([\Server\Bytes Transmitted/sec#] as float) 
     ,cast([\Server\Bytes Total/sec#] as float) 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
-- csv file on a local drive 
'Data Source=C:\PerfLogs;Extended properties=Text')...System_000002#csv

Which can be the problem?

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 03:51:01

Try to change to OpenRowSet instead, where you can state "Hdr=yes".

Peter Larsson
Helsingborg, Sweden

cesark
Posting Yak Master

215 Posts

Posted - 2006-09-29 : 04:00:37

Could you write a little example with my code please?
I don' t understand which parameter of OPENROWSET is "Hdr=yes"..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 04:07:00

Try either of these two methods

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Text;IMEX=1;HDR=yes;Database=\\seludt2135\c$\documents and settings\selupln\desktop\', 'select * from test.txt')

BULK INSERT #Temp
   FROM 'c:\documents and settings\selupln\desktop\test.txt'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '\n'
      )

There is a help file called BOOKS ONLINE if you want to know more about the syntax and options about these two commands.

Peter Larsson
Helsingborg, Sweden

cesark
Posting Yak Master

215 Posts

Posted - 2006-09-29 : 05:31:15

I found the Insert script in this article http://www.sql-server-performance.com/ss_performance_monitoring.asp and seems a good script and technique, so the solution must be very simple.. I suppose that only changing a bit something

Please, somebody can suggest other possible solutions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 05:47:52

Write a service using WMI or performance counters that inserts the result and data to your table directly.

Peter Larsson
Helsingborg, Sweden

cesark
Posting Yak Master

215 Posts

Posted - 2006-09-29 : 07:12:12

I see one interesting thing, when I execute my insert statement returns errors for all columns of .csv file except for the first column. So, seems that the script recognizes only first column (whichever is), this is my .csv file:

"(PDH-CSV 4.0) (Pacific Standard Time)(480)","\Processor(_Total)\% Processor Time","\System\Processor Queue Length",.....

So, it only can read "(PDH-CSV 4.0) (Pacific Standard Time)(480)", but not the others.
If I change the order and I put another column in the first position, for example thus:

"\Processor(_Total)\% Processor Time","(PDH-CSV 4.0) (Pacific Standard Time)(480)","\System\Processor Queue Length","\Memory\Available Mbytes","\Memory\Pages/sec.".....

Then, the column which can read is "\Processor(_Total)\% Processor Time", but not the others.

Why my insert statement only can read the first column of .csv file? And it doesn’ t recognize the others saying that the column names are not valid?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources