Is it impossible for access to import ZLS from .CSV ?!

[MS$DesignersRCretins]

I'll make this as simple as possible to reproduce. Create abc.csv as
"","",""
"a","",""
"a","b","c"

Start a new Access database with a table, 3 fields, and for all 3 fields, yes for both Required and for allow zero length string (ZLS). What you've said - corroborated by MS help/web help - is that you won't permit null, but you will permit ZLS. Okay, import.

Access 03 (okay, check others - if you can navigate the hallucinogenic post-2003 design) will only import the third record. The error message is useless but at least alerts you that not all of the .CSV got imported.

In my testing, apparently the ONLY way to get records with "" fields is to set Required = no on those fields, and they get strictly read in as Null. I'm saying, it seems impossible to read them in as a ZLS.

Sure I could set Required=No and run an update query with ZLS and then set Required=Yes but I hope you would agree that that would be a preposterous amount of patchworking.

I can't believe my eyes, and I've Googled the topic silly, but I can't find a single thing on the web about this anomaly. I believe you will get the same results as I, which is inconsistent with the description of Required.

My only theory is that Required=True only works for direct (i.e. keyboard) user input to a field, and is useless for importing? Really???

The first thing I wonder is if others duplicate my results...

 

[CJ_London]

These links may help to explain

http://msdn.microsoft.com/en-us/library/office/aa193988(v=office.10).aspx

http://office.microsoft.com/en-gb/a...ta-with-a-zero-length-string-HP005187581.aspx

Assuming your .csv import specification has " as a text delimiter then what you are actually importing is what is between the ". e.g. a - not "a". To do what you want you need to change the text delimiter to a single quote and import as follows (not tested)

'""','""','""'
'a','""','""'
'a','b','c'

 

[MS$DesignersRCretins]

