Project Detail

Description

You are bidding on 2 excel data formatting automation spreadsheets. I think these can be done with excel macros and visual basic, but that’s beyond my skills. I have some complicated data formatting that needs to be done on csv files I download from our website. I hope to automate it so I don’t have to do so much copy and pasting. These are reformatting csv files I export from our wig sales website with volusion shopping cart, so I can upload the files back to the database. I need to format them as follows:

1st spreadsheet (see attached files 1.xls, 2.txt, 3.xls)
I download columns productcode, catID, optionid, optiondesc (see 1.xls)
Column k is just a copy of optionid with “-“ instead of “/” to fit the jpg filenames.
The rest of the columns are just html code to make some color choice charts with pictures.

I want all of these condensed into one field for each productcode (“AS_ANGELA” in sample file)
Attachment 2.txt is a sample of what I want in each product field. The <table> tag on top, and </table> tag on bottom must also be included, and the “<tr>” tag must be after every rows (column “M” in 1.xls).

So the code in 2.txt must then be placed in a column field called extinfo (see attachment 3.xls). Each product code has (10-50 options). So AS_ANGELA would be 1 row, with the “extinfo” field containing everything in 2.txt. Other productcodes would each have 1 row.
I can then upload the resulting spreadsheet back up to our web site database.


2nd Excel formatting project:
See attachment: options-condense.xls

This one has the column names: id,optioncatid,optionsdesc,applytoproductcodes,ArrangeOptionsBy
ID is the product option ID, optioncat is the product category, options desc is a description (usually a color number), applytoproductcodes is the product code the option is used for, and ArrangeOptionsby is the order the options for each product appear in the drop down menu on the website.
The problem is each product can have as many as 50 options, and each product has a different combination of options. With 20,000 products, there are over 400,000options which is straining our server. I need to condense it so each option applies to multiple product codes.
The applytoproductcodes field can have multiple product codes, but is limited to 255 characters. I need to reuse each ID for as many product codes such as “BJ_Amy” and “BJ_Charli” that will fit in 255 characters. For common colors such as 1 or 2, several option fields called 1 or 2, ect. will be needed. Excel will need to not go over 255 characters, and create as many options as needed to fit all the productcodes that use that color. The catID must stay the same. Some colors are rare and may only have a couple applytoproductcodes.

I will try to clarify it better if you have any questions, and I can send larger samples if needed.
Thank you!


Additional files submitted:
1.xls
2.txt
3.xls
options-condense.xls