Importing txt unsuccessfully

[SamuelOu]

Hello,

I have been trying to import a txt file to my database, however, it pops up same error message when I click finish:

"The field 'Attribute16Name' contains a start position of '33609'. The maximum start position allowed is 32767."

and

"An error occurred trying to save import/export specification 'Txt gt Import specification'"

Column 'Attribute16Name' has constant value "Handling time" for 18888 rows.

Any help is appreciated! Thank you.

[JoeM]

Just how big is this text file?
Do you really have more than 32767 characters on each line?
I can't imagine having that many characters on a single line.
If so, I think that might be problematic.

[SamuelOu]

The size of the text file is 215mb: 146 columns and 18888 rows.
I have six similar files to import and this is not the biggest one.
I don't have there is more than 32767 characters on each line. However, I am not 100% sure about that.

[rpeare]

yeah that seems exceedingly large for a single line, unless the file is not indicating the end of line with a carriage return/line feed pair and is only using a line feed. Access will choke on it if the carriage return is not there and you can't always tell just by opening the file in a text editor.

if, visually, the lines are shorter than 32000 characters you may have to run it through a process to insert the carriage returns with something like filesystemobject which will acknowledge line feeds as an end of line.

[rpeare]

Try this, write a module and put this code in it:

dim fs
dim f
dim sPath as string
dim sline
dim iRow as long

spath = "c:\test\testfile.txt"  '<====== PUT YOUR ACTUAL FILE PATH AND NAME HERE
set fs = createobject("scripting.filesystemobject")
set f = fs.opentextfile(spath)

do while f.atendofstream <> true
    irow = irow + 1
    sline = f.readline
    if len(sline) > 32000 then
        debug.print "ROW " & irow & " has " & len(sline) & " characters in it
    endif
loop
set fs = nothing

see if you get anything in your immediate window

[SamuelOu]

I found that there is a column having more than 30000 characters, I decided to delete that column to avoid this problem.

However, new issue comes up. Now it pops up " The search key was not found in any record".

I tried to put "No" on all the Index, still doesn't work.

Is there any clue to find out why is causing that? Any help is appreciated.

[rpeare]

When are you getting this error? As you try to import the file? Or before it even gets to that step, like when you're setting up your import spec.

[orange]

I would question 146 columns?? This is a rare table design if normalized.
Need to know more info about the data.

[rpeare]

any chance you can supply a sample file with garbage data in it. I am not questioning the 146 fields like orange is because I deal with a file with a similar number of fields from an external source I have no control over but it's a good question to ask... do you have control over the original file you are getting.

[SamuelOu]

I got this error when I click finish, before saving the importing steps.

I have no control to the data. It's a standardized file downloaded from a 3rd party website. It got so many field because there are more than 60 attributes which generate at least 120 fields (attribute name, attribute value).

[Rawb]

Two possibilities:

1. You're trying to import more data than your DB can hold, or
2. There's a space/other character where there shouldn't be

Try importing the file into an empty database and see if it works, if it does then you might be too close to the 2GB file size limit to import all the data in your main db.

It looks like you're using the Import Wizard to import the file. Especially for a file of this size and complexity, I'd recommend coming up with VBA import code, that will allow you to see exactly where the import is failing and give you much greater control over error handling. It would also allow you to maintain a normalized database even if the file itself isn't normalized.

[rpeare]

What about my previous question about getting a sample file, if the file is coming from a website anyone can access you can give the address and we can take a closer look, if it's proprietary, just create a sample text file and cut and paste some of the original file in there, just make sure to fill in any proprietary information with garbage data.

[SamuelOu]

For the confidential reason, I'm not allowed to upload a sample file.

I tried many ways and it finally works when I skip importing the fields contains more than 256 characters.

[rpeare]

Your import will work if you change the fields that have more than 255 characters to LONG STRING in your table design, assuming they are also less than 32000 characters long.


edit:

err... long text