How can I output an excel files data in a certain way on my webpage?

How can I output an excel files data in a certain way on my webpage?
0

#1

I’m trying to make a program which allows a user to upload an excel file that contains mixed up random addresses, then after this the data will be outputted onto the webpage in a specific order based on how close the addresses are closest together. However, I’m not sure how to do the next step within my jsfiddle, which is to output all of the address within the same postcode.

Here is an example of the addresses process to make it more clear for you. For example, within the excel sheet if there was 100 addresses mixed up together with different postal addresses listed next to each other the program would re order the address list by putting on the N1 post codes together.

Currently I have finished step one and two, which the upload of the excel file and for the data to be outputted to the webpage as you can see in my jsfiddle. However, I’m not sure how to do the next step, which is re order the original uploaded list to output all of the address within the same postcode together.

Here is the jsfiddle.

print('Please upload the excel file by clicking on the button Choose File')

loadFile = function(event) {
	alasql('SELECT * FROM FILE(?,{headers:true})',[event],function(data){
		print(data)
	});
}

function print(x){
    document.getElementById('output').textContent += JSON.stringify(x,  null, '\t')+"\n";
}

#2

Could you post a sample of what the data looks like in the output DOM element after the print function executes or show a screen shot of what the excel file data looks like?

Also, could you show an actual example of how this sample data would need to look after the reorder take place.

It always helps to see the before and after data, to come up with a solution.

Thanks.


#3

Well, you never responded back with what fields were in the file, but if you were to have an Excel file with data that looked like this:

then the following JavaScript should print out the sorted file in JSON format:

print('Please upload the excel file by clicking on the button Choose File\n\n')

loadFile = function(event) {
  alasql('SELECT * FROM FILE(?,{headers:true})',[event],function(data){
    data.sort(function(a, b){
      return a.Zip - b.Zip;	
    });
    print('\n' + JSON.stringify(data,  null, '\t')+'\n\n');
  });
};

function print(x){
  document.getElementById('output').textContent += x;    
}

After creating the above test file, I ran the script and got the following:

Please upload the excel file by clicking on the button Choose File

[
	{
		"Name": "Suzy",
		"Street": "2663 lskgjsl js",
		"City": "San Jose",
		"State": "CA",
		"Zip": 11111
	},
	{
		"Name": "Frank",
		"Street": "7877 Harrsion Drive",
		"City": "Aptos",
		"State": "CA",
		"Zip": 22222
	},
	{
		"Name": "John",
		"Street": "233 Lave Ave",
		"City": "Santa Cruz",
		"State": "CA",
		"Zip": 33333
	},
	{
		"Name": "Bob",
		"Street": "633 glue street",
		"City": "Portland",
		"State": "OR",
		"Zip": 77777
	}
]

#4

Hi,

Sorry for not responding quicker I was really busy. This is exactly what I’m looking for and your time is great appreciated that you went on this :slight_smile:

Do you have a working JSFIDDLE?


#5

No problem. I had never used that library before, so I had to read about it first and then experimented around with it to come up with a workable solution.

I just used the fiddle link you had only modified the JavaScript to arrive at what you see above. You should be able to copy/paste the code above and it will work. I would love to see your final version when finished.

If you have any trouble with the code, just post a revised fiddle link and I will take a look at it.


#7

I got the jsiffdle working now which is great.

However, one small problem that I just noticed is that since I’m located in the UK, I was wondering how I could change “zip” to “post code” and for the program to still list it in the same method example as the “zip” .

Example
http://imgur.com/a/mL0aJ

I put the “post code” within the address if you want to make it easier maybe you can change it to be in a operate column if it makes it easier for you.

Thanks for the help!


#8

When I looked at the screen shot of your Excel file, the address is not split out into street address, city, and zip. Unless there is some kind of delimiter on the address field, you will have to be creative with some kind of regular expression to get the data broken out.

If a field has a header like “Post Code” instead of “Zip” where there is a space between the words of the header, you will have to use bracket notation to refer to the Object property. For example, if my Excel file looks like:

then you would change the sort code part to:

    data.sort(function(a, b){
      return a['Post Code'] - b['Post Code'];	
    });


