How to parse data from an imported array

[warren50]

I have an Access database that imports a comma-delimited text file that has a
field that contains a list of URLs that are comma seperated (the array).
Example:

stock_number,image-urls
"1234","http://photos.store.com/dcm_3.jpg,http://photos.store.com/dcm_769.jpg
"1235","http://photos.store.com/dcm_6.jpg,http://photos.store.com/dcm_0134.jpg"
"1236","http://photos.store.com/dcm_7.jpg"

The [image-urls] field could have from 1 to 100 seperate URLs depending on
how many photos were taken of the item. Once I import the data file I need to
parse the URLs into another table with an individual record for each URL and
the corresponding stock number that matches that URL. Example:

stock_number|image-url
1234|http://photos.store.com/dcm_3.jpg
1234|http://photos.store.com/dcm_769.jpg
1235|http://photos.store.com/dcm_6.jpg
1235|http://photos.store.com/dcm_0134.jpg
1236|http://photos.store.com/dcm_7.jpg

I then will export this table and use in an outside script that goes to the
URL and downloads the photo renaming it to the stock number_1.jpg, _2.jpg
etc... so that I have the photos with formatted in naming convention that is
descriptive of the image.

I can use Left([image-urls], InStr([image-urls], ",")-1) right now to get
the 1st image, but I don't know how to move on through the array to et the
remaining photos.

I want to do this all in an Access query using SQL and built in functions if
possible.

 

[Allen Browne]

This would be *very* messy to try in a query only.
You will need to write some VBA code to follow these steps:


1. Use TransferText to import the file into a temp table.

2. Write some code to OpenRecordset() into this table, and also into the
target table (to append to.)

3. Loop through the records in the source table.

4. At each record, use Split() to parse the field into an array at the
comma. (Assumes at least Access 2000.)

5. Loop through the array from Lbound to UBound, with an AddNew and Update
to create the individual records in the target table.

 

[peregenem]

This would be *very* messy to try in a query only.

SELECT
'"1234","http://photos.store.com/dcm_3.jpg,http://photos.store.com/dcm_769.jpg"'

AS test_data,
2 AS item_1_start,
INSTR(item_1_start, test_data, '","') AS item_1_end,
MID$(test_data, item_1_start, item_1_end - item_1_start) AS item_1,
item_1_end + LEN('","') AS item_2_start,
INSTR(item_2_start, test_data, '"') AS item_2_end,
MID$(test_data, item_2_start, item_2_end - item_2_start) AS item_2

 

[warren50]

I think this query will pull 2 items out of a 2 item comma seperated field,
but I'm not sure. The field i need to parse from doesn't contain a set number
of items in the list, it could from 1 to 100. How will this query be able to
work its way through the field?

 

[warren50]

I completely understand the process you have described here, but I don't know
how to implement the solution. I have an Access 2000 VBA handbook, but it's
overly complicated for my needs. Where do I insert the code with my database
and how to I trigger the code? I think within a Module i would define a
public function, but once again I need an example of how to do this.

Can you suggest I good place to start when learning how to implement VB in
Access? I took 2 years of C++ in college so i know the concepts of
programming, just don't know how to use them in Access 2000.

This would be *very* messy to try in a query only.
You will need to write some VBA code to follow these steps:


1. Use TransferText to import the file into a temp table.

2. Write some code to OpenRecordset() into this table, and also into the
target table (to append to.)

3. Loop through the records in the source table.

4. At each record, use Split() to parse the field into an array at the
comma. (Assumes at least Access 2000.)

5. Loop through the array from Lbound to UBound, with an AddNew and Update
to create the individual records in the target table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access database that imports a comma-delimited text file that has
a
field that contains a list of URLs that are comma seperated (the array).
Example:

stock_number,image-urls
"1234","http://photos.store.com/dcm_3.jpg,http://photos.store.com/dcm_769.jpg"
"1235","http://photos.store.com/dcm_6.jpg,http://photos.store.com/dcm_0134.jpg"
"1236","http://photos.store.com/dcm_7.jpg"

The [image-urls] field could have from 1 to 100 seperate URLs depending on
how many photos were taken of the item. Once I import the data file I need
to
parse the URLs into another table with an individual record for each URL
and
the corresponding stock number that matches that URL. Example:

stock_number|image-url
1234|http://photos.store.com/dcm_3.jpg
1234|http://photos.store.com/dcm_769.jpg
1235|http://photos.store.com/dcm_6.jpg
1235|http://photos.store.com/dcm_0134.jpg
1236|http://photos.store.com/dcm_7.jpg

I then will export this table and use in an outside script that goes to
the
URL and downloads the photo renaming it to the stock number_1.jpg, _2.jpg
etc... so that I have the photos with formatted in naming convention that
is
descriptive of the image.

I can use Left([image-urls], InStr([image-urls], ",")-1) right now to get
the 1st image, but I don't know how to move on through the array to et the
remaining photos.

I want to do this all in an Access query using SQL and built in functions
if
possible.

 

[peregenem]

I think this query will pull 2 items out of a 2 item comma seperated field,
but I'm not sure. The field i need to parse from doesn't contain a set number
of items in the list, it could from 1 to 100. How will this query be able to
work its way through the field?

