Is csv with multi tabs/sheet possible?

Question

I am calling a web service and the data from the web service is in csv format. If I try to save data in xls/xlsx, then I get multiple sheets in a workbook. So, how can I save the data in csv with multipletab/sheets in c#.

I know csv with multiple tabs is not practical, but is there any damn way or any library to save data in csv with multiple tabs/sheet?

a possible alternative - https://technet.microsoft.com/en-us/magazine/2006.01.blogtales.aspx — MethodMan, Apr 13 '15 at 21:14
What do you mean by "multiple tab/sheets" for csv? So that, when opened up in Excel, you'll get multiple tabs? — Ann L., Apr 13 '15 at 21:16
Is converting it to XML (as suggested by @MethodMan) a possibility? — Ann L., Apr 13 '15 at 21:18
Saving and retrieving to and from XML wont be much complicated ? The link given by @MethodMan is requiring you to set up manually. — hello temp9, Apr 13 '15 at 21:26
You can save it as Excel format (xls) with all the work sheets to preserve your data. You can then export/convert it to CSV as a single file. — Minilik, Jun 13 '17 at 05:27

KeithS · Answer 1 · 2015-04-13T22:13:30.397

CSV, as a file format, assumes one "table" of data; in Excel terms that's one sheet of a workbook. While it's just plain text, and you can interpret it any way you want, the "standard" CSV format does not support what your supervisor is thinking.

You can fudge what you want a couple of ways:

Use a different file for each sheet, with related but distinct names, like "Book1_Sheet1", "Book1_Sheet2" etc. You can then find groups of related files by the text before the first underscore. This is the easiest to implement, but requires users to schlep around multiple files per logical "workbook", and if one gets lost in the shuffle you've lost that data.
Do the above, and also "zip" the files into a single archive you can move around. You keep the pure CSV advantage of the above option, plus the convenience of having one file to move instead of several, but the downside of having to zip/unzip the archive to get to the actual files. To ease the pain, if you're in .NET 4.5 you have access to a built-in ZipFile implementation, and if you are not you can use the open-source DotNetZip or SharpZipLib, any of which will allow you to programmatically create and consume standard Windows ZIP files. You can also use the nearly universal .tar.gz (aka .tgz) combination, but your users will need either your program or a third-party compression tool like 7Zip or WinRAR to create the archive from a set of exported CSVs.
Implement a quasi-CSV format where a blank line (containing only a newline) acts as a "tab separator", and your parser would expect a new line of column headers followed by data rows in the new configuration. This variant of standard CSV may not readable by other consumers of CSVs as it doesn't adhere to the expected file format, and as such I would recommend you don't use the ".csv" extension as it will confuse and frustrate users expecting to be able to open it in other applications like spreadsheets.

Thank you. Can you give me an example to implement a quasi - csv format with blank line as a seperation. — hello temp9, Apr 13 '15 at 22:33
It's fairly straightforward; simply parse CSV lines as normal (first line is headers, then data rows matching those headers), but before parsing a data row, test to see if the line contains any non-whitespace characters. If not, then break out of the data row parsing and repeat the column header logic using a new collection to store the next sheet's data. Anything more specific than that and you'll be asking me for code you can drop in, and that's not what this site is for. — KeithS, Apr 14 '15 at 00:08

score 12 · Answer 2 · answered Apr 13 '15 at 21:19

12

If I try to save data in xls/xlsx, then I get multiple sheets in a workbook.

Your answer is in your question, don't use text/csv (which most certainly can not do multiple sheets, it can't even do one sheet; there's no such thing as a sheet in text/csv though there is in how some applications like Excel or Calc choose to import it into a format that does have sheets) but save it as xls, xlsx, ods or another format that does have sheets.

Both XLSX and ODS are much more complicated than text/csv, but are each probably the most straightforward of their respective sets of formats.

answered Apr 13 '15 at 21:19

Jon Hanna

110,372
10
146
251

The data which I am getting is in CSV and I am told to keep it in csv only. Because the server might not support Excel and Office.Interop where I want to retrieve certain values. – hello temp9 Apr 13 '15 at 21:25
There is no library in c#/Asp.net which can make multiple sheets ? – hello temp9 Apr 13 '15 at 21:28
You wouldn't want to use Excel on the server anyway -- it's explicitly not designed for heavy multithreaded, non-UI use. There are plenty of libraries out there that will generate Excel files in a server environment (in pure .NET code, no external dependencies); there are plenty of good paid ones, and probably some free ones. – Joe White Apr 13 '15 at 21:46
Is there any good and free library in .net which is free ? I do not want to use Interop – hello temp9 Apr 13 '15 at 21:49
@JoeWhite And no library to convert to multiple sheets for csv ? – hello temp9 Apr 13 '15 at 21:49
2

You can't have multiple sheets in CSV, because CSV doesn't have sheets. – Jon Hanna Apr 13 '15 at 21:55
@hellotemp9, I don't know. You're going to have to do some research. But you do have your answer to the question you asked: CSV has no concept of sheets; use XLSX instead. – Joe White Apr 13 '15 at 21:58
@JonHanna Thanks. But is is possible to not have any tabs in csv. My superviosr is thinking, there should be some way. Do you have any link with which I can prove it, that there is no way. – hello temp9 Apr 13 '15 at 22:02
3

Ask your supervisor to point to the bit in https://www.ietf.org/rfc/rfc4180.txt that shows how sheets are specified. They won't be able to, because it's not there. It's like asking how to turn the sound up on a CSV. Meanwhile, XLSX files are internally zip files of a few related XML and text files. They aren't exactly easy to build programmatically, but they aren't terribly hard either. – Jon Hanna Apr 13 '15 at 22:05
@JoeWhite Can you please help me on this : http://stackoverflow.com/questions/29613374/reading-hyperlink-from-excel-file – hello temp9 Apr 13 '15 at 22:06
@JonHanna Can you please help me on this :http://stackoverflow.com/questions/29613374/reading-hyperlink-from-excel-file – hello temp9 Apr 13 '15 at 22:06
I haven't used ADO with excel, so I don't know what's up there, but hopefully someone will. – Jon Hanna Apr 13 '15 at 22:08

score 5 · Answer 3 · answered Apr 13 '15 at 22:30

5

I've been using this library for a while now,

https://github.com/SheetJS/js-xlsx

in my projects to import data and structure from formats like: xls(x), csv and xml but you can for sure save in that formats as well (all from client)!

Hope that can help you,, take a look on online demo,

http://oss.sheetjs.com/js-xlsx/

peek in source code or file an issue on GH? but I think you will have to do most coding on youre own

answered Apr 13 '15 at 22:30

Kresimir Pendic

3,597
1
21
28

so you are telling is multi sheet csv possible? – hello temp9 Apr 13 '15 at 22:34
no, that thing does not exist,, csv is rough format, old format but mainly for data transport used,, no multi anything :) – Kresimir Pendic Apr 13 '15 at 22:48
This seems really good. Can you guide me on how to use this library in my visual studio ? – hello temp9 Apr 13 '15 at 23:48
I want to extract csv of every file deliminated by a line and thats what this library is giving. But please help me on installing this library. – hello temp9 Apr 13 '15 at 23:51

score -3 · Answer 4 · edited May 07 '19 at 17:34

-3

I think you want to reduce the size of your excel file. If yes then you can do it by saving it as xlsb i.e., Excel Binary Workbook format. Further, you can reduce your file size by deleting all the blank cells.

edited May 07 '19 at 17:34

Mayo

369
1
10
24

answered Dec 28 '16 at 08:34

Asim Khan

9

Is csv with multi tabs/sheet possible?

4 Answers4

Linked