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
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.

      • Mark_McCoy's avatar
        Mark_McCoy
        Copper Contributor
        Jun 15, 2020

        mtarler 

        Thanks you for pointing out the mal-formed issues. I will go back to the programmer and have him address the issues.

         

        Mark

Resources