[andreandre]

Hi guys,

I have a .csv file wich has the following information (exactly like this):


List Name: User
Format Version:1.2.5.0
Date:12/11/2012 12:00:34
Equipment.:L6867000214
IP:172.21.12.62

User,Name,Total,B&W
[1600],[technical],76,76
[6611],[Daise],559,559
[900301],[Richard],0,0


I want to execute a create table and a bulk insert transaction but I don't know how to insert the information of the top lines of the .csv file like "Equipment.:L6867000214" and IP:172.21.12.62". I need to create a collumn called Equipment", other collumn called "IP" and insert this information (repeat "L6867000214" and "172.21.12.62" in all records bulk inserted). The result must be like this:

IP,Equipment.,User,Name,Total,B&W, Color
172.21.12.62,L6867000214,[1600],[technical],76,76
172.21.12.62,L6867000214,[6611],[Daise],559,559
172.21.12.62,L6867000214,[900301],[Richard],0,0

I have no ideia how to do that. Could anyone here help me out on this issue?

Thanks a lot!

[gk53]

load in temp tables data as is. After you will have
table Settings
param, value
List Name: User
Format Version:1.2.5.0
Date:12/11/2012 12:00:34
Equipment.:L6867000214
IP:172.21.12.62

table Details
User,Name,Total,B&W
[1600],[technical],76,76
[6611],[Daise],559,559
[900301],[Richard],0,0


after that run query

insert into destTable
select
(select value from Settings where param ='Equipment.:') as Equipment,
(select value from Settings where param ='IP:') as IP,
User,Name,Total,[B&W]
from Details

I think it is clear

[andreandre]

Originally Posted by gk53

load in temp tables data as is. After you will have
table Settings
param, value
List Name: User
Format Version:1.2.5.0
Date:12/11/2012 12:00:34
Equipment.:L6867000214
IP:172.21.12.62

table Details
User,Name,Total,B&W
[1600],[technical],76,76
[6611],[Daise],559,559
[900301],[Richard],0,0


after that run query

insert into destTable
select
(select value from Settings where param ='Equipment.:') as Equipment,
(select value from Settings where param ='IP:') as IP,
User,Name,Total,[B&W]
from Details

I think it is clear

Hi gk53,

Thank you for your response!

What is the best way to load this data in order to apply that select?

Thanks!

[gk53]

I think it is pretty simple
I assume you know how to read file line by line and how tomake database connection, after that open empty recordset like

set rsSettings = server.createObject("adodb.recordset")
sqlSettings = "select * from Settings where 1=0"
rsSettings.open rsSettings, connection, 3,3

read one like from file
split by first ":"
so if you read line into SettingsLine
you need loop thru your first file and run code below for each line

rsSettings.addNew
rsSettings("param")= mid(SettingsLine,1,instr(SettingsLine,":"))
rsSettings("value")= mid(SettingsLine,instr(SettingsLine,":") + 1)
rsSettings.Update

second file split by "," and do the similar code

this is asp solution, but I do not know what you have and what environment you in