6 ms·

idk im pretty good at exporting to CSV.

by fu9ar 10y ago

taneq 10y ago
Dunno why you're getting hammered, this is part of a good Excel workflow. Do what you can using the nice GUI, if you have bulk changes to make that are easier to script than find-and-replace then dump it to CSV, do the grunt work with some command line tools or a script, and re-import it.
- smallnamespace 10y ago
  While this is a fine workflow if you're more familiar with command line tools than Excel, bulk changes are usually easily doable in Excel with judicious use of cell formulas and copy-pasting of values. On a lark, I did a couple dozen Euler problems in Excel a couple years ago and found it was actually quicker and easier than doing it in a 'real' programming language; at least part of that is that loops and recursions are easy to visualize and debug in Excel because you can just break intermediate calculations into cells.
- rspeer 10y ago
  I have never seen anyone with an Excel workflow that involves exporting to CSV that does not immediately mojibake every non-ASCII character. Yeah, I know there's a well-hidden option to export in tab-separated UTF-16. Nobody uses it. So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.
  - yen223 10y ago
    Excel also messes with CSV values that look like integers but aren't (think serial numbers or IDs), leading to many frustrating days of support.
    - jhbadger 10y ago
      Not to mention the famous mangling of gene names that look like dates. Yes, I know scientists shouldn't be using Excel -- I agree. But I'm a computational biologist. Experimentalists know how to use exactly three programs 1) Microsoft Word 2) Microsoft Excel 3) Microsoft Powerpoint.
      - Neeek 10y ago
        I find myself working with four letter codes to identify wards and facilities at my work, inevitably I have to compare my SQL output to someone's spreadsheet. No excel, I didn't mean March 02, just give me MAR2 like I asked. Drug codes that start with a '.' are another headache, they will be auto formatted as a decimal number. Worse is when they want to put info from a spreadsheet in to the database, no one understands the need for consistent field formatting :'(
        mistermann 10y ago
        Have you tried bringing in with PowerQuery instead? Much more flexible.
        Neeek 10y ago
        I haven't tried pulling Excel -> SQL with PowerQuery yet, only really dabbled in presenting data the other way. Do you know if this works easily with a document that utilises a lot of macros and VBA? Or are we still talking flat CSV.
        mistermann 10y ago
        What are you talking, Excel with VBA is importing/consuming the data, or it is the datasource for something else? I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.
        coroxout 10y ago
    - bigger_cheese 10y ago
      There is also a pretty gnarly bug in excel when it tries to import a CSV with the asci string "ID" as the first two characters. http://superuser.com/questions/210027/why-does-excel-think-csv-files-are-sylk http://superuser.com/questions/210027/why-does-excel-think-c... Very Frustrating
      - nommm-nommm 10y ago
        Hacker news discussion on that bug: https://news.ycombinator.com/item?id=12041210 https://news.ycombinator.com/item?id=12041210
    - intended 10y ago
      Oh this was fun. Excel has issues with numerical figures greater than 13 digits, and can't distinguish from a serial number in a csv or an integer. Importing a small (30k rows of so) set of of tax information thus became hell. Worst part is that I didn't find it till halfway through the migration. I love excel on average, but that was just a painful fight.
  - taneq 10y ago
    > So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places. Whoops, busted. I very rarely deal with localised data so I can get away with ASCII. Excellent point, though, my case doesn't generalise as well as I thought it did.
  - Falkon1313 10y ago
    Often, you can strip the BOM (which isn't supposed to be there to begin with), convert from excel's proprietary character set to UTF-8, and translate the line endings and things may work - if it hasn't decided to randomly change things that weren't dates into dates, strip leading 0's from your zip codes, reformat things that you didn't want reformatted, and otherwise mangle your data. (Nevermind that Excel can't handle dates properly anyway.) What's worse is that many people play with excel and use things like coloring, fonts, and visual layout to distinguish things, and/or combine multiple unrelated chunks into one sheet so that they won't have to switch tabs (which of course makes for a scrambled mess of data). I try to suppress the memories of it taking 36 tries to get a usable CSV from business people (after having given clear instructions in advance and doing postprocessing afterward to clean it up). Excel should never be used for working with data. But it is, so that's job security if you can clean up the messes that it makes.
  - garyclarke27 10y ago
    I use LibreOffice just for this purpose. ie Creating csv files from xls files. Open Excel file in LibreOffice Save as text >> UTF-8 >> quote all text cells. Works perfectly every time, Excel is brilliant for most things but is useless with csv, all kinds of strange bugs will arise.
- orbitingpluto 10y ago
  Exporting from and importing to Excel from CSV becomes a life-saver sometimes. Once upon a time, I had to work on a 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. Writing VBA to export and then letting Cygwin do my work: curling regexing and sorting was the only option.
  - MagnumOpus 10y ago
    > 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. This is implausible. I worked with far bigger datasets, and far more complex calculations, and it didn't take minutes - several seconds, maybe.
    - phonon 10y ago
      "1 GB RAM"
      - dx034 10y ago
        That's the key point here. We recently upgraded all computers to at least 16GB ram solely because of huge Excel spreadsheets that we use.
    - orbitingpluto 10y ago
      I'm talking about a filter, not calculations. And I'm so glad you mystically decided that your calculations were much more complex than whatever I was doing.
- ekingr 10y ago
  Actually, one of the safest option I have found to import/export from Excel without the encoding going wild is to just copy/paste TSV data, and treat it as UTF-8 in your programming language of choice.
- fu9ar 10y ago
  Spreadsheet programs are great for data entry if we take the care before hand to design the data structures well so that they easily export and can be easily analyzed by a computer. Easy stuff like small org budgets can be done with spreadsheets, but masse data analysis simply requires industrial grade tools.

idk im pretty good at exporting to CSV.

by fu9ar 10y ago

idk im pretty good at exporting to CSV.

taneq 10y ago

Dunno why you're getting hammered, this is part of a good Excel workflow. Do what you can using the nice GUI, if you have bulk changes to make that are easier to script than find-and-replace then dump it to CSV, do the grunt work with some command line tools or a script, and re-import it.

smallnamespace 10y ago
While this is a fine workflow if you're more familiar with command line tools than Excel, bulk changes are usually easily doable in Excel with judicious use of cell formulas and copy-pasting of values. On a lark, I did a couple dozen Euler problems in Excel a couple years ago and found it was actually quicker and easier than doing it in a 'real' programming language; at least part of that is that loops and recursions are easy to visualize and debug in Excel because you can just break intermediate calculations into cells.
rspeer 10y ago
I have never seen anyone with an Excel workflow that involves exporting to CSV that does not immediately mojibake every non-ASCII character. Yeah, I know there's a well-hidden option to export in tab-separated UTF-16. Nobody uses it. So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.
- yen223 10y ago
  Excel also messes with CSV values that look like integers but aren't (think serial numbers or IDs), leading to many frustrating days of support.
  - jhbadger 10y ago
    Not to mention the famous mangling of gene names that look like dates. Yes, I know scientists shouldn't be using Excel -- I agree. But I'm a computational biologist. Experimentalists know how to use exactly three programs 1) Microsoft Word 2) Microsoft Excel 3) Microsoft Powerpoint.
    - Neeek 10y ago
      I find myself working with four letter codes to identify wards and facilities at my work, inevitably I have to compare my SQL output to someone's spreadsheet. No excel, I didn't mean March 02, just give me MAR2 like I asked. Drug codes that start with a '.' are another headache, they will be auto formatted as a decimal number. Worse is when they want to put info from a spreadsheet in to the database, no one understands the need for consistent field formatting :'(
      - mistermann 10y ago
        Have you tried bringing in with PowerQuery instead? Much more flexible.
        Neeek 10y ago
        I haven't tried pulling Excel -> SQL with PowerQuery yet, only really dabbled in presenting data the other way. Do you know if this works easily with a document that utilises a lot of macros and VBA? Or are we still talking flat CSV.
        mistermann 10y ago
        What are you talking, Excel with VBA is importing/consuming the data, or it is the datasource for something else? I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.
      - coroxout 10y ago
  - bigger_cheese 10y ago
    There is also a pretty gnarly bug in excel when it tries to import a CSV with the asci string "ID" as the first two characters. http://superuser.com/questions/210027/why-does-excel-think-csv-files-are-sylk http://superuser.com/questions/210027/why-does-excel-think-c... Very Frustrating
    - nommm-nommm 10y ago
      Hacker news discussion on that bug: https://news.ycombinator.com/item?id=12041210 https://news.ycombinator.com/item?id=12041210
  - intended 10y ago
    Oh this was fun. Excel has issues with numerical figures greater than 13 digits, and can't distinguish from a serial number in a csv or an integer. Importing a small (30k rows of so) set of of tax information thus became hell. Worst part is that I didn't find it till halfway through the migration. I love excel on average, but that was just a painful fight.
