Page 2 of 2 FirstFirst 12
Results 41 to 47 of 47

Thread: [RESOLVED] How to save a xls file as csv via ADO ?

  1. #41
    Addicted Member jj2007's Avatar
    Join Date
    Dec 2015
    Posts
    205

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by dilettante View Post
    Well maybe you can tell us what "correctly" handling CRLF within a column value should look like?

    As far as I know, common CSV doesn't address the matter and it is just illegal.
    Yes and no: CrLf should simply be treated as a new row; technically you could wrap it in quotes, but I doubt you would find something like that in the wild.

    However, you can find embedded linefeeds e.g. in an official UN database available here - starting with footnote 456, Source: Dominican Republic Encuesta Nacional de Hogares..., approximately row 41174, there are 34 of them. I write "approximately" because Excel 2003 doesn't load them correctly.

    Which brings me to a few questions - if they are stupid, apologies:

    If there is no Office installation on a machine, is it still possible to load an Excel file in format *.xls or *.xlsx via ADO or JET?
    If yes, are these applications part of a standard Windows installation?
    If so, why does Microsoft allow everybody to open their proprietary formats?

  2. #42
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: [RESOLVED] How to save a xls file as csv via ADO ?

    You can't "load" it in any meaningful sense. There is no user interface and no macros can be run.

    Jet 4.0 and its Excel 8.0 Installable ISAM are part of Windows so every machine should have them except perhaps for certain Windows Server SKUs where you can omit the WOW64 mechanism. I'm not even sure any recent version allows that any more since it caused no end of problems with nothing gained.

    That can be used to read/write data from/to XLS workbooks but not XLSX format workbooks even if the converter library for Office 2003 is installed. So to process XLSX this way you'd need to either convert to XLS format or else install the ACE 12.0 OLEDB Provider and its own IISAMs available as a separate download for free.

    But yes, you do not need MS Excel installed in order to access XLS workbook data. Even on a fresh install of Windows. It has been this way for a very long time, at least the late 1990s.

  3. #43
    Addicted Member jj2007's Avatar
    Join Date
    Dec 2015
    Posts
    205

    Re: [RESOLVED] How to save a xls file as csv via ADO ?

    Thanks, very interesting ;-)

  4. #44
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,871

    Re: [RESOLVED] How to save a xls file as csv via ADO ?

    Yes and no: CrLf should simply be treated as a new row; technically you could wrap it in quotes, but I doubt you would find something like that in the wild.
    By accident I encountered such a file in the wild yesterday.
    I did a dump of the contacts of my outlook.com account, which is a CSV file.
    The text stored in the "notes" field can wrap multiple lines, which are stored as CRLF in the text file, thus giving new lines for a field.

  5. #45
    Addicted Member jj2007's Avatar
    Join Date
    Dec 2015
    Posts
    205

    Re: [RESOLVED] How to save a xls file as csv via ADO ?

    That's a gross one! Fortunately, I don't have to use Outlook ;-)

  6. #46

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: [RESOLVED] How to save a xls file as csv via ADO ?

    Quote Originally Posted by jj2007 View Post
    That's a gross one! Fortunately, I don't have to use Outlook ;-)
    Such files, which for some are exceptions, are common files at my workplace.
    These files come automatically from a BI tool and it only exports data from another tool where users have the freedom to write anything in certain text boxes from GUI. Thus, in the Description field, we can find all sorts of exotic characters, email messages or explanatory texts copied from other tools. Therefore, the difficulty of parsing as CSV. So, the Excel file itself is not modified by Outlook in any way - in fact, there is no Outlook here, but a local mail server that receives MIME-formatted emails. Fortunately, there are some experienced guys in the forum who have managed to offer a robust solution that I have not met it elsewhere. Note that conversion time is very close to Python, but Python uses a class that reads directly from the excel file archive. We can not say exactly, but perhaps in our case a bottleneck could be the ADO itself if we consider that in my tests the ADO reading time is on average about 70% of the total processing time ...
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  7. #47
    Addicted Member jj2007's Avatar
    Join Date
    Dec 2015
    Posts
    205

    Re: [RESOLVED] How to save a xls file as csv via ADO ?

    Why don't you use tab-delimited instead? It's much easier to handle than csv. Of course, in theory a user could insert a tab character in a field, but that could be substituted with a space before storing it. Which would be a good thing anyway, replacing tabs with \t and crlf's with \n before storing the stuff in a cell.

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width