Xlsx vs csv - finding solutions
What is the difference between XLSX and CSV?
In a xlsx you can save the formulas, graphs, pivots, etc in the file. A CSV is just a flat text file that uses a delimiter to separated the fields, e.g. Although Excel can read the fileformat and you can use formulas in it, you cannot save the formulas in the sheet. Excel will only save the result of the calculation.
Okay so welcome back, we've finally run out of budget, but what we need to do now is talk about the different spreadsheet file formats available to us and you really shouldn't be skipping this article I know it seems a little boring to talk about file formats but trust me this is going to be really useful information so let's start by saving our document here, this is the spreadsheet I used in LibreOffice as ok i will skip this default for now and skip straight to what is probably the most commonly used format which will be this xlsx format, in other words this is the microsoft excel format, it is called microsoft excel 2007 slash 2010 xml may not say exactly the same in Your table, but anyway the last one becomes the last kind of suffix or extension here bexlsx be. I'll go ahead and save this to my desktop and I'm going to give it a name from Robo Bunny Budget, okay I'll go ahead and save that now, let's save that and of course when I do, because I will I'm using Libre Office and because LibreOffice doesn't save natively it doesn't save automatically, I don't save it in this format by default, it asks, hey are you sure you want to use this and yes I really want to use this, that's perfectly fine, we don't have to worry so I went ahead and saved this as an XLS-Xformat file and that's great. This is a great format especially because it allows me to share this table with other people who may not be using Libre Office.I have the flexibility to save in two different formats so that other people can use this file, even if they Don't use Libre Office, okay and that's great, why don't I close that and we'll see in a moment that was our sample budget we don't want the Robo Bunny budget that's what we just saved let's go ahead and open that okay I will open this up here in libre office it's okay awesome so it certainly saves an xlsx format now let's take a look at the other formats available to us i go back to the file and go back one more time i will save and i want to show you one more thing you may have noticed down here there is an xls format okay and the only difference is that it is a m does not have the additional X at the end.
You may wonder what the difference between these is. It's also called Microsoft Excel This is an older version of Microsoft Excel spreadsheet file and you might think oh it's older I don't know I don't want to use it but it's actually very useful to know as the problem is with the newer version, the newer version which is the xlsx file is older, can't open it and buy older versions which i'm talking lly older versions like if you had a 2003 version of the excel i know it is ancient at this point, but you know some people still have old software on their computers if you had an old version of excel you couldn't open this xlsx file okay it is not backward compatible in whereas XLS files can be opened by both old Excel and new Excel versions, now I have to admit the longer I've talked to my students about it because a few years ago n was actually a big deal but a lot of people were still using older versions of Excel now don't so many a lot of people have newer versions of Excel It seems to be very very common It is very rare now that people don't have XLSX Can open files, but I still think it's likely for the next year or so that it's still important to let people know, suffice to say, when you see an XLS file you can use it Open your version of Excel, as long as it is a file that you know, as long as it is the file itself upted, so that we can continue and save again, even though we are inlibreoffice, we can save in Microsoft Excel format can save in xls format if we want and i will go ahead and do that i will click and save again we get this little dialog box where ax asks us to confirm the file format , and that's perfectly fine with any version of Excel, although you need to make sure you have a fairly recent version of Excel in order to open XLSX files. Here's a nice tip, if you ever get an XLSX file and happen to have an older version of Excel and are trying to figure out how to open it because you can't open it, Libre Office actually opens all of these files, it's very powerful and you can just temporarily download Libre Office and open that file in it, so that's a nice little trick to make things clear go back to LibreOffice and talk about Libre Officelet going back and I want it to have a few more file formats I want to talk to you about.
Let's go back and save as and here is another file type This is the first file type that comes up as some sort of our default and you might see this every now and then, especially when you are working with other people who have Libre Office or other Open -Using source software, you may see the ODS extension OK ODF stands for OpenDocument Format OK and the ODSformat is the default format used by libreoffice to store tables, if you don't change the default, okay I want to tell you about this format probably not going to use it very often, unless I mean for someone like me, when I do a spreadsheet and I know that I am not going to do it to share this table with others and it just stays on my computer then you know you, and I use Libre Office for pretty much everything, now I'll probably save everything in ODS format and don't worry because it doesn't make a difference, but most of the time you'll be sharing your files, your spreadsheet files, with others, okay or you might want to send them to work or something like that, in which case you don't want to go in an ODS format fine, some excel Versions may open, but some cannot. So you want to be as flexible as possible if you want your file to be opened by as many different applications as possible to stick with either Excel XLS X format or XLS format, but I want to tell you something about tell the ODS format simply because it's the default setting in LibreOffice and we've used Libre Office as our example so far, here is our ODS format file that we just saved okay all three of these files apart from the format is the same but the actual content the numbers the formula is everything we just wrote identical okay the only difference is the computer file format it has to do with the way things are coded ok let's go back to libre office two more file formats I want to talk to you about this, Okay, these two file formats you probably won't use nearly as often, Okay, but I want to create that you know about it, let's go back to archive and save, while i want to talk to you about this file format called CSV now you may have seen this maybe you have natives you don't have it it is a text format text csv and CSV stands for comma separated values and that's exactly it now I go ahead and save this in CSV format It's not a very useful file format for what we've just done here, what our budget is, but I'll go ahead and save that and what it will say to me, it will say hey okay you know one more time to confirm that i go ahead and do that i just go ahead and click yeah okay and it gives me a warning the only thing all that means to me that only this current worksheet was saved because I can only CSV save one worksheet at a time and that's perfectly fine for what we're doing here when I've r looking to open this i will try to open this in it, just a simple text file, okay to show you what's going on here and have a look at this and you go oh this is terribly disgusting i don't like this at all all it does is that it stores the data in clear text separated by commas and you might be thinking what the use of this right in this case it is not very useful at all it has stored all the dataave but we have lost all the formulas and everything that table useful, we've lost all of that, so on a budget for some kind of non-functional spreadsheet like this one isn't a very useful format for us, but CSV is a very useful format for something else.It's useful when you're using a spreadsheet to Store information so i will go ahead and temporarily start a new spreadsheet here and what if you had some sort of what if you had all of your contacts i I'd keep an eye out, maybe h something like first name and last name and email address and phone number something like that and then you said you know John Smith and then blah blah blah blah so and we have JaneSmith etc etc etc Okay , some people save all of their information then we use the raster format here.
Now to save a lot of information, the CSV format is very convenient as the CSV format is a very portable format that allows you to store data very efficiently and export to and from applications effectively Okay, what do I mean? Suppose you had tons and tons of records here, maybe you want to take this data, all this information, and import that into some kind of contact manager, okay, some kind of software that organizes all of your contacts for you, or maybe you want that exporting to some kind of system like some kind of contact management system or something like that then in this case saving a certain worksheet as a CSV file is very useful because this ensures that it removes all things that would normally confuse a contact manager or some other kind of software it would remove all formatting and everything and formulas and anything that would potentially confuse any of those programs and just transfer the data, so comma-separated value CSV files are very useful when transferring data in and out of applications. Okay, maybe you have the downside of that maybe you have a contact manager, some kind of contact manager, and you want to back up all your contacts. Maybe you have thousands of contacts there and you want to go back with them.
Many of these uses many of these software programs allow you to to export all information and CSV format and guess what exactly how to export spreadsheets in CSV format, you can also import data in CSV format in spreadsheets Believe it or not, it will actually import everything and then place it all put all the data in those boxes really nicely organized for you so then you can do things, sort things out, and do all sorts of cool things that are a little bit beyond the scope of this class, okay we keep you focused really just focus on using spreadsheets and learning formulas and Functions and the like, but you know when you actually start doing more data management and reusing spreadsheets you will find a lot of really good uses for that, so that's what CSV formats are all about now, the last format I want to tell you is that there is a Format is that you probably won't use too often. It's the PDF format you might know PDFs are already. PDF stands for Portable Document Format and that's basically a type of file that is a non-editable file okay, so this will be won't be very useful if you want to share your table.
They want someone else to have a chance to go in and edit and add information that it might be useful for, for example, let's say you have a business plan in place, okay, and let's say, instead of this is a recording budget, maybe is Is this some kind of total budget or maybe it is a cash flow analysis or something like that? Maybe you actually want to export to PDF and then you could include that PDF as an attachment or something in your PDF business plan. Again, using a familiar PDF and editing PDFs is a bit outside the scope of this class, but suffice it to say you can export from many programs, many spreadsheet applications allow you to export directly as PDFLibreOffice, which makes it really easy. There's literally a button here that allows you to export as a PDF directly so I can just save it as a PDF and when I close it and when I open it here we'll see right here my budget is all fine or now I can not really edit but you know i could go in and i could maybe i might want to go in and format this as li a bit but this is ready for printing now and i don't have to worry about all of these lines and i could maybe let you know that Do you edit this and add this PDF as an attachment to a business plan or something or maybe a financial report or something like that? slightly more advanced usage, but I wanted you to know about this, so these are the five main formats we'll likely be using.
There are plenty of other formats out there too but those are the five that I think are the most useful that we know about two excel formats which are xls and xlsx, okay you have the ODS format that is is the default for Libre Office, and you won't actually use it outside of that. So this really only affects you if you have Libre Office, if you are using something else you don't really have to worry that CSV isn't that useful when you are talking about spreadsheets in terms of budget, but very useful when you are Transfer raw data to and from applications if you have contact for example This information is a really good example, and finally PDF which will be really more useful if you want to publish your spreadsheet in some sort of report format. Okay, Libre Office is awesome because you can import an expert in all of these different formats and we'll see it here.
See, there were all of these other formats here and there are rocks that I haven't even talked about but we have to don't worry too much about the others we worry about ODSxlsx XLS CSV and if you wonder then where the P is the PDF? just that button is fine here Excel allows you to export and a whole host of different formats. Let's see what options we have when it comes to exporting and from Google Sheets Okay, here's an example where I created the exact same recording budget, exactly the same thing, but I pasted it into our Google Sheets, okay and by the way when i click here we can see it is exactly the same, the layout might look a little different for you these tabs are all or these buttons are a little different but at the end of the day it is exactly the same table, it is not right, we have our expenses we have our subtotals here are the unit costs per unit and all I've done here was here, if I click here we'll see that the formula is correct and remember, even though we are totally in one other program are, same formula, here is the same sum function, here a function, okay, and here we use a subtraction formula to calculate profit, and then below a division formula to find out the profit per band member so this is exactly the same spreadsheet except that it is randomly created in a google spreadsheet. Let's say I want to save this What are my options when I click File here I have a couple of download options and you remember because you're on the web you're technically all changes are saved on the fly, but when you do To actually download this file to your computer, you have a few options.
You have the option to save them as an XLS X file that will include your M. is icrosoft excel filewe talked about it before and interestingly, Google actually gives you the option to download directly to the open document format which is the ODS format which is the standard format that LibreOffice uses. You can export as a PDF document, just like in LibreOffice Okay, we've already talked about comma separated values, tab separated values are very similar to comma separated values, except that the data is not separated by commas, it's all of them Tab Separated Find That CSV Tends To Be In my experience, CSV files are more reliable than tab separated files, but you know your mileage can vary.
It's not the end of the world You can also export to HTML Check out that xlsx ODS PDF and CSV are all available. You're the only ones not available here, just the old XLS level, this is the older Microsoft Excel version and as I mentioned, because you know it's older software that we are seeing less and less now and pretty soon it won't really be relevant, but for now I just wanted to make sure you know about it now let's switch to the microsoft excel web app in SkyDrive and see what options we have here now here they are a little more limited i will click on the file and we will see here that we can actually save how to do it and then down here we can download a copy of this workbook to our computer so I hit the download button and we see here that the only one Option we have and this is just XLSX by default is fine so the Microsoft Excel web app and SkyDrive just give us the option to download asxlsx and so will we go ahead and do that and i will just go ahead and save the file and i hit ok and it just gives us this one format but xlsx is a great format will probably use most ul format for us and we have it right now downloaded our computer Oh that's a lot right I hope this wasn't too boring but it's really important to know all of these different file formats because the last thing you want to do is put together a great time all this time Spreadsheet and then save it in the wrong format and then let it be of no use to you, so these are the file formats that will be most useful to you in the next screencast. I'll talk to you about where you can go from this point, how can you? You expand your knowledge of what the next logical steps in your learning path are when it comes to spreadsheets and after that you can take it over from there, so we'll see you in the next article
Which is better CSV or XLS?
While Excel (XLS and XLSX) file formats are better for storing more complex data, CSV files are supported by nearly all data upload interfaces. If you are planning to move your data between platforms, export and import it from one interface to another, you might be better off with the CSV file format.
Ok Great! Now we understand why people use databases, however I would imagine some of you are Excel users and still imagine an Excel spreadsheet when we talk about tabular data, please don't! Data tables, databases and Excel spreadsheets are different things! In this lecture, we will focus on the differences between spreadsheets and databases. This exercise will not only be relevant to current Excel users, the advantages and disadvantages of using databases or spreadsheets. Ok, let's start with a definition of what is a spreadsheet? It is an electronic general ledger, an electronic version of paper accounting worksheets.
It is designed to make it easier for people who have had to save their accounting information in tabular form digitally, so it is possible to create tables in a spreadsheet. This is one of the reasons why some people believe that spreadsheets and databases are interchangeable when in reality there are differences between the two. Both can contain a large amount of tabular data and can use existing data for calculations.
Third, neither will Spreadsheets nor databases normally used by a single person, many users will work with the data. The differences between the two forms of data storage are in the way these three features are implemented. Ok.
Imagine a table. Each Cell is treated as a unique entity. It can store any type of information - a date, an integer value, a string name.
And then, not only can we have different kinds of values in different cells, but we can have those cells as well assign a specific format. This is not inherent in databases. They only contain raw data.
Each cell is a container with a single data value, the smallest piece of information there is. You have to preset the data type in a certain field. This feature prevents accidental mistakes - for example, if the user tries to enter a string in a date field, the software will sho if an error occurs and it has the opportunity to correct itself.
This will not happen in Excel - if you paste a string into the column of date values, you will not get an error message and Excel will save the string value.In a spreadsheet, data can be stored in a cell, while in a database, data can be stored in a record of a table meaning you have to count the records in a table to express how long the data table is, not the number of cells - you cannot choose a font color or size. All you care about is the information that is stored; You don't care about the formatting.
Our main goal is to save the numbers. Another major difference is that in a spreadsheet, different cells can contain calculations such as functions and formulas, and the result is stored in a different cell. In a database, all calculations and operations are based on the existing data and are performed after retrieval.
There is a special function called 'Views', similar to the tables, that you can perform a calculation in. These objects also contain columns that can be normal columns like those in the tables or contain some type of calculation. There is no way to confuse a data set with a calculation.
The database features mentioned so far improve data integrity - you cannot store different types of data in the same field, and it is unlikely that anyone will mistake a data value for a result of a calculation, especially with large data sets. Data integrity is a huge benefit when working with data. Of course, you might think that a spreadsheet can contain multiple worksheets, so you can create tables in the worksheets and then use the worksheets to create relationships between the tables.
Why bother using relational databases? Well, in a spreadsheet, such relationships are logically limited; instead of setting up spreadsheets or worksheets, you can set up relationships between the tables, which improves the performance of operations and the speed at which you can manipulate your data set. While powerful for many circumstances, spreadsheets have their limitations; Excel is unable to handle over 1 million rows of data. This immediately prompts us to look for a solution.
Usually the solution is to use databases where 2, 5 or 10 million records are not a problem owned, spreadsheets lag behind. Basically, each person needs to update their own spreadsheet with new data. For example, if there is a new purchase to register or a last name to be corrected in the 'Cus' tomers' table, each user has to make these changes manually.
You would rightly think that Google Docs and the latest versions of Office solve this problem, but they only partially do it. In Google Docs, you may have trouble figuring out who made changes or deleted information incorrectly, which often leads to a cumbersome situation where people struggle to organize their tasks. In contrast, you saw in the Data Control Language lecture that databases offer a stable structure and control access rights and user restrictions.
A person can make a change that is instantly visible to everyone. This function increases the efficiency and data consistency when using databases. In view of data integrity and data consistency, using databases eliminates duplicate information, which is another way to save space and improve efficiency.
Look at the 'customers'. Table. You know a given first and last name corresponds to a unique email address, so if you know John McKinley changed his email using a data-flooded table, you can get the email - Change address once and accidentally miss the update of the same address in another record.
This can lead to accidental errors. They can be avoided by using a relational database - an accredited user just needs to access the Customers table and change John McKinley's email address there. Once only.
This process not only saves time, but also prevents inconsistencies. So what we've discussed in this lesson shows why databases are a better environment for storing and tracking data when you are working with multiple dimensions and large amounts of data. Spreadsheets have their advantages too - they are a great tool for performing extensive analysis.
But for easy data retrieval and updating, efficiency, data consistency, data integrity, speed, and security, relational databases are definitely the structure. They can store a lot of raw data and are great for separating data. The data is displayed as it is displayed for analysis.
As you have seen, it would be a good idea to stop trying to visualize tables of data in the form of spreadsheets. They're different. Stay tuned for the next lecture where we'll be offering more database terminology.
Thanks for watching!
Is CSV faster than Xlsx?
CSV is a format for saving tabular information into a delimited text file with extension . csv whereas Excel is a spreadsheet that keeps files into its own proprietary format viz xls or xlsx. ... CSV files are faster and also consumes less memory whereas Excel consumes more memory while importing data.13 mei 2021
For this lesson, I need to open a new blank Excel 2013 workbook. Now let's take a look at the file formats that Excel supports. So I click the File button in the top left of the screen and then click Save As in the left menu bar.
Then Computer and then the Browse button. And I click the drop-down arrow to the right of the 'Save As Type' drop-down menu. You can see that there are many different formats that I can save a workbook in.
We will discuss the most common and important file formats. Above all the Excel workbook format. This is also known as the Open XML format.
Before Office 2007 was released, each program stored its information on disk in a different way. These incompatible formats are known as binary formats and made it very difficult to write applications that could be used together. All of this changed with the introduction of the new Open XML format.
Microsoft published exactly how the format worked and gave it to the world's developer community for free. This allows other programs to work with Excel workbook files without any problems. For example, the Apple iPhone supports Office Open XML email attachments, so you'll want to save your files in Excel workbook format most of the time.
Note, however, that there is also an Excel 97-2003 workbook format. This is the old format that was used before Excel 2007 was released. Very few people in the world are now using Excel 2003 and earlier, but if you had to share a file with such a user, you'd have to save it in the old Excel 97-2003 format.
Now let's talk about the Excel Macro -Activated Workbook. An Excel macro-activated workbook is simply a workbook that has program code (called VBA code) embedded in it. Macros are beyond the scope of this course but are covered in the Excel Expert Skills course in this series.
While macro code is very powerful, it can also be destructive because it is extremely easy to write harmful viruses into Excel macro code. Versions of Excel prior to Excel 2007 could allow a workbook to infect your computer with a macro virus because all Excel files could contain macros. Now that the formats are separate, it is easier to avoid opening potentially infected files.
Now let's look at the Excel Binary Workbook Format. It is very unusual to use this format, but it has one advantage over the others. If you are working with a very large workbook, it can take a long time to open or save.
The Excel binary workbook opens and saves much faster. So if you are working with a huge workbook and it takes a long time to open and save, then you should consider Excel binary workbook format. Now we come to an extremely useful format: the PDF format.
If you need to send a worksheet to someone who doesn't have a copy of Excel, you can save it in PDF format. It stands for Portable Document Format. This format was invented by Adobe and is sometimes called Adobe Acrobat or simply Acrobat.
Adobe offers a free PDF reader program, and most users already have it installed on their computer. When you send a PDF to a user, they can read and print the worksheet, but they cannot change it. At this point, the XPS document format is also probably worth mentioning.
This format is not widely used, but it is an alternative to Adobe PDF format developed by Microsoft. You can see that there are also many other less commonly used formats that are supported by Excel 2013, but the formats we discussed are the only ones that you will typically encounter. The most important thing is that you should always save workbooks in the standard Excel workbook format unless there is a good reason to use a different format.
I now click Cancel to close the dialog, the Back button, and I close Excel. And you have now completed Lessons 1-7 on Understanding Common File Formats.
Can you convert XLSX to CSV?
How to convert a XLSX to a CSV file? Choose the XLSX file that you want to convert. Select CSV as the the format you want to convert your XLSX file to. Click 'Convert' to convert your XLSX file.
What's the difference between CSV and XLs in Excel?
Excel is a spreadsheet that saves files into its own proprietary format viz xls or xlsx. CSV is a format for saving tabular information into a delimited text file with extension .csv. Files saved in excel cannot be opened or edited by text editors.
What is a CSV file format in Excel?
CSV is a format for saving tabular information into a delimited text file with extension .csv. Files saved in excel cannot be opened or edited by text editors. CSV files can be opened or edited by text editors like notepad.
What's the difference between a CSV file and a text file?
CSV stands for Comma Separated Values. A CSV file is just a text file, it stores data but does not contain formatting, formulas, macros, etc. CSV is safe and can clearly differentiate between the numeric values and text. CSV does not manipulate data and stores it as-is.
Which is better for a developer, Excel or CSV?
Excel allows for Review of Data with detailed tracking and commenting feature. As a developer, it's difficult to programmatically manipulate Excel files since the Excel is proprietary. This is especially true for languages other than .NET As a developer it's easy to programmatically manipulate CSV since, after all, they are simple text files.