- taneq 10y ago
  > So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places. Whoops, busted. I very rarely deal with localised data so I can get away with ASCII. Excellent point, though, my case doesn't generalise as well as I thought it did.
- Falkon1313 10y ago
  Often, you can strip the BOM (which isn't supposed to be there to begin with), convert from excel's proprietary character set to UTF-8, and translate the line endings and things may work - if it hasn't decided to randomly change things that weren't dates into dates, strip leading 0's from your zip codes, reformat things that you didn't want reformatted, and otherwise mangle your data. (Nevermind that Excel can't handle dates properly anyway.) What's worse is that many people play with excel and use things like coloring, fonts, and visual layout to distinguish things, and/or combine multiple unrelated chunks into one sheet so that they won't have to switch tabs (which of course makes for a scrambled mess of data). I try to suppress the memories of it taking 36 tries to get a usable CSV from business people (after having given clear instructions in advance and doing postprocessing afterward to clean it up). Excel should never be used for working with data. But it is, so that's job security if you can clean up the messes that it makes.
- garyclarke27 10y ago
  I use LibreOffice just for this purpose. ie Creating csv files from xls files. Open Excel file in LibreOffice Save as text >> UTF-8 >> quote all text cells. Works perfectly every time, Excel is brilliant for most things but is useless with csv, all kinds of strange bugs will arise.
orbitingpluto 10y ago
Exporting from and importing to Excel from CSV becomes a life-saver sometimes. Once upon a time, I had to work on a 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. Writing VBA to export and then letting Cygwin do my work: curling regexing and sorting was the only option.
- MagnumOpus 10y ago
  > 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. This is implausible. I worked with far bigger datasets, and far more complex calculations, and it didn't take minutes - several seconds, maybe.
  - phonon 10y ago
    "1 GB RAM"
    - dx034 10y ago
      That's the key point here. We recently upgraded all computers to at least 16GB ram solely because of huge Excel spreadsheets that we use.
  - orbitingpluto 10y ago
    I'm talking about a filter, not calculations. And I'm so glad you mystically decided that your calculations were much more complex than whatever I was doing.
ekingr 10y ago
Actually, one of the safest option I have found to import/export from Excel without the encoding going wild is to just copy/paste TSV data, and treat it as UTF-8 in your programming language of choice.
fu9ar 10y ago
Spreadsheet programs are great for data entry if we take the care before hand to design the data structures well so that they easily export and can be easily analyzed by a computer. Easy stuff like small org budgets can be done with spreadsheets, but masse data analysis simply requires industrial grade tools.