Results 1 to 7 of 7

Thread: Big Problem

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unhappy Unanswered: Big Problem

    I'm trying to copy a textfile into an access database. The statistics in the textfile are arranged as follow;

    Code:
    CELLTCH    FTCALLS  FTCONGP  FMHTIME FTRAFLV FSUCALS  FDRTCH   FTIME
    SUAK01C         6416         0.3    31.6        2.3        99.2        1.2    3348
    ZEAZ01A         792           0      23.4        0.2        97.6        1.9    302
    I've made alot of afford but things are not working, can anyone help???

    Metro
    Last edited by loquin; 02-11-08 at 13:20. Reason: added code tags to properly display spacing

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Take a look at this File I/O Tutorial and the ADO Tutorial at our VB sister-site, Xtreme VB Talk.

    The third or fourth post in the File I/O tutorial discusses fast I/O, which is what I like to use, unless the file is too large to comfortably fit into RAM (> 30-50 MB or so.)

    With this approach, you will first read the entire file into a single string buffer. Then, use VB's SPLIT function to break down this string buffer into a dynamic array of string, one array element for each line of your file.

    Now, you can iterate through your array of string, parsing each line and inserting the data into your database. One effective approach is to define a second dynamic array of string, and SPLIT each line into this array. Then, you can loop through the array of fields, inserting the data into the database.

    The final step is the actual data insertion into Access. Ref the ADO Tutorial. Note, that since there's no reason to read the database ijust to insert new records, you won't need a recordset at all. Just issue a series of SQL INSERT statements, using the ADO Connection.Execute method.

    Now, assuming that your Access table is named STATS, and it consists of an autonumber ID field, and data fields CELLTCH, FTCALLS, FTCONGP, FMHTIME, FTRAFLV, FSUCALS, FDRTCH, and FTIME with all fields except CELLTCH numeric (it's a text field), the SQL statement you construct for the first data line above would be a string :

    Quote Originally Posted by SQL
    INSERT INTO STATS (CELLTCH, FTCALLS, FTCONGP, FMHTIME, FTRAFLV, FSUCALS, FDRTCH, FTIME) VALUES ('SUAK01C', 6416, 0.3, 31.6, 2.3, 99.2, 1.2, 3348)
    You will (pseudo code)
    • Open the ADO connection and connect to the database
    • Open the source file
    • Read the data into the string buffer
    • Close the Source File
    • SPLIT the string buffer to the File Array
    • Loop using a For Loop: UBound(ArrayName) is the upper limit for the array
      1. Build a SQL Insert statement (SPLIT the line into fields, Construct the SQL Statement) using the loop counter as the array index location
      2. Execute the Statement.
      3. Loop until you're through the array. (NEXT N)
    • Close and Release the ADO Connection


    If you haven't done a lot of VB programming, the VB Standards and Practices Tutorial may prove useful as well.
    Last edited by loquin; 02-07-08 at 15:29.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    BTW. IF the machine where you are using importing the data has a copy of MS Access installed, Access will import the data with the File-Get External Data-Import utility.

    Just specify a space delimited text file, the first row containing the field names, and specify long integer and double as the numeric data types as appropriate.

    Also, let Access add a primary key.


    Once you've specified the import specifications, save them (use the advanced tab on the import utility form). You can then use automation the process, if you wish.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    hmmm seem to me that..

    Seem to me that..
    the delimiter is set to a TAB
    so if you want to import it into the db select tab separated file
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The data above was definitely not tab separated. It was space separated. (And, in a test, I did import successfully into an access database, using spaces as the separator.) However, because the forum software will replace multiple spaces in text, with single spaces when posting, I took a look at the data before it is displayed, and it almost appears to be some sort of fixed-width format. So, I edited the post to add code tags around the data, which overrides the space compression, and formats the text with a fixed-width font.

    Now, it appears to be some sort of fixed width format. But, it's not consistent...

    metro292008: Did you copy from the source file, and paste without any editing?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Question If thats the case.

    If it is an incositand space seperated file.
    I would be VERRY courius who/what exported the file

    I would think someone/body data-typed the file from a system into a text-file. I than think it would be wise to go to that person an ask how / where did they get the info.

    But this is assuming and we all know what that does :
    IT MAKES AN ASS OUT OF U AND ME ;-)

    SO will wait to see what further info well get.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  7. #7
    Join Date
    Feb 2008
    Posts
    2

    Thumbs up Thanks y'all

    Thanks loquin and all others, you've been very helpful especially you loquin. I tried the first method you suggested, getting help too from reading and researching and I'm getting somewhere. I even, just before asking for help, imported the datas in the db using "Get External Data" and it worked well though I'll had to do few adjustments because of the spacing in between the datas but it was all good. Now I want to do it automatically from my application that why I asked for help, anyway the first method you showed me opening the textfile, importing the datas using sql etc is working for now, though not perfectly yet but I know I'm getting somewhere thanks a million.

    Quote Originally Posted by loquin
    The data above was definitely not tab separated. It was space separated. (And, in a test, I did import successfully into an access database, using spaces as the separator.) However, because the forum software will replace multiple spaces in text, with single spaces when posting, I took a look at the data before it is displayed, and it almost appears to be some sort of fixed-width format. So, I edited the post to add code tags around the data, which overrides the space compression, and formats the text with a fixed-width font.

    Now, it appears to be some sort of fixed width format. But, it's not consistent...

    metro292008: Did you copy from the source file, and paste without any editing?

Posting Permissions

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