December 16th, 2012, 02:08 PM
-
Create Table + Bulk Insert
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!
December 21st, 2012, 02:37 PM
-
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
Code:
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
December 23rd, 2012, 09:51 PM
-
Create Table + Bulk Insert
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
Code:
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!
December 24th, 2012, 08:06 AM
-
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
Code:
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
Code:
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
Last edited by gk53; December 24th, 2012 at 08:09 AM.