Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    France
    Posts
    4

    Unanswered: Importing some lines of a fixed-width txt file into 3 different tables

    Hello to all

    My native language is not English. I think I can make myself understood. I have:

    An Access database composed of three tables: BAT10, and Bat21 bat40
    A fixed-width Txt file file of about 3000 lines that I want to import into the database above with condition.

    Txt file lines are differentiated by a code in column 31 and 32. This code can take the values ​​10, 21, 30, 36 or 40. These different lines are not grouped by the code but spread throughout the file. I am only interested in 10, 21 and 40. Lines with 10 will be imported into BAT10, 21 into Bat21 ...

    My problem is very similar to one treated there two years ago by Sinndho but here there is the complication of the filter ...

    Thank you in advance for all the help that you will give me.

    José

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    import the data into a table
    then create a query which extracts whatever data you need from the imported data top the destination table(s)

    thats going to be something like
    insert into mytable (a, column, list) select another, listof, columns from anothertable where code in (31,32)

    but as we don;'t know your table names or structure its hard to tell

    it would be nice had you included the reference/link to the post where Sinndho provided a solution
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2003
    Location
    France
    Posts
    4

    Importing some lines of a fixed-width txt file into 3 different tables

    Thank you for your prompt response Healdem.

    The reference / link to the post (first page) where Sinndho Provided a solution is http://www.dbforums.com/microsoft-ac...nt-tables.html

    Please find 2. Txt files. The first is a small part of the fixed-width From Where I want to imort the data

    370 INDRE-ET-L OIRE D414052013 2013
    3700570570030075 00 B0014 A010001001B044 000420011A0351CHATEAU OF Grillemont Z
    3700570570030075 0004610121999 C 10 0A 0A ME000002195 4000 0000006 01011985 0
    00121 H 01000 3700570570030075 001200000002195000010321 P 001 000002195 00 + 000 + 000 + 000
    3700570570030075 00136 000002195 000010321 000005161 000002195 000010321 000005161 000000000 000000000 000000000 000002195 000010321 000005161 000005161 000000000
    3700570570030076 00 000 420 011 B0407 B010001001B044 0351CHATEAU OF Grillemont V
    3700570570030076 0004610121999 C 10 0A 1000 MA000000288 0L 1,800,016 01,011,985 0
    3700570570030076 000105000000288000001354 00121 HA6 090 L 003 000 000 288 + 00 000 to 005 000 +
    3700570570030076 00136 000000288 000001354 000000677 000000288 000001354 000000677 000000288 000001354 000000677 000000288 000001354 000000677 000000677 000000000
    3700570570030076 00000000 00140 H 00000000CV00002304 00000000OO NO N00010101024040002000101010500008010201800401
    3700570570030076 00160001000021GA 0010204NNN0000000000004090 000 +
    3700570570030080 00 000 420 011 B0407 C010001001B044 0351CHATEAU OF Grillemont P
    3700570570030080 0004610121999 C 10 0A 1000 MA000000419 0L 1,800,026 01,011,985 0
    3700570570030080 000153000000419000001971 00121 HA6 100 L 003 000 000 419 + 00 000 to 005 000 +
    3700570570030080 00136 000000419 000001971 000000986 000000419 000001971 000000986 000000419 000001971 000000986 000000419 000001971 000000986 000000986 000000000
    3700570570030080 00000000 00140 H 00000000CV00001303 00000000OO NO N00010101030070104000101020900013110201800302
    3700570570030080 00160001000023GA 0010203NNN0000000000004100 000 +
    3700570570030080 0010203NNN0000000000003100 00160002000009CV 000 +
    3700570570030081 00 002 180 022 B0059 A010001001B232 0351LES SIZES Louris K
    3700570570030081 10 0AB0022828072000 C 1000 186001 CC28072000 MA000000245 0P 0
    3700570570030081 000092000000245000001153 00121 HA65090 P 003 + 00 000 000 245 000-005 000 +
    3700570570030081 00136 000000245 000001153 000000577 000000245 000001153 000000577 000000245 000001153 000000577 000000245 000001153 000000577 000000577 000000000
    3700570570030081 00000000 00140 H 00000000GR00006003 00000000OO NN O01000101020040002010001000400006310101860401
    3700570570030081 0010103NNN0000000000003100 00160001000018CV 000 +
    3700570570030082 00 002 180 263 B0063 A010001001B232 0351LES SIZES Louris F
    3700570570030082 10 0AJ0002901011970 C 1000 MA000000671 0P 0 197902 01011985
    HA5 3700570570030082 00121 120 002 000 000 680 00 000223000000671000003154 P + 005-005 + 000
    3700570570030082 00136 000000671 000003154 000001577 000000671 000003154 000001577 000000671 000003154 000001577 000000671 000003154 000001577 000001577 000000000
    00140 3700570570030082 H NO GA00003806CV00005004GR00006704 00000000OO N01000201034070203000101010800008750201979102
    3700570570030083 00 001 880 253 B0099 A010001001B202 0351LE WELL Berrault B
    3700570570030083 10 0AP0009124091994 C 1000 178002 AC08081995 MA000000279 0P 0
    3700570570030083 000102000000279000001312 00121 HA6 110 P 004 000000279 00 + 000 + 000 + 000
    3700570570030083 00136 000000279 000001312 000000656 000000279 000001312 000000656 000000279 000001312 000000656 000000279 000001312 000000656 000000656 000000000
    3700570570030083 00000000 00140 H 00000000CV00001003 00000000OO NN N00010101016060202010001000600007210101780202
    3700570570030083 0050901NNN0000000000003120 00160001000016CV 000 +

    The second file includes description of the tables for BAT10 and Bat21.
    If these presentations are not appropriate, tell me how.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    so what are we supposed to do with that?

    the link you supplied refers to this page, not some page of two years ago

    I still don't see the issue
    import your source data into a table with Access
    then send the data to wherever its required in whatever format its required using an insert into query
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2003
    Location
    France
    Posts
    4
    This is the correct link :
    http://www.dbforums.com/microsoft-ac...xt-access.html

    I posted these two files to complete the description I made earlier. They are best viewed with Notepad++
    Last edited by f5jd; 08-08-14 at 03:22.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You posted a sample of the contents of the text file but you did not specified the position of each column, so writing a specific data parser (i.e. a function that would read the text file and dispatch segments of each line in different columns is not possible with the information I have. However, healdem's suggestion is a good one. Here's a more detailed explanation of the method he suggested:

    1. Import the text file in a temporary table (or buffer table) using the Access Text Import Assistant. As there is no separator in the text file (it's not a csv file), you'll need to specify the length of each column.

    2. Once the contents of the whole file is in the buffer table, you can use a set of INSERT queries to extract rows matching a condition and insert them in the (final) destination tables. This can be done either by creating queries objects with the graphic interface or using "dynamic" queries, i.e. strings of SQL expressions submitted to the CurrentDb.Execute method.

    3. In you case the SQL expression to be used, either in an Access query or using VBA, would be something like:
    Code:
    INSERT INTO BAT10 ( Col1, Col2,... ColN )
        SELECT Col1, Col2,... ColN
          FROM BufferTable
         WHERE Col31 = 10;
    With Col1, Col2,... ColN being a comma-separated list of the columns in both tables.

    Note:
    The names of the columns in both tables can be different. However, the number of columns must be the same in the INSERT and the SELECT parts of the query and the data type of the columns must match in both lists (i.e. if Col1 in the INSERT part is of type Text, Col1 in the SELECT part must also be of type Text). Some implicit conversions are possible but I would not rely on that.

    You repeat the same process for Bat21 and bat40, changing the name of the table in the INSERT part of the SQL expression (INSERT INTO Bat21 then INSERT INTO Bat40) as well as the criteria, i.e. the WHERE part (WHERE Col31 = 20, then WHERE Col31 = 40).
    Have a nice day!

Tags for this Thread

Posting Permissions

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