It can't, I was just demonstrating the 'messy' point.

However, here's a much better example by my pal Celko, translated into
Access/Jet (it may be best to construct the Sequence table in Excel
<g>)

CREATE TABLE InputStrings (
keycol VARCHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL)
;
INSERT INTO InputStrings
VALUES ('first', '12,34,567,896')
;
INSERT INTO InputStrings
VALUES ('second', '312,534,997,896')
;
INSERT INTO InputStrings
VALUES ('Third', '667,841')
;
INSERT INTO InputStrings
VALUES ('Forth', '523,842,225,227,458,369')
;
CREATE TABLE Parmlist (
keycol VARCHAR(10) NOT NULL,
parm INTEGER NOT NULL)
;
CREATE TABLE Sequence (seq INTEGER NOT NULL)
;
INSERT INTO Sequence VALUES (1)
;
INSERT INTO Sequence VALUES (2)
;
INSERT INTO Sequence VALUES (3)
;
....
INSERT INTO Sequence VALUES (999)
;

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CLNG(MID$(I1.input_string, S1.seq, MIN(S2.seq) - S1.seq - 1))
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE MID$(',' & I1.input_string & ',', S1.seq, 1) =
','
AND MID$(',' & I1.input_string & ',', S2.seq, 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq
;

 

[Allen Browne]

Okay, Warren.

Start by creating a new module (modules tab of Database window).

That opens a module, where you can create the function.

From the Tools menu, choose References, and check the box beside:
Microsoft DAO 3.6 Library.
DAO is the native Access library. (This library is referenced by default in
all versions except 2000 and 2002.)

Now the following is *really* rough aircode, without validation checks, just
enough to get you into the relevant parts of the VBA help file.

Public Function DoTheImport()
dim db As DAO.Database
Dim rsSource As DAO.Recordset 'Temp source table.
Dim rsTarget As DAO.Recordset 'Output table.
Dim varLinks As Variant 'The array to be populated by Split()
Dim i As Integer 'loop controller

'Do the import
DoCmd.TransferDatabase acImport, ...

'Open target and source recordsets
Set db = CurrentDb()
Set rsTarget = db.OpenRecordSource("MyOutputTable", dbOpenDynaset,
dbAppendOnly)
Set rsSource = db.OpenRecordset("MyImportTable", dbOpenDynaset)

'Loop through all source records.
Do While Not rsSource.EOF
'Split the links into the array.
varLinks = Split(rsSource!image-urls, ",")

'Loop through the array, adding a record for each to the target
table.
For i = LBound(varLinks) to UBound(varLinks)
rsTarget.AddNew
rsTarget!stock_number = rsSource!stock_number
rsTarget!image_url = varLinks(i)
rsTarget.Update
Next
rsSource.MoveNext
Loop

'Clean up
rsSource.Close
rsTarget.Close
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I completely understand the process you have described here, but I don't
know
how to implement the solution. I have an Access 2000 VBA handbook, but
it's
overly complicated for my needs. Where do I insert the code with my
database
and how to I trigger the code? I think within a Module i would define a
public function, but once again I need an example of how to do this.

Can you suggest I good place to start when learning how to implement VB in
Access? I took 2 years of C++ in college so i know the concepts of
programming, just don't know how to use them in Access 2000.

This would be *very* messy to try in a query only.
You will need to write some VBA code to follow these steps:


1. Use TransferText to import the file into a temp table.

2. Write some code to OpenRecordset() into this table, and also into the
target table (to append to.)

3. Loop through the records in the source table.

4. At each record, use Split() to parse the field into an array at the
comma. (Assumes at least Access 2000.)

5. Loop through the array from Lbound to UBound, with an AddNew and
Update
to create the individual records in the target table.

I have an Access database that imports a comma-delimited text file that
has
a
field that contains a list of URLs that are comma seperated (the
array).
Example:

stock_number,image-urls
"1234","http://photos.store.com/dcm_3.jpg,http://photos.store.com/dcm_769.jpg"
"1235","http://photos.store.com/dcm_6.jpg,http://photos.store.com/dcm_0134.jpg"
"1236","http://photos.store.com/dcm_7.jpg"

The [image-urls] field could have from 1 to 100 seperate URLs depending
on
how many photos were taken of the item. Once I import the data file I
need
to
parse the URLs into another table with an individual record for each
URL
and
the corresponding stock number that matches that URL. Example:

stock_number|image-url
1234|http://photos.store.com/dcm_3.jpg
1234|http://photos.store.com/dcm_769.jpg
1235|http://photos.store.com/dcm_6.jpg
1235|http://photos.store.com/dcm_0134.jpg
1236|http://photos.store.com/dcm_7.jpg

I then will export this table and use in an outside script that goes to
the
URL and downloads the photo renaming it to the stock number_1.jpg,
_2.jpg
etc... so that I have the photos with formatted in naming convention
that
is
descriptive of the image.

I can use Left([image-urls], InStr([image-urls], ",")-1) right now to
get
the 1st image, but I don't know how to move on through the array to et
the
remaining photos.

I want to do this all in an Access query using SQL and built in
functions
if
possible.