1

I'm using Excel on Office 365.

Presently when I open a csv file which is opened with excel, no import wizard is shown and the separator and encoding are incorrect.

Contrastingly, if I open the csv as a text file and paste it into excel, I can then use the paste options to use the text import wizard and set the correct delimiter for the import.

How can I open a csv file in Excel and have text import options applied to it, either at import or simply & immediately after the fact?

  • In windows versions of Excel, you would perform an Import and not an Open. I don't have the Mac version so cannot reliably answer. But see Microsoft support for Import data from a CSV, HTML, or text file. It would seem to provide the proper guidance for your system. – Ron Rosenfeld Oct 27 '17 at 2:17
1

So i had a similar problem,

I created a macro with my desired settings, see below:

Sub OpenSpool()
    Workbooks.OpenText Filename:="C:\Downloads\SPOOL_PRHN", Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
        3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
End Sub

You would need to change the file location,

To get your desired settings, copy it in from the word file, record a macro and use the text to columns function. Once done look in the macro to get your settings and copy into the above with you file location etc.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.