Dynamically prepend long url to file name for a file link.

[Misery@Work]

We have an issue where we're dependent on pushing Excel spreadsheets for sitewide updates. The issue I'm having is the 32k character limit for a single cell and a few pages blow that limitation out of the water. What would fix the problem is having a way to dynamically attach the long path to the file while only having the file name in the actual href.

Example?
<a href="https://www.xxxxxxxxxxxxx.com/2Documents/Marketing/Catalogs/English/IndexedCatalog/xxxxx/xxxxxx.pdf">

If I could maybe have a class that would add the path and just have the file name?

I'm not a programmer and know just enough to get myself into trouble.

[Nancy OShea]

Keep it simple.  Move pdfs to a common directory in your DW local site folder. 

Use document -relative paths such as  folder/folder/filename.pdf

[Misery@Work]

That would be wonderful, but we're hosting PDFs and technical files on a separate server.

[Nancy OShea]

Why odn't you take us through your workflow so we can better understand your problem.

[anissa_thompson]

This may be above your current knowledge base, but you could have your server dynamically fill the URL from a variable. I've done this with PHP, but it could also be done with ASP, and I believe JavaScript as well.

As @Nancy_Oshea has stated, we'll need a little more info to help guide you in the right direction.

[Misery@Work]

We're working with Yahoo! Small Business to develop a custom solution for our website. We use Epicor as our ERP system, but management didn't purchase the e-commerce package so a third party developer is building a work around. We have a secondary domain that is used to host a database and all of our techincal docs (3d files, word, excel and PDFs) are hosted and maintained there.

My responsibilities are to create the content for each product page as well as non-techincal pages like resources, videos, about, etc. I create the HTML for these sections using a Bootstrap template that I've modified for our purposes and push that data into an excel spreadsheet that we use to update the database on the Yahoo! side to maintain site content. 

Now to my particular issue. We have a seperate page for each international market that has our current catalog broken out into downloadable PDF pages. Specifically because our customers routinely need the technical data contained within those pages and some foreign markets like China have horrible internet access and can't take the time to download an entire catalog. The problem is that you have a 32k character limit for each cell on an Excel spreadsheet and one of these download pages has almost 50k characters. After reviewing the page and doing a little math I know I could get the count below the threshold if I could call in a file that has all the links and just reference it in the HTML. 

I hope this makes sense. I'm not a programmer, but I have no issues working with basic HTML, CSS and within a Bootstrap template framework. It's when you get into actual programming of Javascript or anything else that I'm at a loss.

Thanks ahead of time for any assistance.

[Nancy OShea]

Excel has a limit of 32,767 characters PER CELL.  Are you telling us that your urls are longer than that?  

https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269...

[Misery@Work]

No. Each product has a line in the spreadsheet. One cell in that line is reserved to paste in the HTML for that particular section. That section of HTML for this particular page is something like 42k characters.

I had our 3rd party developer adjust the script so that I can replace the long path in the URL with a keyword/filename which solves the issue with these particular pages. However, if there is another way to shoehorn large amounts of data into a cell that would be beneficial for some other pages I'm developing.

There used to be an option in Excel where you could dump all you data into an external file and put a link to that file in your cell. Then when you export the file as CSV it would reference that file and dump all the data into the cell with no character limit. I just can't find this information anymore????

[Nancy OShea]

Why in god's name are you putting HTML code into a spreadsheet?  HTML goes in your Bootstrap document, not your database. 

My server supports PHP & MySQL.  So that's what I'm using.  But a similar approach will work in other languages if your server supports them.  

The code below is from an actual Bootstrap product detail.php page.  The economy of code is mainly due to  server-side includes in green and database records in red.   Incidentally, this one page takes care of my entire product catalog dynamically.  So instead of 1,000 static site pages, I have one dynamic page that supports 1,000 products.  And when the database is updated with new content, it's automatically pushed to the site.  

<!--Connect to SQL database-->
<?php require_once('Connect_to_SQL_script.php'); ?>
<!--Query the product database table-->
<?php
$Recordset1 = new WA_MySQLi_RS("Recordset1",$Connect_to_SQL_script,1);
$Recordset1->setQuery("SELECT * FROM products WHERE PRODID = ?");
$Recordset1->bindParam("i", "".(isset($_GET['PRODID'])?$_GET['PRODID']:"") ."", "-1");
$Recordset1->execute();?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title><?php echo($Recordset1->getColumnVal("PRODNAME")); ?></title>
<?php include("includes/head.html"); ?>
</head>
<body class="detail container">
<!--SERVER-SIDE INCLUDES FOR REPEATED PAGE ELEMENTS-->
<?php include("includes/header.php"); ?>
<?php include("includes/top-navbar.php"); ?>
<?php include("includes/search-form.php"); ?>

<section id="product-details" class="row">
<div class="col-sm-offset-1 col-sm-7">

<!--DEFINITION LIST CONTAINING SQL PRODUCT DATA-->

<dl>
<dt class="center"><img class="img-thumbnail center-block" src="<?php echo($Recordset1->getColumnVal("PRODIMAGE")); ?>" alt="<?php echo($Recordset1->getColumnVal("PRODCODE")); ?>"></dt>
<dt class="center"><?php echo($Recordset1->getColumnVal("PRODNAME")); ?></dt>
<dd><?php echo($Recordset1->getColumnVal("LONGDESC")); ?></dd>
<dd>Duration: <?php echo($Recordset1->getColumnVal("DURATION")); ?></dd>
<dd>Category: <?php echo($Recordset1->getColumnVal("CATEGORY")); ?></dd>
<dd>Product Code: <?php echo($Recordset1->getColumnVal("PRODCODE")); ?></dd>
<dd>Price: <?php echo($Recordset1->getColumnVal("PRICE")); ?></dd>
</dl>
</div>
</section>
<?php include("includes/footer.php"); ?>
<?php include("includes/footer-scripts.php"); ?>
</body>
</html>

[Misery@Work]

Sigh...because all the data goes into a spreadsheet, exported as a CSV and uploaded into the Yahoo! database. I don't know if it's been blessed by the Lord, but that's the process.

[Nancy OShea]

HTML doesn't belong in a CSV file either.

[Misery@Work]

Look, I don't know what to tell you. Options, titles, model numbers and HTML for each section of the page is all entered into a spreadsheet so that one single file can be uploaded to Yahoo! which updates all the content on the site. Thanks for being rude and your non-assistance.

[BenPleysier]

Look, I don't know what to tell you. Options, titles, model numbers and HTML for each section of the page is all entered into a spreadsheet so that one single file can be uploaded to Yahoo! which updates all the content on the site. Thanks for being rude and your non-assistance.

I am sorry that you view Nancy's answer as rude, if your read her reply properly you will notice that what she is trying to tell you is that you (your company) is using the wrong approach.

It amuses me that spreadsheets are more often than not used for what it is not designed. I have even seen factory production flows animated in a spreadsheet. There are so many tools out there that would do a much better job.

Such as in your case, rather than entering information into a spreadsheet and translating the CSV into a database, why not have it programmed to enter the info directly into the database.

Anyhow, we are not privy to what is going on in your situation, it is only what we think is the right way. Ask the guy doing the scripts for you for assistance.

[Nancy OShea]

"...rather than entering information into a spreadsheet and translating the CSV into a database, why not have it programmed to enter the info directly into the database."

That was my thought too.  A backend CMS that ties in with the shopping cart would be ideal.