#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep 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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep 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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    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!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep 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 08:09 AM.

IMN logo majestic logo threadwatch logo seochat tools logo