Re: How to parse data from an imported array

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"warren50" <warren50@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4CB92171-24EC-4E41-AC11-91E0BC8D04AB@xxxxxxxxxxxxxxxx
>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.


.

Relevant Pages

  • Re: Photo gallery
    ... but now I can at least show photos when a link are created. ... Instead of allowing the user from outside give you the image name, they give you the array cell number. ... // View images when the links are clicked ... Now you check first if the get variables are set or not, then you compare the values of the cells in the GET array. ...
    (alt.php)
  • Re: Photo gallery
    ... but now I can at least show photos when a link are created. ... Instead of allowing the user from outside give you the image name, they give you the array cell number. ... // View images when the links are clicked ... Now you check first if the get variables are set or not, then you compare the values of the cells in the GET array. ...
    (alt.php)
  • Re: How to display in-memory image?
    ... Each photo contains a byte array containing the jpeg images for some ... would be a good idea to do a web service call in the page handler to get ... photos one by one either. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Lost Indexing of Old HDD
    ... I need help recovering files from an ... HDD array. ... decided to make a clean install on the new raptor array. ... (favorites, .pst, photos, My Documents) ...
    (microsoft.public.windows.vista.file_management)