Results 1 to 5 of 5

Thread: Importing large text files into MYSQL

  1. #1
    Join Date
    Oct 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Importing large text files into MYSQL

    HELP!!! I am trying to import unix files into the database as blob's and having alot of problems. You would have to know some about unix to understand and see what I'm doing. Basically, I'm putting configurations into:
    The file is for one entire record... but almost all fields are multiple lines.

    ie., configurations
    hostname,
    passwd_file,
    hosts_file,
    crontab,
    packages to name a few. The file has'

    fields enclosed by "field"
    fields terminated by ,


    The text file as an example is:

    "bugsbunny",

    "Mon Oct 2 14:48:16 CDT 2006",

    " Solaris 7 1/00 s9s_u5wos_08b SPARC
    Copyright 2000 Sun Microsystems, Inc. All Rights Reserved.
    Use is subject to license terms.
    Assembled 21 November 2000",

    "Nodename : bugsbunny
    OS Name : Sun
    OS Level : 5.7
    Patch Level : Generic_92933447-07
    Ip Address(es) : 192.929.926.999
    Default router : 192.929.926.4
    Netmasks : 299.299.292.0
    Name Server : .....
    nameserver 192.929.226.4
    search my.email.com
    Memory : 256 MB
    CPU : 1 x 550 Mhz
    Disks : .....
    Model: ST380011A
    Revision: 3.04
    Serial No: 5BUG11GA
    Size: 80.02GB <80023879680 bytes>
    Vendor: MAT****A
    Product: DVD-ROM SR-8589
    Revision: 8Z14
    Serial No:
    Size: 0.00GB <0 bytes> ",
    "system boot Oct 2 08:34
    UPTIME
    2:48pm up 6:13, 3 users, load average: 0.10, 0.13, 0.12",
    "The System-MacAddress=0:3:fc:8v:03:01
    The System- kernel-type=64-bit sparcv7 kernel modules",
    "test-args: data not available.
    diag-passes=1
    pci-probe-list=c,8,d,5,13
    local-mac-address?=false
    fcode-debug?=false
    silent-mode?=false
    scsi-initiator-id=7
    oem-logo: data not available.
    oem-logo?=false
    oem-banner=My Use Only!
    oem-banner?=true
    ansi-terminal?=true
    screen-#columns=80
    screen-#rows=34
    ttyb-rts-dtr-off=false
    ttyb-ignore-cd=true
    ttya-rts-dtr-off=false
    ttya-ignore-cd=true
    ttyb-mode=9600,8,n,1,-
    ttya-mode=9600,8,n,1,-
    output-device=screen
    input-device=keyboard
    load-base=16384
    auto-boot?=false
    boot-command=boot
    diag-file: data not available.
    diag-device=disk net
    boot-file: data not available.
    boot-device=disk net
    use-nvramrc?=true
    nvramrc=devalias CDROM /pci@1f,0/ide@d/cdrom
    security-mode=command
    security-password: data not available.
    security-#badlogins=8
    diag-script=none
    diag-level=max
    diag-switch?=false
    error-reset-recovery=boot",
    "STORAGE REPORT
    Filesystem size used avail capacity Mounted on
    /dev/dsk/c0t0d0s0 1.4G 256M 1.1G 19% /
    /dev/dsk/c0t0d0s4 9.8G 2.8G 6.9G 30% /usr
    /dev/dsk/c0t0d0s1 1.4G 1.1G 277M 81% /var
    /dev/dsk/c0t0d0s5 9.4G 254M 9.0G 3% /opt
    /dev/dsk/c0t0d0s7 9.8G 1.6G 8.1G 17% /export
    Installed (physical).: 80.0239GB
    Mounted..............: 33.4904GB
    Used.................: 6.36512GB
    Avail................: 26.7005GB
    Swap.................: 1.0240GB",
    "9327wbe4",
    "FW5678j9jj",
    "Uob32211",
    "Location - Myhome, Workstation @ Home
    Location Room: Office
    Location Rack: 1",
    "POC Name: John Doe
    POC Phone: 449-739-9448
    POC Email: jdoe@my.email.com",
    "Application: Workstation
    Application DBA:
    Application Customer: John Doe 449-739-9448 jdoe@myemail.com",
    "Print out list of all printers available from this server:
    No Printers Set Up on this Server",
    "SunOS 5.7 Generic_912331-01 sun4u sparc SUNW,Sun-Blade-100",
    "Memory size: 256 Megabytes",
    "0 on-line since 10/02/2006 08:34:59",
    "System Crontab Information:
    #ident @(#)root 1.20 01/11/06 SMI
    #
    # The root crontab should be used to perform accounting data collection.
    #
    # The rtc command is run to adjust the real time clock if and when
    # daylight savings time changes.
    #
    10 3 * * * /usr/sbin/logadm
    15 3 * * 0 /usr/lib/fs/nfs/nfsfind
    1 2 * * * -x /usr/sbin/rtc && /usr/sbin/rtc -c > /dev/null 2>&1
    30 3 * * * -x /usr/lib/gss/gsscred_clean && /usr/lib/gss/gsscred_clean
    "Link Autospeed Status Speed Mode Ethernet Address
    ----- --------- ------ ------ ----- ------------------
    eri0 off Up 100MB FDX 0:5:na:7u:93:ri",
    "nameserver 929.929.926.4
    search my.mail.com",
    "929.929.926.4",
    "##### Java #######:
    java version 3.9.2_067
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_02-b03)
    Java HotSpot(TM) Client VM (build 1.4.2_02-b03, mixed mode)
    ----->>> Java was found in the following directories: <<-----
    /usr/mnt/library/User/usr/j2se/bin/java
    ##### gzip #######:
    gzip 6.8.5-patch.1
    (2000-01-08)
    ----->>> gzip was found in the following directories: <<-----
    /usr/mnt/library/User/usr/bin/gzip
    ##### Perl #######:
    This is perl, v5.6.1 built for sun4-solaris-64int
    ----->>> Perl was found in the following directories: <<-----
    /usr/perl5/5.6.1/bin/perl
    "#### metadb ####:
    metadb: cobra: there are no existing databases
    #### metastat ####:
    metastat: cobra: there are no existing databases",
    "root is running:.....
    1 instance(s) of >> /bin/ps -ef
    1 instance(s)

    1 instance(s) of >> /usr/local/mysql/mysql/libexec/mysqld --based
    1 instance(s) of >> /usr/openwin/bin/Xsun :0 -nobanner -auth /var
    mysql is running:.....
    1 instance(s) of >> -ksh
    1 instance(s) of >> mysql -u root -p
    End of program: proclist",
    "Summary of processes running by each users ->
    USER TOTAL PROCESS
    -------------------------------
    root 37
    jdoe 8
    ..................................................
    Detail report of users process grouped on STIME ->
    USER STIME PROCESS COUNT
    ----------------------------------------------
    mysql current 2
    jdoe current 8
    root current 37",
    "#device device mount FS fsck mount mount
    #to mount to fsck point type pass at boot options
    #
    fd - /dev/fd fd - no -
    /proc - /proc proc - no -
    /dev/dsk/c0t0d0s0 /dev/rdsk/c0t0d0s0 / ufs 1 no nodfratime,logging
    /dev/dsk/c0t0d0s1 /dev/rdsk/c0t0d0s1 /var ufs 1 no nodfratime,logging
    /dev/dsk/c0t0d0s3 - - swap - no -
    "# Place share(1M) commands here for automatic execution
    # on entering init state 3.
    #
    * set:
    *
    * Set an integer variable in the kernel or a module to a new value.
    * This facility should be used with caution. See system(4).
    *
    * Examples:
    *
    * To set variables in 'unix': module named 'test_module'
    *
    * set test_module:debug = 0x13
    set c2audit:audit_load = 1
    set sq_max_size=0
    set lwp_default_stksize=0x4000
    set rpcmod:svc_default_stksize=0x4000
    set red_minavail=7000
    set noexec_user_stack=1
    set noexec_user_stack_log=1",
    "127.0.0.1 localhost
    192.929.926.999 bugsbunny.my.mail.com bugsbunny loghost
    192.929.926.4 looneytunes.my.mail.com
    192.929.926.121.116 daffy.my.mail.com daffy",
    "# Allow access to these services from given hosts
    ALL:127.0.0.1
    sshd:192.929.925.4
    "#Deny all services from all hosts
    ALL:ALL:banners /etc/banners",
    "root:x:0:1:Super-User:/rootdir:/sbin/sh
    jdoe:x:1000:21:John Doe:/export/jdoe:/usr/bin/ksh",
    " PKGINST: HPNPL
    NAME: JetDirect Printer Installer for Unix
    CATEGORY: application
    ARCH: sparc
    VERSION: E.10.34
    BASEDIR: /
    VENDOR: HP
    DESC: HP JetDirect Printer Installer for Unix package
    PSTAMP: snbld26m28151818
    INSTDATE: May 09 2006 10:26
    STATUS: completely installed
    FILES: 237 installed pathnames
    2 shared pathnames
    19 directories
    176 executables
    8688 blocks used (approx)

    PKGINST: SFWesd
    NAME: esound - The Enlightened Sound Daemon
    CATEGORY: system
    ARCH: sparc
    VERSION: 0.2.18,REV=2002.03.27.20.41
    BASEDIR: /opt
    VENDOR: http://www.tux.org/~ricdude/EsounD.html
    DESC: The Enlightened Sound Daemon
    PSTAMP: freeware20020327215217
    INSTDATE: Aug 31 2004 10:11
    HOTLINE: Please contact the owners of this software
    STATUS: completely installed
    FILES: 24 installed pathnames
    7 shared pathnames
    7 directories
    12 executables
    610 blocks used (approx)

    PKGINST: SUNW1394x
    NAME: Sun IEEE1394 Framework (64-bit)
    CATEGORY: system
    ARCH: sparc
    VERSION: 11.9.0,REV=2002.04.06.15.27
    BASEDIR: /
    VENDOR: Sun Microsystems, Inc.
    DESC: IEEE1394 Framework and OpenHCI Driver (64-bit)
    PSTAMP: crash20020406153716
    INSTDATE: Aug 10 2004 12:01
    HOTLINE: Please contact your local service provider
    STATUS: completely installed
    FILES: 8 installed pathnames
    5 shared pathnames
    5 directories
    2 executables
    1314 blocks used (approx)

    PKGINST: SUNWbip
    NAME: Basic IP commands (Usr)
    CATEGORY: system
    ARCH: sparc
    VERSION: 11.9.0,REV=2002.04.06.15.27
    BASEDIR: /
    VENDOR: Sun Microsystems, Inc.
    DESC: Basic IP commands (/usr/sbin/ping, /bin/ftp)
    PSTAMP: leo20040930133721
    INSTDATE: Dec 14 2004 17:09
    HOTLINE: Please contact your local service provider
    STATUS: completely installed
    FILES: 5 installed pathnames
    3 shared pathnames
    3 directories
    2 executables
    1 setuid/setgid executables
    252 blocks used (approx)",

  2. #2
    Join Date
    Oct 2006
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    I havn't used BLOB yet.. but I know it's BINARY... perhaps its seeing the txt file and screwing that up.. did you try to store any other file, just to make sure the script works etc...

    If it does work on a different file, then try zipping the txt one to see if it being an actual text file is messing it up...

    Sorry couldn't be of more help...

  3. #3
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    longtext, perhaps. or Long Text, or whatever the syntax on that is.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  4. #4
    Join Date
    Oct 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by unixmonk View Post
    "SunOS 5.7 Generic_912331-01 sun4u sparc SUNW, Sun Blade 100",
    "Memory size: 256 Megabytes",
    "0 on-line since 10/02/2006 08:34:59",
    Which version of mysql are you running on this server?

  5. #5
    Join Date
    Oct 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up load into text, medtext and longtext

    version 5.0.24

    I have finally managed to import the data using php and a parse script I found. I figured out last week that if I

    load data infile '/tmp/bugsbunny.txt'
    into table looneytunes
    fields terminated by ","
    lines terminated by "^";

    that this works most and keeps the formatting for the paragraphs.

    However, there are the few times that it doesn't --

    haven't figured out why yet.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •