1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: txt to csv without losing formatting

Discussion in 'Business Applications' started by Mike McBain, Feb 3, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    426
    G'day Magicians

    I have a large txt file every day similar to the sample below except that using Fixed Sys in the txt file it looks to be formatted into columns right across...

    CR5 16:30 #6 x221 Wisteria Lane (1) N Perryman 9-2
    VR7 16:35 #2 1217 Zendi (2) M Zahra 5-1
    AR7 16:40 #2 4x0x Odysseus (3) D Holland 7-2
    XR8 16:56 #3 x333 Panetimos (10) N Hall (a1.5) 10-1
    QR6 17:04 #11 54x6 Tycoon Rose (9) N Thomas (a2) 9-2
    CR6 17:08 #1 3333 Shy Prince (4) S Pollard 5-1
    NR9 17:08 #9 5822 Sunman (2) J Galea *28-10

    I want to have the info in a csv file with each item in its own column but no matter how hard I try the right hand column formats as dates like 09- Feb & 05-Jan & 07-Feb etc etc.

    I would be eternally grateful if someone could guide me towards the solution.

    With thanks

    Mike
    Tasmania
     
  2. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    How are you trying to put it into Excel??

    If you run Excel and use File > Open on your Text file, a Text Import Wizard will start to guide you through the process. You use it to define the size of the columns as well as the format.
     
  3. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    426
    WhitPhil

    Yes I do that OK but how can I set an option that will open the file with the right hand set of text numbers including some in the format 10-1 not showing in the excel or csv file as 10-Jan?

    Mike.
     
  4. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    There is no "automatic" way to have this happen, without writing a macro, I believe.

    But, when you DO use the Wizard you CAN change the format for that column. Yes?

    But since you have to do this every day, you want it to just happen??
     
  5. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    426
    WhitPhil

    I don't mind doing it with a macro - but

    WHAT do I change the column format TO to prevent 11-2 showing as 11-Feb???

    Mike.
     
  6. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Mike:

    Well, I personally am not a Macro man, so take my comments in that regard.

    I would have thought that somewhere in the macro, you would be able to do that definition.

    Or, since it is the same format everytime, you "should" be able to turn on Macro Recording, then go through the exercise with the wisard to define the column widths and formats, then stop recording, and the result should be what you are looking for?
     
  7. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    426
    WhitPhil

    I'm sorry but perhaps I am not explaining myself very well?

    My problem is that I cannot find a "format" that will let me paste 11-2 as it shows here OR after I paste will reconvert it back from 11-Feb to 11-2.

    Mike.
     
  8. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Mike:

    I'm not sure what you are pasting??

    If you do a File Open on your text file, the wizard leads you through the process.

    And, if you are copy/pasting the text file into the sheet and then using Data > Text to Columns, that wizard also leads you through the process.

    SO, if you are in fact using one of these wizards, when it gets to the point that all the columns have been shown and you are about to finish, click on the title of the column where the "11-2" is (in my test it was called General) and at the top are choices of formats. Choose text and that data will be brought in as text. IE the way it looks.
     
  9. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    426
    WhitPhil

    Eureka! I've finally got it and I'm forever in your debt and thanks for your patience.

    Mike.
     
  10. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Mike:

    Glad you got it.

    That's the problem, sometimes with a forum. One "assumes" that the other understands the problem, and the other "assumes" that the solution is understood.

    I guess when we both started to type slower, .......

    :)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/540986

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice