Trying to export data from a .csv file to a database table

[ericnet]

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

 

[ericnet]

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?