#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171

    Import data from notepad to mysql


    What would be the best way to import these into a sub_cat table.

    I did think about something like this below but I am not sure about

    1 - The red part:
    2 - The synthax in general (where to assign the columns)

    LOAD DATA INFILE 'data.txt' INTO TABLE sub_cat FIELDS TERMINATED BY '\r\n' ;

    ----------------------------------------------------------------------

    The table has 3 columns:
    id, category_id, title

    Here is my data:

    Data.txt
    Air Condition Installation
    Air Condition Supply
    Appliances
    Architects / Drafting Services / Building Design
    Architectural Decorative Moulding
    Bank / Building Society
    Bathroom Supply
    Bin Hire
    Brick and Block Supply
    Brick Layers
    Builders
    Building Bodies
    Carpenters
    Certification Services
    Cleaning Companies
    Color Concrete / Curb / Edging Specialist
    Concrete Contractors
    Concrete Pumping Services
    Concrete Supply
    Crane Hire
    Door Installation
    Door Supply
    Drafting Services
    Drawing and Office Supplies
    Drilling and Sawing
    Driveway and Path
    Electrical Contractors
    Electrical Supply
    Engineers
    Fascia / Gutter Installer
    Fasteners Tools and Equipment Supply
    Fencing
    Floor Covering / Carpet Supply
    Floor Sheeting Installation
    Floor Sheeting Supply
    Forklifts Hire / Sales / Service
    Garage Door Installation
    Garage Door Supply
    Gas and Welding Supply
    Gas Fitter
    Gate / Fencing
    General Building Hire
    Glass and Aluminium
    Hardware Supply
    Insulation
    Insurance Finance / Legal
    Interior Design
    Kitchen & Cabinet Makers
    Landscaping / Rock Specialist
    Light Fitting Supply
    Lightweight Building Systems
    Paint Supply
    Painters
    Pest Control
    Plaster Board Supply
    Plastering
    Plumber / Drainer
    Plumbing Supply
    Pool Construction
    Roof Installer
    Roof Supply
    Scaffolding
    Security System
    Shower Screen and Mirrors
    Solicitors
    Solid Plastering / Texture
    Stairs / Balustrade
    Steel Structure Building System
    Steel Suppliers
    Surveyors
    Telecommunication Contractors
    Texture Supply
    Tile Supply
    Tiler
    Timber Structure Building System
    Timber Supplier
    Treatment System and Tanks
    Truck Hire and Transport
    Wall Cladding
    Water Proofing
    Windows and Door Supply
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    that's it?

    just those lines?

    easiest way is to run this query --
    Code:
    INSERT
      INTO sub_cat
         ( title )
    VALUES
     ( 'Air Condition Installation' )
    ,( 'Air Condition Supply' )
    ,( 'Appliances' )
    ,( 'Architects / Drafting Services / Building Design' )
    ,( 'Architectural Decorative Moulding' )
    ,( 'Bank / Building Society' )
    ,( 'Bathroom Supply' )
    ,( 'Bin Hire' )
    ,( 'Brick and Block Supply' )
    ,( 'Brick Layers' )
    ,( 'Builders' )
    ,( 'Building Bodies' )
    ,( 'Carpenters' )
    ,( 'Certification Services' )
    ,( 'Cleaning Companies' )
    ,( 'Color Concrete / Curb / Edging Specialist' )
    ,( 'Concrete Contractors' )
    ,( 'Concrete Pumping Services' )
    ,( 'Concrete Supply' )
    ,( 'Crane Hire' )
    ,( 'Door Installation' )
    ,( 'Door Supply' )
    ,( 'Drafting Services' )
    ,( 'Drawing and Office Supplies' )
    ,( 'Drilling and Sawing' )
    ,( 'Driveway and Path' )
    ,( 'Electrical Contractors' )
    ,( 'Electrical Supply' )
    ,( 'Engineers' )
    ,( 'Fascia / Gutter Installer' )
    ,( 'Fasteners Tools and Equipment Supply' )
    ,( 'Fencing' )
    ,( 'Floor Covering / Carpet Supply' )
    ,( 'Floor Sheeting Installation' )
    ,( 'Floor Sheeting Supply' )
    ,( 'Forklifts Hire / Sales / Service' )
    ,( 'Garage Door Installation' )
    ,( 'Garage Door Supply' )
    ,( 'Gas and Welding Supply' )
    ,( 'Gas Fitter' )
    ,( 'Gate / Fencing' )
    ,( 'General Building Hire' )
    ,( 'Glass and Aluminium' )
    ,( 'Hardware Supply' )
    ,( 'Insulation' )
    ,( 'Insurance Finance / Legal' )
    ,( 'Interior Design' )
    ,( 'Kitchen & Cabinet Makers' )
    ,( 'Landscaping / Rock Specialist' )
    ,( 'Light Fitting Supply' )
    ,( 'Lightweight Building Systems' )
    ,( 'Paint Supply' )
    ,( 'Painters' )
    ,( 'Pest Control' )
    ,( 'Plaster Board Supply' )
    ,( 'Plastering' )
    ,( 'Plumber / Drainer' )
    ,( 'Plumbing Supply' )
    ,( 'Pool Construction' )
    ,( 'Roof Installer' )
    ,( 'Roof Supply' )
    ,( 'Scaffolding' )
    ,( 'Security System' )
    ,( 'Shower Screen and Mirrors' )
    ,( 'Solicitors' )
    ,( 'Solid Plastering / Texture' )
    ,( 'Stairs / Balustrade' )
    ,( 'Steel Structure Building System' )
    ,( 'Steel Suppliers' )
    ,( 'Surveyors' )
    ,( 'Telecommunication Contractors' )
    ,( 'Texture Supply' )
    ,( 'Tile Supply' )
    ,( 'Tiler' )
    ,( 'Timber Structure Building System' )
    ,( 'Timber Supplier' )
    ,( 'Treatment System and Tanks' )
    ,( 'Truck Hire and Transport' )
    ,( 'Wall Cladding' )
    ,( 'Water Proofing' )
    ,( 'Windows and Door Supply ')
    ;
    ah, the joys of using a good text editor

    how were you planning to assign the category_id values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by r937
    that's it?

    just those lines?
    Thank you for your reply (I used what you wrote for now).

    In general I wanted to learn what is the syntax to importing data from a .txt or an excel file using LOAD DATA INFILE.

    I want to learn the formula so I can change it based on my data type/format. So I really like to discuss this:

    LOAD DATA INFILE 'data.txt' INTO TABLE sub_cat FIELDS TERMINATED BY '\r\n' ;

    ------------------------------------------------------------------------

    Lets say my .txt file has this structure:

    sports, tennis
    cars, toyota
    food,soup
    .
    .
    .

    Then how can I use something like this:

    LOAD DATA INFILE 'data.txt' INTO TABLE sub_cat (category, title) FIELDS TERMINATED BY , '\r\n' ;


    ------------------------------------------------------------------------
    And in another scenario lets say my file is an excel file like this:

    sports | tennis
    cars | toyota
    food |soup
    .
    .
    .

    Then I look for something like this:

    LOAD DATA INFILE 'data.xls' INTO TABLE sub_cat (category, title) FIELDS TERMINATED BY excel column '\r\n' ;

IMN logo majestic logo threadwatch logo seochat tools logo