#9

I was playing a little bit more with a solution for when Street Address, City, and Post Code are in the same Excel field. Let’s assume the following was in the Excel file:

and if you want to sort by just the Post Code (I assume the last two items in the Address field - i.e. N1 385), then you first need to split out the address into the various components. I needed to add a Post Code to each object in the data array, so below are the steps to do it using a forEach loop to modify each object in the array. :

  1. I broke out the Address into two parts using ", " as the delimeter. This allows me to have the street address separate from City/Post Code (see cityAndPostCode array in code below).

  2. I was able split the cityAndPostCode array into 3 parts (City, Post Code part 1, Post Code part 2), because they were separated by spaces.

  3. I reassigned the street address obtained in step 1 back to the object

  4. I created a new property called “City” for the city which was in the first element of the array created in step 2.

  5. Finally, I had to reassemble the two parts of the Post Code and assign the result back to a new property called “Post Code”

Here is the final JavaScript code to make this work.

print('Please upload the excel file by clicking on the button Choose File\n\n')

loadFile = function(event) {
  alasql('SELECT * FROM FILE(?,{headers:true})',[event],function(data){
    breakOutAddress(data);
    data.sort(function(a, b){
      var field = 'Post Code';
      return (a[field] < b[field]) ? -1 : (a[field] > b[field]) ? 1 : 0;
    });    
    print('\n' + JSON.stringify(data,  null, '\t')+'\n\n');
  });
};

breakOutAddress = function(data) {
  data.forEach(function(obj) {
    var addressArr = obj.Address.split(', ');
    var cityAndPostCode = addressArr[1].split(' '); // second element of addressArr contains the city and post code
    obj.Address = addressArr[0];
    obj.City  = cityAndPostCode[0];
    obj['Post Code'] = cityAndPostCode[1] + ' ' + cityAndPostCode[2]; // needed to reassemble the post code
  });
}

function print(x){
  document.getElementById('output').textContent += x;    
}

The above code used with the Excel file I showed above produces the following output:

Please upload the excel file by clicking on the button Choose File


[
	{
		"No": 3,
		"Company": "Optic",
		"Name": "Mr. Liam Docherty",
		"Address": "40 Street",
		"City": "London",
		"Post Code": "E1 DE2"
	},
	{
		"No": 4,
		"Company": "Buildog",
		"Name": "Mr. Jon Docherty",
		"Address": "10 Street",
		"City": "London",
		"Post Code": "E3 85A"
	},
	{
		"No": 2,
		"Company": "FaZe",
		"Name": "Mr. Jack Test",
		"Address": "640 Street",
		"City": "London",
		"Post Code": "N1 385"
	},
	{
		"No": 1,
		"Company": "E6",
		"Name": "Mr. Harry Nick",
		"Address": "4 Street",
		"City": "London",
		"Post Code": "SW QWE"
	}
]

NOTE: Since each object in the data array now has 3 separate properties for address (Street Address, City, and Post Code) instead of the originally combined address, you would need to concatenate them together, if you wanted to display the combined address in the future. Plus, having city separate would allow you to sort by city if needed in the future. If you do not care about being able to sort by city or another newly created property. then you would not reassign the Street Address to the Address property of each Object and not create the City property at all. You would still need the new “Post Code” property to make the sort work correctly. To accomplish this, you would just need to remove the following lines from the breakOutAdress function:

    obj.Address = addressArr[0];
    obj.City  = cityAndPostCode[0];

#10

Thanks for posting the in-depth clear post which explained everything regarding the program much appreciated. I will 100% send you the final version. However, one small issue I can’t seem to get the jsfiddle working. http://jsfiddle.net/3ve90afo/98/ Could it be the code or am I clicking something wrong?


#11

What you have in this revised fiddle works as long as the Excel file looks like below:

If it is not working, I would need to see the file you are trying to upload.


#12

Mine exactly looks like your data and the file format is the correct choice. Strange… I’m going to give it ago again later on when I wake up I’ll keep you updated. Could you maybe email me your excel file if you don’t mind, I’ll give that a try see if it works. My email is [email protected].


#13

Just sent you a private email with the Excel file.