#1
  1. No Profile Picture
    Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Indianapolis, USA
    Posts
    3,559
    Rep Power
    697

    Dynamic Dealer Feed


    Dealers that are registered with my website are able to upload a delimited text document with vehicle information in the document. These documents must all be delimited by the | character. The data can come in these files in any order. For instance, one dealer might have the following structure:

    Field 1: ID
    Field 2: Vehicle Year
    Field 3: Vehicle Make
    Field 4: Description

    While another dealer feed might have the following:

    Field 1: Vehicle Year
    Field 2: Description
    Field 3: Vehicle Make
    Field 4: Fuel Type

    In my tbl_Vehicles table, I have specific fields:

    vm_id (Primary key for vehicle make -- tbl_Vehicle_make)
    v_year
    v_description
    vc_id (Primary ket for vehicle condition -- tbl_Vehicle_condition)

    There are many more, but you get the point.

    I have created a links table for the dealer field. The links table tells me what field in the text file matches up with what field in the DB. For the first dealer example, the link table would look something like this:

    Field 1: ID = v_id
    Field 2: Vehicle Year = v_year
    Field 3: Vehicle Make = vm_id
    Field 4: Fuel Type = v_fuel_type

    I am having to loop through each line in the text delimited file, look up in the link table to figure out which field it belongs to, and then create the SQL statement. There are loops inside of loops that is causing the page to run VERY slow and generally timeout.

    Does anyone have any better ideas how to go about this?
    "You'll never be as perfect as BLaaaaaaaaarche."
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,594
    Rep Power
    278
    If you have test delimited file, you do not need loop on each line to map data. Loop just first line and create mapping and load data after that
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Indianapolis, USA
    Posts
    3,559
    Rep Power
    697
    Each dealer feed will be different, so each will have its own set of mapping information. I'm not following what you are saying. I need to loop through each line of data to insert it into the DB. How do you suggest I create my mapping without having to loop through the link table for each line?
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,594
    Rep Power
    278
    delimited file means all lines inside the file in the same data order like you said
    Field 1: ID
    Field 2: Vehicle Year
    Field 3: Vehicle Make
    Field 4: Description

    While another dealer feed might have the following:

    Field 1: Vehicle Year
    Field 2: Description
    Field 3: Vehicle Make
    Field 4: Fuel Type

    but line 1 in file 1 will be in the same order as line 2, line 3, etc. in file 1 right?
    So you need just find in what order fields in current file from line 1 and load the rest of the data from that file...
    and no nested loops...
  8. #5
  9. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    Blarche,

    I assume you are using the FileSystemObject/TextStreamObject to read the file one line at a time. I also assume you capture enough info on initial file upload to identify which links table to use to translate that particular dealer's file layout into you database layout. I am also assuming the links table is an actual table in the database.

    I think KIS or Shadow would probably have a better way, but I would be real tempted to insert the incoming data in a temp table in the database as is (in the supplied order). Then instead of a links table to translate I would have a query.

    So given data like:
    main_table
    Code:
    one	two		three	four
    1	apple		www	123
    2	banana		eee	234
    3	cherry		rrr	345
    4	pear		ttt	567
    5	strawberry	uuu	789
    I read in the incoming table from a dealer:
    incoming_table
    Code:
    chort	veeble	snertz	winkle
    ppp	908	potato	7
    kkk	909	carrot	8
    lll	907	celery	9
    iii	906	radish	10
    mmm	905	beet	11
    Then instead of a table translating for that dealer, I know for that dealer I need to use a query like:
    dealer_1_query
    Code:
    INSERT INTO main_table (one, two, three, four)
    select winkle, snertz, chort, veeble from incoming_table
    The order of fields in the sub-select of the insert does the translation putting the values in the right place in main_table.

    which results in:
    main_table
    Code:
    one	two		three	four
    1	apple		www	123
    2	banana		eee	234
    3	cherry		rrr	345
    4	pear		ttt	567
    5	strawberry	uuu	789
    7	potato		ppp	908
    8	carrot		kkk	909
    9	celery		lll	907
    10	radish		iii	906
    11	beet		mmm	905
    Read the uploaded text file.
    Save as incoming_table in the database.
    Execute the dealer_x query based on who uploaded to load the main_table.
    Delete the records in incoming_table.

    Just an idea.

    Edit: This assumes, of course, the dealer stays with the same file layout for each upload and that you know that layout. If that isn't the case, maybe you could build an interface where they specify the layout at upload and then you could capture that to make a dynamic insert query for the uplaoded table. But that gets in the weeds fast and I don't know all your details so I will stop.
    Last edited by bigmike1212; November 29th, 2012 at 12:44 PM.
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  10. #6
  11. No Profile Picture
    Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Indianapolis, USA
    Posts
    3,559
    Rep Power
    697
    Thanks for the reply, bigmike. All of your assumptions are correct. I have a temp table that I also created. I tried that route, but couldn't get it to work. However, my data also resides in the temp table. The temp table has columns named "df_t_field1" through "df_t_field30". I store the data exactly as it comes in from the text file. To better help understand, I have provided my DB structure for the vehicles table. This is the end table that everything gets stored into:



    The below table, is the links table. This is my config table for each dealer. It tells me which field in the text fiel (or temp DB) ties back to which field in the vehicles table above. I'll provide another table below that is the setup table to link the config table with the vehicles table:



    I also created another table to as a field lookup. This tells the feed what the field name is in the vehicles table (df_v_field_id), what the primary key of the field is (df_f_field_id), what the name of the field storing the value is (df_f_field_name), what the datatype of the field is (df_f_datatype), and what the lookup table of the field is in (df_f_table). The reason I do this, is so that I get consisent data. For instance, I have a vehicle make table (tbl_Vehicles_make). The dealer feed will submit the vehicle make as 'FORD'. I need to convert this to the numeric value in the vehicle make table to store into the vehicles table. The vehicle make ID for 'FORD" is 2. "2" will get stored into the vehicles table in the "vm_id" field. However, in order to do this, I need to convert from text to numeric by looking up "FORD" in the vehicle make table.



    Below is my code to try and loop through the tables:

    Code:
    ' -- Create Fields For SELECT --
     For x = 1 To 30
         strDBFields = strDBFields & "df_t_field" & x & ", "
     Next
      
      
     ' -- Insert Records Into Main Vehicle Table --
     strSQL = "SELECT " & strDBFields & "df_t_id FROM tbl_DealerFeed_temp WHERE df_d_id = " & df_d_id
     Set objRS = siteConn.Execute(strSQL)
     If Not objRS.EOF Then
         Do While Not objRS.EOF
             strSQL = "SELECT df.df_v_field_id, df.df_f_field_id, df.df_f_field_name, df.df_f_description, df.df_f_datatype, " & _
                 "df.df_f_table, dfl.df_l_field_id " & _
                 "FROM tbl_DealerFeed_link dfl " & _
                 "LEFT JOIN tbl_DealerFeed_fields df ON df.df_f_id = dfl.df_f_id " & _
                 "WHERE dfl.df_d_id = " & df_d_id
             Set objField = siteConn.Execute(strSQL)
             If Not objField.EOF Then
                 Do While Not objField.EOF
                     ' -- Grab DB Field ID --
                     intDBFieldID = objField("df_l_field_id")
                     strDBValue = objRS("df_t_field" & intDBFieldID)
                     strVehicleFieldID = objField("df_v_field_id")
                     strFieldID = objField("df_f_field_id")
                     strFieldName = objField("df_f_field_name")
                     strDataType = objField("df_f_datatype")
      
      
                     ' -- Check For Lookup Value --
                     If Not CheckBlank(objField("df_f_field_name")) Then
                         strNewValue = GrabCatID(strDBValue, strFieldID, objField("df_f_field_name"), objField("df_f_table"))
                     Else
                         strNewValue = strDBValue
                     End If
      
      
                     If Not CheckBlank(strNewValue) Then
                         ' -- Check Image Field --
                         If strDataType = "image" Then
                             strImages = strNewValue
                         Else
                             ' -- Store Field Values --
                             If Not CheckBlank(strFields) Then strFields = strFields & ","
                             strFields = strFields & strVehicleFieldID
                             If Not CheckBlank(strValues) Then strValues = strValues & ","
                             If strDataType <> "int" Then
                                 strValues = strValues & "'" & ReplaceVars(strNewValue) & "'"
                             Else
                                 strValues = strValues & ReplaceVars(strNewValue)
                             End If
                         End If
                     End If
                 objField.MoveNext
                 Loop
      
      
                 ' -- Create SQL --
                 If Not CheckBlank(strFields) And Not CheckBlank(strValues) Then
                     strSQL = "INSERT INTO tbl_Vehicles (" & strFields & ") VALUES (" & strValues & ")"
                     response.write strSQL & "<p>"
                 End If
      
      
                 ' -- Upload Images --
                 If Not CheckBlank(strImages) Then
                     If InStr(strImages, ",") > 0 Then
                         arrImages = Split(strImages, ",")
                     Else
                         arrImages = strImages
                     End If
                     If IsArray(arrImages) Then
                         For x = 0 To UBound(arrImages)
                             ' -- Create Primary --
                             v_p_primary = 0
                             If x = 0 Then
                                 v_p_primary = 1
                             End If
      
      
                             ' -- Create Array of Fields --
                             arrFields = Array("int_v_id","v_p_name","int_v_p_primary","int_v_p_resized","int_v_p_external_link")
                             arrValues = Array(v_id,arrImages(x),v_p_primary,0,1)
                             strSQL = SubmitForm(arrFields, arrValues, "ADD", "tbl_Vehicles", "", "")
                             response.write strSQL & "<br>"
                         Next
                     End If
                 End If
      
      
                 ' -- Clear Values --
                 strFields = ""
                 strValues = ""
                 strImages = ""
             End If
         objRS.MoveNext
         Loop
     End If
      
      
     ' -- Grab Vehicle Category ID --
     Function GrabCatID(strValue, strFieldID, strFieldName, strTable)
         ' -- Create Recordset --
         strSQL = "SELECT " & strFieldID & " FROM " & strTable & " WHERE " & strFieldName & " = '" & strValue & "'"
         Set objFieldID = siteConn.Execute(strSQL)
         If Not objFieldID.EOF Then
             GrabCatID = objFieldID(strFieldID)
         End If
         objFieldID.Close
         Set objFieldID = Nothing
     End Function
  12. #7
  13. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,594
    Rep Power
    278
    you are on SQL server, Why not use SQL server as it should...
    create stored procedure on SQL server which will load data from temp table to destination table.
    Load data into temp table from asp page, after that just call your stored procedure.
    Stored procedure will do all SQL data manipulation much faster...
    you can set translation tables and you will not need to translate things like "'FORD'. I need to convert this to the numeric value in the vehicle make table to store into the vehicles table"
  14. #8
  15. No Profile Picture
    Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Indianapolis, USA
    Posts
    3,559
    Rep Power
    697
    I thought of using a stored procedure, but then had no idea how to link everything together dynamically. Do you have any ideas?
  16. #9
  17. No Profile Picture
    Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Indianapolis, USA
    Posts
    3,559
    Rep Power
    697
    I'm still having some struggles with this. Would anyone be able to help further?

Similar Threads

  1. Ex-Chrysler dealer in Mo. sues for reinstatement (AP)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: June 22nd, 2010, 03:00 PM
  2. Ex-Chrysler dealer in Mo. sues for reinstatement (AP)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: June 22nd, 2010, 12:00 PM
  3. Chrysler says it won't sue over dealer arbitration (AP)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: January 28th, 2010, 03:04 PM
  4. Chrysler says it won't sue over dealer arbitration (AP)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: January 28th, 2010, 02:04 PM
  5. Dynamic Form Fields - Dynamic Database Addition
    By sunny99 in forum ASP Development
    Replies: 1
    Last Post: May 13th, 2009, 01:58 PM

IMN logo majestic logo threadwatch logo seochat tools logo