Dynamic Dealer Feed

[BLarche]

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?

[gk53]

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

[BLarche]

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?

[gk53]

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...

[bigmike1212]

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

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

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

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

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.

[BLarche]

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:

' -- 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

[gk53]

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"

[BLarche]

I thought of using a stored procedure, but then had no idea how to link everything together dynamically. Do you have any ideas?

[BLarche]

I'm still having some struggles with this. Would anyone be able to help further?