As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Excel question

HeirHeir Ausitn, TXRegistered User regular
edited September 2009 in Help / Advice Forum
I have a text file.

It is filled with thousands of rows of data that follows this format:

last name, first name, email, passkey, etc, etc

Each category is separated by a comma.

I need to separate each category into a different column when I open it in excel.

Is there a way to do so?

I apologize, I'm terrible with Excel. Thanks in advance.

Oh, and also...each line is enclosed in ""

camo_sig2.png
Heir on

Posts

  • Options
    rfaliasrfalias Registered User regular
    edited September 2009
    Heir wrote: »
    I have a text file.

    It is filled with thousands of rows of data that follows this format:

    last name, first name, email, passkey, etc, etc

    Each category is separated by a comma.

    I need to separate each category into a different column when I open it in excel.

    Is there a way to do so?

    I apologize, I'm terrible with Excel. Thanks in advance.

    Oh, and also...each line is enclosed in ""

    Open it as a .csv, or just change the extension to .csv, should work fine.

    rfalias on
  • Options
    HeirHeir Ausitn, TXRegistered User regular
    edited September 2009
    Didn't seem to do it. :(

    Heir on
    camo_sig2.png
  • Options
    rfaliasrfalias Registered User regular
    edited September 2009
    Heir wrote: »
    Didn't seem to do it. :(

    What does it look like in excel when it opens up?

    rfalias on
  • Options
    midgetspymidgetspy Registered User regular
    edited September 2009
    You could also use any decent text editor to search/replace "," for " " or "\t" (tab) and then just paste it in.

    midgetspy on
  • Options
    rfaliasrfalias Registered User regular
    edited September 2009
    Actually, just go into excel, do file->open.
    Change the file type to 'Text File'
    Find that .txt and open it.
    On the next dialog, click the delimited radio button, then click the comma separator.

    rfalias on
  • Options
    PracticalProblemSolverPracticalProblemSolver Registered User regular
    edited September 2009
    so it's like:

    "john doe, blah blah, xlkjsdfjlsd, etc etc"

    ???

    You probably just need to delete the quotes.

    PracticalProblemSolver on
  • Options
    DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited September 2009
    Heir wrote: »

    Oh, and also...each line is enclosed in ""

    Practical is right, this is what is fucking you up. Find/replace quotemarks with nothing and you should be able to open it up just fine.

    How the hell did they get it to export like that anyway?

    Deebaser on
  • Options
    HeirHeir Ausitn, TXRegistered User regular
    edited September 2009
    It's just a weird program we have...it wrapped each line in quotes.

    I ended up just writing a batch file that scrubbed out the quotes for me. Then used excel to import it looking for commas as the delimiters.

    Thanks

    Heir on
    camo_sig2.png
  • Options
    rfaliasrfalias Registered User regular
    edited September 2009
    Shouldn't have even needed a batch, notepad and Ctrl+h and replace quotes with nothing.
    That aside; excel 2003 does differently than 2k. 2k3 treats quotes as a whole string thus puts it all into one column, but 2000 lets you choose the delimiter and ignores quotes.

    Peculiar.

    rfalias on
  • Options
    Sir CarcassSir Carcass I have been shown the end of my world Round Rock, TXRegistered User regular
    edited September 2009
    There's also Data -> Text to Columns

    Sir Carcass on
  • Options
    CorvusCorvus . VancouverRegistered User regular
    edited September 2009
    There's also Data -> Text to Columns

    This is what you need. Just select your data, fire up Text to Columns, tell it where you want to split it (on the commas) and it will separate it all into columns for you.

    Corvus on
    :so_raven:
Sign In or Register to comment.