Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    21

    Red face Unanswered: Importing ascii file

    I have a tiger/zip file that i ordered from the united states post office. So i want to import it into access. How can I do this, I tried but i got import errors. The cd rom comes with one dat file, and lots of txt files. How should I do this?!? Also do i have to import each txt files or should they all link ?!?

    please help asap

    thanx

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    look at the data in the text files - is it the same data but different layouts or are all of the files needed together?


    if it is text - just import - do not assign field properties - bring it all in as text and then manipulate


    as far as the dat file - what format is it in?

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    21
    HI thanx for replying . . .well the txt files are well like i said before, zip+4 codes and other information. So each text file are different zip codes but the record layout is the same. I do have the record layout, that is how i used it for the advanced feature in the import wizard. So all the text files need to be together. The dat file is in form of ascii and it is used to match the state code assigned by the census bureau to correct the state abbreviation. It also matches the county code to its corresponding county name. So I also have the tiger.dat file record layout and the copyright record layout.

    It just when I imported it I did get some information, but for example the pmsa, cmsa, and the multiple match indicator there was no information and i had another table that was created that said not everything imported and it was named import errors. .


    Originally posted by axsprog
    look at the data in the text files - is it the same data but different layouts or are all of the files needed together?


    if it is text - just import - do not assign field properties - bring it all in as text and then manipulate


    as far as the dat file - what format is it in?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one cause of this behaviour is A looking at the first record and making assumptions about data types. if A sees all numbers in a field on the first row it assigns a numeric data type (e.g. Double) ...this fails if in later records the same field contains alphas.

    check the data types in the (partial) tables after import ...if you have something that is not text, you have probably found the culprit

    solution is as outlined by axsprog: go through each field in the import wizard and override all data types to TEXT

    izy

  5. #5
    Join Date
    May 2002
    Location
    London
    Posts
    87
    If its proving troublesome, set up a new import spec and pull in all the fields as Text. Then work on the resulting table working out what the exception items are when you try and convert them (using queries) to the approprtiate data types.

    Maybe stick in an autonumber index field when you do the import and immediately convert it to Long afterwards so you can trace which row is the problem.


    Oh and it could be worse. At least the data has proper line breaks and you can use the wizard. Binary data imports are mroe of a pain and taht's before you reach the horrors of text-scraping.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    21

    Smile

    HEY !!

    Well what I did now is that I imported all in text and it worked! No import errors. Now there are only two fields the CMSA $ PMSA codes that are blank in each table that I import. I am not sure if they are meant to be or I have to do something else. So now i have like 83 txt files to import. Now I am importing one. Is it wise to import each in different new tables or import it in existing table? The record layout is the same?

    Thanx so much for your help. I am new to this board. Hey like it now =D.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by Trini
    HEY !!

    Well what I did now is that I imported all in text and it worked! No import errors. Now there are only two fields the CMSA $ PMSA codes that are blank in each table that I import. I am not sure if they are meant to be or I have to do something else. So now i have like 83 txt files to import. Now I am importing one. Is it wise to import each in different new tables or import it in existing table? The record layout is the same?

    Thanx so much for your help. I am new to this board. Hey like it now =D.
    you can write a bit of code to do that, if they have simalar file names Ie txt1, txt2, txt3 ... txt83 or something simalar, otherwise you need to employ some latteral thinking as for how to do it i would advise importing and then appending the data to another table and then deleteing the import
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    m.timoney is correct

    different tables - nothing is worse than have your 20th table or so spit out errors and the data has already been appended to a master table

    take you time - do it very methodically

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    21

    Unhappy

    awww man!! So how am I suppose to do that . . . I mean you are right it may be a problem if my 20th table has errors of some sort. I was thinking just importing it in the same table. So it would be this great huge table . . . uhm I know MS Access but not that of an expert in it. Me still learning :/ , but hey I love to learn =D.

    What we are going to use this data is for gps mapping. That is our intention.


    thanx more help =D

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    if the textfiles have different names - let the import spec name them accordingly

    when all the data is intact you can run append queries to a master table that has the strucure of all the tables

    you could write code something like what I have placed in here
    this code is actual code I use to append imported tables into a master table and then rename them


    DoCmd.SetWarnings False
    Dim db As Database
    Set db = CurrentDb
    Dim tdTableDef
    Dim mytable As String
    Dim d
    d = Date
    Dim x
    x = "Tables "
    For Each tdTableDef In CurrentDb.TableDefs
    mytable = tdTableDef.Name

    If Left(mytable, 1) = "2" Then 'tablenames starting with 2
    db.Execute "INSERT INTO tblAlldata SELECT " & _
    mytable & ".* FROM " & _
    mytable
    x = x & mytable & " "
    tdTableDef.Name = "Arch_" & mytable & d

    Else
    End If
    Next
    CurrentDb.TableDefs.Refresh

    DoCmd.SetWarnings True
    MsgBox x & " were appended and archived."

    I hope this helps

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by Trini
    awww man!! So how am I suppose to do that . . . I mean you are right it may be a problem if my 20th table has errors of some sort. I was thinking just importing it in the same table. So it would be this great huge table . . . uhm I know MS Access but not that of an expert in it. Me still learning :/ , but hey I love to learn =D.

    What we are going to use this data is for gps mapping. That is our intention.


    thanx more help =D
    the commands you need are
    docmd.TransferText
    CurrentDb.Execute "Delete"
    CurrentDb.Execute "Append"

    where delete is the query
    DELETE *
    FROM <imported table>;

    and append is the query
    INSERT INTO <Source Table> ( <comma seperated field list> )
    SELECT *
    FROM <imported table>;

    how you use these depends on your DB
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Import them all to the same table. Once you have all your data together, you can better come up with a strategy for dealing with anomalies. You can run a single procedure to transform it, and if something goes wrong you can rerun the entire thing with one swipe.
    As Mark Twain once said: "Put all your eggs in one basket...and then WATCH THAT BASKET."

    blindman

  13. #13
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I am sorry but that is not a good strategy.

    Seperate the data - do it right

    do you place all of your code in one module (functions, procedures, etc) and watch that one module.

    blindman - you usually have great ideas but this user is having problems and to exponentiate them into one table is not good. More time to debug a big f'd up table then to methodically add correct data together.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try identifying duplicate records or keys across 20 tables and you may change your mind.

    blindman

  15. #15
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    yes but in this case each text file is unique data from the post office - all of them totalling the zipcodes and such accross the usa - so in essence no dupes

    then again one can use
    select distinctrow for the final tally

Posting Permissions

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