Import question

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Joost

    Import question

    Hello,

    I am using the LOAD DATA INFILE command to import a text file into
    mysql.

    However, there is much more text in the file than needed in my tables.
    How can I make a selection from the text line. For example position 5 -
    10 in field A, position 20-23 in field B, etc

    Regards,

    Joost.


  • Jeff North

    #2
    Re: Import question

    On Tue, 31 May 2005 10:21:47 +0200, in mailing.databas e.mysql "Joost"
    <a@b.c> wrote:
    [color=blue]
    >| Hello,
    >|
    >| I am using the LOAD DATA INFILE command to import a text file into
    >| mysql.
    >|
    >| However, there is much more text in the file than needed in my tables.
    >| How can I make a selection from the text line. For example position 5 -
    >| 10 in field A, position 20-23 in field B, etc[/color]

    Simple answer: you can't.

    What you need to do is import your data into a temporary table.
    Massage the data the way you want it within this table.
    Update/Replace/Insert you temp data fields into your normal table.
    ---------------------------------------------------------------
    jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
    ---------------------------------------------------------------

    Comment

    • Joost

      #3
      Re: Import question

      Jeff North formuleerde de vraag :[color=blue]
      > On Tue, 31 May 2005 10:21:47 +0200, in mailing.databas e.mysql "Joost"
      > <a@b.c> wrote:
      >[color=green][color=darkred]
      >>> Hello,
      >>>
      >>> I am using the LOAD DATA INFILE command to import a text file into
      >>> mysql.
      >>>
      >>> However, there is much more text in the file than needed in my tables.
      >>> How can I make a selection from the text line. For example position 5 -
      >>> 10 in field A, position 20-23 in field B, etc[/color][/color]
      >
      > Simple answer: you can't.
      >
      > What you need to do is import your data into a temporary table.
      > Massage the data the way you want it within this table.
      > Update/Replace/Insert you temp data fields into your normal table.
      > ---------------------------------------------------------------
      > jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
      > ---------------------------------------------------------------[/color]

      I was afraid that someone was going to say this...

      Anyway, thank you for confirming this.

      Joost.

      PS: I like the remove your pants part. :D Didn't see that one before...


      Comment

      • Jeff North

        #4
        Re: Import question

        On Tue, 31 May 2005 19:17:47 +0200, in mailing.databas e.mysql "Joost"
        <a@b.c> wrote:
        [color=blue]
        >| Jeff North formuleerde de vraag :
        >| > On Tue, 31 May 2005 10:21:47 +0200, in mailing.databas e.mysql "Joost"
        >| > <a@b.c> wrote:
        >| >
        >| >>> Hello,
        >| >>>
        >| >>> I am using the LOAD DATA INFILE command to import a text file into
        >| >>> mysql.
        >| >>>
        >| >>> However, there is much more text in the file than needed in my tables.
        >| >>> How can I make a selection from the text line. For example position 5 -
        >| >>> 10 in field A, position 20-23 in field B, etc
        >| >
        >| > Simple answer: you can't.
        >| >
        >| > What you need to do is import your data into a temporary table.
        >| > Massage the data the way you want it within this table.
        >| > Update/Replace/Insert you temp data fields into your normal table.
        >|
        >| I was afraid that someone was going to say this...
        >|
        >| Anyway, thank you for confirming this.[/color]

        I always use temp tables when importing data. Why?
        1. Because you can bet there will be one or two records that are
        incorrect and will not import into the main tables correctly.

        2. if the data is corrupt then you simply clear out the temp table and
        start again. Your main data isn't affected in anyway.

        3. is that it is quicker to apply the 'rules' to this small data set
        that manipulating the larger set.

        4. data can be easily split out to as many tables as required.
        ---------------------------------------------------------------
        jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
        ---------------------------------------------------------------

        Comment

        Working...