Forum Discussion

Mark_McCoy's avatar
Mark_McCoy
Copper Contributor
Jun 12, 2020

Convert Text to Columns Wizard

Is it possible to create a macro or some function to quickly or automatically convert text to columns? Every day I receive via email, a csv file with 8000+ pieces of data. 

 

Thanks

Mark

excel

4 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 12, 2020
    Excel already has a lot of options built-in during the import/open function. I'm guessing those tools either don't work or you want something that is more automated. In answer to your question, yes, cell formulas and macros/VBA can do that, but we can't help you without specific data and information.
    • Mark_McCoy's avatar
      Mark_McCoy
      Copper Contributor
      Jun 12, 2020

      mtarler 

      Thank you for your response. I don't know more than the very basic functions of Excel.  Each morning I receive an email with a CSV file attached (Maintenance 1M3D2020_DailyCoolingLog.CSV). I have managed to figure out how to use the convert text to columns wizard to end up with the CSV file (A TEST 1M4D2020.csv)

      I would like to be able to make the conversion much faster than the method described in the help topic. After that I would like to be able to create a chart for each CSV file and also create a chart showing multiple CSV file data.

       

      Mark

      • mtarler's avatar
        mtarler
        Silver Contributor
        Jun 12, 2020

        Mark_McCoyI see what you're talking about, that output file is mal-formed.  The header and the 1st line of data uses comma separated values, then all remaining lines start with a comma and have semi-colon separated values all encased in quotes and even that isn't consistent with the 1st semicolon inside the date-time set of quotes and the rest of the semicolons in their own set of quotes and a whole set of commas.

        Basically I think who or whatever is creating this file has some problems and if possible it should get corrected there.

        That all said, a macro could be created to facilitate import of this (or the corrected) data, but I don't have the spare time right now to do it.  Maybe another helper here does.

Resources