Create Table + Bulk Insert

Dev Shed Forums Sponsor:

December 16th, 2012, 03:08 PM

andreandre

Registered User

Join Date: Dec 2012

Posts: 2

Time spent in forums: 26 m 4 sec
Reputation Power: 0

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, 03:37 PM

gk53

Contributing User

Join Date: Oct 2012

Posts: 92

Time spent in forums: 11 h 29 m 41 sec
Reputation Power: 9

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, 10:51 PM

andreandre

Registered User

Join Date: Dec 2012

Posts: 2

Time spent in forums: 26 m 4 sec
Reputation Power: 0

Create Table + Bulk Insert

Quote:

Originally Posted by gk53

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, 09:06 AM

gk53

Contributing User

Join Date: Oct 2012

Posts: 92

Time spent in forums: 11 h 29 m 41 sec
Reputation Power: 9

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 09:09 AM.

Create Table + Bulk Insert

Developer Shed Advertisers and Affiliates