Skip to main content
M
Misery@Work
Participating Frequently
October 15, 2019
Question

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

  • October 15, 2019
  • 7 replies
  • 1227 views

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.

    This topic has been closed for replies.

    7 replies

    Nancy OShea
    Community Expert
    Nancy OSheaCommunity Expert
    Community Expert
    October 17, 2019

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

    Nancy O'Shea— Product User & Community Expert
    BenPleysier
    Community Expert
    BenPleysierCommunity Expert
    Community Expert
    October 16, 2019

    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.

    Wappler is the DMXzone-made Dreamweaver replacement and includes the best of their powerful extensions, as well as much more!
      Nancy OShea
      Community Expert
      Nancy OSheaCommunity Expert
      Community Expert
      October 16, 2019

      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>

      Nancy O'Shea— Product User & Community Expert
      M
      Misery@WorkAuthor
      Participating Frequently
      October 16, 2019
      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
      Community Expert
      Nancy OSheaCommunity Expert
      Community Expert
      October 16, 2019
      HTML doesn't belong in a CSV file either.
      Nancy O'Shea— Product User & Community Expert
      Nancy OShea
      Community Expert
      Nancy OSheaCommunity Expert
      Community Expert
      October 16, 2019

      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-269d656771c3

      Nancy O'Shea— Product User & Community Expert
      M
      Misery@WorkAuthor
      Participating Frequently
      October 16, 2019

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

      anissa_thompson
      Community Expert
      anissa_thompsonCommunity Expert
      Community Expert
      October 15, 2019

      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.

      Hope this helps! Make sure to press "✔ Correct Answer" on this post if this answers your question. Happy Creating!Anissa • @anissat
        M
        Misery@WorkAuthor
        Participating Frequently
        October 16, 2019

        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
        Community Expert
        Nancy OSheaCommunity Expert
        Community Expert
        October 15, 2019

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

        Nancy O'Shea— Product User & Community Expert
          Nancy OShea
          Community Expert
          Nancy OSheaCommunity Expert
          Community Expert
          October 15, 2019

          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

           

           

           

           

          Nancy O'Shea— Product User & Community Expert
            M
            Misery@WorkAuthor
            Participating Frequently
            October 15, 2019
            That would be wonderful, but we're hosting PDFs and technical files on a separate server.
            Terms & ConditionsAccessibility statement
            Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices