Questions

MS Access 2002- Problem importing File

+
0 Votes
Locked

MS Access 2002- Problem importing File

polinimz
Hello,

I am trying to import a fairly large text file, fixed delimited. I am getting an error: "One or more rows of data in your file contain too many characters to import." Strange thing is, everything looks correct, and it imports fine with Access 97. I am confident it is an Access 2002 issue... I searched this error on the web and ran the Microsoft utility for correcting the Null gaps but this is not a satisfactory fix. Any ideas why I am getting this error? And any possible solutions? Thanks in advance...
  • +
    0 Votes
    dryflies

    you say the type of file is fixed delimited. Is it fixed width or is it delimited? in the Access 2002 help under import, the subject data sources you can import or link to gives a choice of delimited text or fixed width text. if it is an import problem more specific info is needed.
    However, I think you should look at the table definition and see if the number of characters in (width of) each field is exceeded by any of the rows of data in the import file. You may be trying to import data that will not fit the field definition for that column.
    it is more likely to be an access 97 issue allowing the import truncating the data instead of forcing it to be correct.
    if this answer is usefull, tell TR.

    +
    0 Votes
    polinimz

    I apologize, it is a fixed width import. First 2 rows of data contain many "blank" columns, and this appears to be my problem. Evidently there are "embedded null values" in my source file and Access 2002 cannot "read" these, instead it needs spaces to mark the blank columns. Strangely enough, Access 97 imports everything just fine, and there is no truncation (I checked). If I delete the first 2 rows from the source text file that have some blank fields in them, it imports into 2002 perfect, leading me to say with some confidence it is an issue with the embedded null values... Any advice with this issue? I cannot modify the source file because it is a production file, I need to import! Please don't tell me I must regress to 97 in order to perform this operation!!!!!!!!

    Thanks...

    +
    0 Votes
    dryflies

    Can it import those first two rows without the rest of the file?
    if there is a way to change it to a delimited file you should.

    +
    0 Votes
    ag691234

    I just tried importing several text files (some tad delimited, some fixed width into Access 2002 and had no problem. I agree with the other responder that it is more likely to be an Access 97 error rather than Access 2000.

    I am assuming you are importing the file to a new table. I wonder if there is an issue with the text file. It is possible that some delimiters are located in such order that Access has a hard time figuring out what the columns are.

    Try importing only one row to see if the import works (make a copy of the file and delete everything but one row of date). If that works you know the issue may not be with Access, so try importing several rows, and check the text file.

    Another idea is to make sure you have all text converters installed (under Office installation).

    If you are importing the file into an exsting table, check the tabe design. Possibly one of the columns needs to be modified to allow for more data.

    Hope this helps.

  • +
    0 Votes
    dryflies

    you say the type of file is fixed delimited. Is it fixed width or is it delimited? in the Access 2002 help under import, the subject data sources you can import or link to gives a choice of delimited text or fixed width text. if it is an import problem more specific info is needed.
    However, I think you should look at the table definition and see if the number of characters in (width of) each field is exceeded by any of the rows of data in the import file. You may be trying to import data that will not fit the field definition for that column.
    it is more likely to be an access 97 issue allowing the import truncating the data instead of forcing it to be correct.
    if this answer is usefull, tell TR.

    +
    0 Votes
    polinimz

    I apologize, it is a fixed width import. First 2 rows of data contain many "blank" columns, and this appears to be my problem. Evidently there are "embedded null values" in my source file and Access 2002 cannot "read" these, instead it needs spaces to mark the blank columns. Strangely enough, Access 97 imports everything just fine, and there is no truncation (I checked). If I delete the first 2 rows from the source text file that have some blank fields in them, it imports into 2002 perfect, leading me to say with some confidence it is an issue with the embedded null values... Any advice with this issue? I cannot modify the source file because it is a production file, I need to import! Please don't tell me I must regress to 97 in order to perform this operation!!!!!!!!

    Thanks...

    +
    0 Votes
    dryflies

    Can it import those first two rows without the rest of the file?
    if there is a way to change it to a delimited file you should.

    +
    0 Votes
    ag691234

    I just tried importing several text files (some tad delimited, some fixed width into Access 2002 and had no problem. I agree with the other responder that it is more likely to be an Access 97 error rather than Access 2000.

    I am assuming you are importing the file to a new table. I wonder if there is an issue with the text file. It is possible that some delimiters are located in such order that Access has a hard time figuring out what the columns are.

    Try importing only one row to see if the import works (make a copy of the file and delete everything but one row of date). If that works you know the issue may not be with Access, so try importing several rows, and check the text file.

    Another idea is to make sure you have all text converters installed (under Office installation).

    If you are importing the file into an exsting table, check the tabe design. Possibly one of the columns needs to be modified to allow for more data.

    Hope this helps.