1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

CSV file. Frustrated!

Discussion in 'BlackHat Lounge' started by Masterptc, Sep 29, 2014.

  1. Masterptc

    Masterptc Regular Member

    Joined:
    Aug 9, 2011
    Messages:
    419
    Likes Received:
    604
    Home Page:
    Im very frustrated and i cant figure this out, maybe someone here can help me and end my frustration and earn a Thanks. :)
    Im scraping shipping info from a website, the shipping info isnt being separated correctly because there is no deliminator after "CA" as shown in the pic below. How the hek do i get it to add a deliminator without doing it manually?

    [​IMG]
     
  2. madandi

    madandi Junior Member

    Joined:
    Aug 19, 2014
    Messages:
    120
    Likes Received:
    34
    It depends on how your CSV file is setup in the first place, but by the looks of what you have there.. I'd import it as is, then after you've got your data in Calc:

    Highlight the column you want to split
    Goto the "Data" menu and select "Text to Columns"
    On the popup window, select the "Space" option in the Separated by section
    And you're done.
     
  3. smlover

    smlover Junior Member

    Joined:
    Mar 4, 2011
    Messages:
    111
    Likes Received:
    17
    Home Page:
    copy all the "CA Zipcode" things
    paste in notepad
    press tab once, block the tab and ctrl + x
    Ctrl+h
    replace the space with tab [ctrl+v] (it's in the clipboard)
    copy paste back to excel
    now it's separated
     
  4. Masterptc

    Masterptc Regular Member

    Joined:
    Aug 9, 2011
    Messages:
    419
    Likes Received:
    604
    Home Page:
    It would be easier to add a comma to it by editing in a notepad file, is there a way i can create a custom deliminator for all 50+ US states?
     
  5. madandi

    madandi Junior Member

    Joined:
    Aug 19, 2014
    Messages:
    120
    Likes Received:
    34
    If the name, address and city are all coming out in Column A, and the state and zip in Column B (like in your screenshot), then following the steps I suggested above will have that data separated in 2 seconds flat without the need to go back and forth between notepad, and trying to figure out a tricky way to insert a comma after 50 different states.
     
  6. Masterptc

    Masterptc Regular Member

    Joined:
    Aug 9, 2011
    Messages:
    419
    Likes Received:
    604
    Home Page:
    Except i scrape new data everyday, and im trying to have everything run without me going back into the files and making changes.