Importing text file with blank fields changes to null

[Egoyret]

When I import a text file with fields separated by ~ character, if I have a field with blanks, the field in the table appears as null. Also if I have valid characters in the text field plus some spaces at the end, it will trim out those spaces.
I need access to import the field just as it is, respecting any blanks. The field es defined as text. Is there some option I need to set up ? looks like its doing an auto trim. Mi version es Access 2003.



Please help !

[June7]

Importing from what file type? Can you provide sample file? Are you importing with Fixed Width or Delimiter option?

[ajetrumpet]

When I import a text file with fields separated by ~ character, if I have a field with blanks, the field in the table appears as null. Also if I have valid characters in the text field plus some spaces at the end, it will trim out those spaces.
I need access to import the field just as it is, respecting any blanks. The field es defined as text. Is there some option I need to set up ? looks like its doing an auto trim. Mi version es Access 2003.

Please help !

there has been very few questions I've see about this, but Access is horrible when it comes to this issue. It's inconsistent and I don't believe there is anyway around it.

For instance, querying out NULL values through the interface yields different results than doing it in code. The same is true for querying out results that contain leading and/or trailing blanks. You can query out "value" through the interface and/or with code and still be able to return something like "value " in text fields.

One thing you may want to check, which is something I had set in all my fields when I ran these checks, is if specific field sizes are set in your table fields. I doubt that makes a difference here. I'm more inclined to think this is one of the many inconsistencies of Access, which it seems like the source of your little quick here is the same source of the quick I mentioned above when I ran my tests.

Again in summary here, Microsoft has become known for releasing very untrustworthy software. It's spiraling down into the "low quality" realm, if you ask me.

[Egoyret]

Importing from what file type? Can you provide sample file? Are you importing with Fixed Width or Delimiter option?

Text file type, delimiter option, delimiting with the ~ character. I attach sample text file. The field in question is called contract_number and is just before the field which says USD (see ~ ~)

[Egoyret]

Thanks. No field sizes in import spec as file option is delimited. But resulting table gets defaults sizes of 255 for text fields.

[June7]

Don't understand the issue. I don't see any trailing spaces in the data. I don't allow empty strings in my tables. I certainly don't understand the requirement for trailing spaces or a field with only a space in it. If you want something besides Null in the no data fields, run Update query to fix.

However, did have problem with field Last_Processing_Date. Type Conversion Failure error. So I edited the date from 11/4/2011 to 11/04/2011, import again and both date fields erred. Weird!

Sorry, don't have solution.

[RayMilhon]

Take out the AM After the date. I still got Errors on the Account Number and GL_Code but both dates came through successfully. Will look at the other 2 in a bit.

[RayMilhon]

Ok After looking at it Access assumes those 2 fields are Long Integers Since the are 9 Digits that exceeds the Long Integer Max of 2 billion. Change them to text and they import fine. Since these are account numbers you will not be performing any calculations on them so Text is the appropriate Datatype.

[Egoyret]

Ok After looking at it Access assumes those 2 fields are Long Integers Since the are 9 Digits that exceeds the Long Integer Max of 2 billion. Change them to text and they import fine. Since these are account numbers you will not be performing any calculations on them so Text is the appropriate Datatype.

Thanks and sorry for the trouble, but the issue is not those fields which were part of the sample record. The issue is field contract_number which I cannnot get the space that comes in the text file into a field in the database table.

[Egoyret]

Don't understand the issue. I don't see any trailing spaces in the data. I don't allow empty strings in my tables. I certainly don't understand the requirement for trailing spaces or a field with only a space in it. If you want something besides Null in the no data fields, run Update query to fix.

However, did have problem with field Last_Processing_Date. Type Conversion Failure error. So I edited the date from 11/4/2011 to 11/04/2011, import again and both date fields erred. Weird!

Sorry, don't have solution.

Let me explain: the field contract_number which you can find in the text file just before the word USD, has one space and as it is separated by ~ it looks like this:~ ~. When I import, I dont get the space but a null value instead. I noticed that if I replace in the text file the space for a text, for example AAA, plus some trailing spaces, I dont get the trailing spaces, just the AAA. So it looks like Access on it´s own is making a right trim of the text fields and I have no way of avoiding that.
Why do I want to read a field with blank space ? precisely I am analyzing data form a larger Oracle data base in order to detect and recommend corrections for inconsistent data like this. The data is handed off to me via a text file. I cant believe Access, which I love, wont let me pick up the data with no interferences !!
Thanks for your trouble in analysing this case.
regards,