Thanks, though there is no import specification. (True, I might have used one to limit which fields to ignore, but I didn't) I am grateful for the two links but unfortunately they did not add anything. So I'm still looking for an answer.

No one has said they reproduce the issue, but I'll assume that they would have said if they differed. My description above is very easy; 45 seconds tops to test it.

Quick trivia, more about Excel: I tried opening in Excel and saving, to then try importing from Excel instead of a .CSV. The interesting part was if the CSV has
"","",""
"","",""
(2 records, 3 fields) XL03 opens it as 2 records X 2 fields (4 empty cells, B2 is end of worksheet), and XL07 opens it as no records, no fields (A1 is end of sheet). FWIW!

 

[CJ_London]

So if there is no specification - how are you importing? Every .csv import has to have a specification. Suggest you read up on it here - you will have created one automatically but have probably skipped past the tabs where you set the delimiter

http://office.microsoft.com/en-gb/access-help/import-or-link-to-data-in-a-text-file-HA001232227.aspx

A text delimiter can only be nothing or " or '

 

[CJ_London]

So I'm still looking for an answer

did you read my entire post - this is your answer

Assuming your .csv import specification has " as a text delimiter then what you are actually importing is what is between the ". e.g. a - not "a". To do what you want you need to change the text delimiter to a single quote and import as follows (not tested)

'""','""','""'
'a','""','""'
'a','b','c'

did you try this?

 

[MS$DesignersRCretins]

I will be thankful if anyone has anything to add as the issue is not solved. I appreciate your attempts to help CJ, and would like to hear from others now.

I have read your entire posts and considered their usability. Great stuff for Access 2007. Thank you for your help and concern. (Also, I'm not inclined to modify the data with single quotes, but thanks for the suggestion)

 

[spikepl]

Copy your table, remove the Required-requirement and use it as staging table for input. That works.

 

[spikepl]

I was just scanning the posts and realized what name you elected to use.

Very few bugs are still found in Access, whereas features that do not comply with the expectations of some user or other are not sign of cretinism on the part of MS but mostly on the part of the users. MS-designers made a database tool widely available and accessible for use by non-specialists, which is quite a feat and not a sign of cretinism.

 

[MS$DesignersRCretins]

Spike my name choice is explained on the about page, but in case you've drawn an incorrect impression, I'll indicate that the "design" fiasco regards the interface, not product performance and features. I knew Excel's interface was severely slashed and deproductivized in Office 2007+, but with Access it was raped, burned, and thrown into a ditch! I don't know how you separate Queries from Tables now. Not inviting war, but you seemed to want to know what that was about - I'm not a troublemaker; just a scorner of incompetence :banghead:

 

[MS$DesignersRCretins]

Last try. Has anyone tried to reproduce this issue and can they report? It's not difficult. I spent some time condensing the issue into a 45 second exercise for anyone to reproduce - see original post. If no one has a solution (and no one has, short of speculating to edit source data) it would be nice to know it rather than just work from guessing whether this is user specific. I would have thought a pile of MVPs would jump all over this as it's so extraordinary. Maybe my post count or nic suggests that it's a novice question...

Spike, thanks for trying but your idea produces a null field, not a zero length string (ZLS) as I've discussed earlier in the thread. Turning off Required may get the field across, but the field is not null, so it is an inaccurate representation of the data.

 

[CJ_London]

but the field is not null

Have you proved it is not null?

 

[namliam]

You can select "Table" or "query" from the drop down box so you only see those instead of both of them quite easily. I dont mind the new interface that much anymore, though it took some getting used too.

was able to recreate you test from the OP, but have ZERO reporting abilities atm.
To most users the difference between ZLS and NULL doesnt exist, because of this I highly doubt M$ will make any effort even looking into this.

 

[MS$DesignersRCretins]

Thank you for both the interface advice and the confirmation of the problem. I'm not sure if I should mark this solved as it appears to be destined to "remain unresolved."

I appreciate the clear, specific and useful message, and you earn the "thank."

I'm confident that ZLS and null are not adjudged equivalent by expert database users. A case might be made that Access is more of a "consumer product" and some long time database elitists (some highly competent) decry Access as weak-performing kid stuff; maybe some portion of Access users would fit the bill you described. However I can't believe that something as fundamental as Null vs. ZLS would be ignored. There are even a number of MS web pages and help screens that spend some time stressing the distinction.

 

[spikepl]

It seems to me you are so stuck with your indignation that the solution has sailed just past you.

You did not explain why reading the table as I suggested and then stuffing data from that table into the final destination in whichever format you wish is not a solution.

 

[MS$DesignersRCretins]

spikepl - thank you for spelling out your idea to stage it. I apologize that I don't often use the word "staging" although I acknowledge that it's a legitimate database term and your followup makes things very clear.. (I use the vulgarian lingo "temp table"

.) It wasn't indignation, friendly jab permitted

AAR I think I covered staging in the original post, paragraph beginning "Sure I could" . Is the word preposterous unreasonable there? I'm trying to stay dignified (This is becoming a long thread, and noisy, so I wouldn't hold you to having read every word anyway.) Sure, that would get the task done, but would you really suggest requiring two passes to import unremarkably routine data, when the receiving table is already built with fields and data types? Kidding, right?! Or do you mean just to get past this project. There's nothing "wrong" with staging, but imagine if every import had to be staged. Lot of work for Access developers rewriting databases that don't if that was that case.

I really am being serious. Lots of good cleanup and consolidation can be achieved by staging; I just stretch to believe that it should be required for something as routine as picking up "" in a .CSV.

Thanks for following up.

 

[namliam]

To be pedantic, a temp table is something quite different from a staging table, much like the difference between ZLS and NULL. They appear the same however are distinctly different.

Personally I do think it is strange that this doesnt work as expected, however as I stated really a lot of people do not even consider this a problem since they see ZLS as NULL.
Even seasoned developers that I have grown to respect over time dont always seem to understand and try to prevent NULL values just to prevent from having to ever deal with them. I.e. a lot of systems default strings to " " or "?", blank dates filled with 12/31/2999 or 01/01/1900 or keys to 0 (zero) instead of leaving the key blank and having to deal with the possibility of outer joins.
Granted not dealing with outer joins can have some performance gains, but performance in most cases is NO real issue and it is really the problem of dealing with NULL that is being avoided.

 

[MS$DesignersRCretins]

Thank you for your perspective and for being the sole voice acknowledging that something seems truly amiss here.

try to prevent NULL values just to prevent from having to ever deal with them. I.e. a lot of systems default strings to " "

You really hit the nail on the head in my experience, particularly with midrange/mainframes. On the " " case, besides the [perhaps minor] waste of size on a delimited extract, that may unnecessarily introduce need for a TRIM, and instead of efficiently screening ZLS you may have to do a character compare to " ". I dislike trailing blanks personally .

Great commentary and perspective. Thank you for taking the trouble.

 

[CJ_London]

@namliam

Personally I do think it is strange that this doesnt work as expected

It works as expected from my perspective

if you are importing a csv file with text delimiters set as " then the delimiters do not get imported. A ZLS is represented by "" so if you import "","A" then you will see the A (not "A") and Nothing or Null (not a ZLS) for the "". Setting the target table field to allow ZLS does not mean a ZLS will be imported. Setting the the target table field to Required does not mean a ZLS will be imported.

Happy to debate whether it is Nothing or Null but do not see why you would expect it to be a ZLS.

The only way I could see this happening is if the import specification could be modified to convert Nothing or Null to a ZLS prior to importing or some way in the table design to convert Nothing or Null to a ZLS on importing - but then the Required property would be irrelevant

 

[MS$DesignersRCretins]

CJ, how about
,,
representing a null, and
,"",
meaning ZLS? That's how I've always assumed, but you've definitely got me thinking about it.

 

[MS$DesignersRCretins]

For those who have sinlessly suffered through this thread, here are some references for
empty vs. "" vs. Null vs. Nothing vs. not Used vs. Missing vs. Blank vs. Argggh

A fine albeit long historically oriented Taoish piece (oddly available to nonmembers, lol). BTW it and Allen purely equate "" to ZLS:
Understanding Null, Empty, Blank, #N/A, ZLS, Nothing, Missing
http://www.experts-exchange.com/Sof...Null-Empty-Blank-N-A-ZLS-Nothing-Missing.html

Allen, Tips for Serious Users:
Nothing? Empty? Missing? Null?
http://allenbrowne.com/vba-NothingEmpty.html

An Excel perspective would be nice. This one at least grazes the surface:
empty vs. "" vs. Null vs. Nothing vs. not Used vs. Argggh
http://www.pcreview.co.uk/forums/empty-vs-vs-null-vs-nothing-vs-not-used-vs-argggh-t1008967.html