How to create an expression in a table's default value 2

[stv999]

Hi - I have an automated process that imports a large 1 gig .csv file every evening into Access into a pre-existing Access table. One of the fields contains 4 spaces in most records in the .csv file, but Access is importing this field in as null instead of spaces even when I have the default value in the table set as " ". I need to keep those spaces that come from the .csv file because this field is part of the table’s index in Access. Can I write an expression in the default value of the table design that will force 4 spaces into this field instead of nulls when it gets imported? I know I could write a query that updates this field after it gets imported, but I’d like to keep the automated process down to just one step because the file is so big. Thank-you in advance for any help.

 

[PWise]

how are you importing the file?

what i would do is create a linked table to the csv file

and import from the linked table into the access table

 

[SoCalAccessPro]

Sounds like maybe a data type conversion failure.

Do the import manually as a test.

When you do it, does access complian something like "Access set (x) values to null due to type conversion failure, records to (some other reason), and did'nt import (z) record...

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[dhookom]

I wasn't aware that you could or would want to save only spaces in a field. Be default, Access doesn't allow this.

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Setting the default value to =space(4) or " " give you a field with 4 spaces

 

[dhookom]

A default value will have no affect on the field if you are importing a value into the field. Perhaps importing into the table excluding the field in question. Then import into another table with the primary key and the " " field. Use an update query to update the first table with the "another" table where the field value is not " ".

Duane
Hook'D on Access
MS Access MVP

 

[stv999]

Now that I think of it, that makes logical sense - that a default value has no effect on a field if I'm importing into it. That is exactly what is happening...the default of 4 spaces is being overlayed by nulls coming from the .csv file.

I am needing 4 spaces in this field because other tables with this same indexed field contains 4 spaces too, and I am relating this table to other tables in the database. These are outside tables originally coming from Oracle, and I don't know why those fields were set up that way.

I was originally importing the file using the TRANSFER TEXT action in a macro, but I didn't know that I could link to a .csv file in Access. This morning, I tried linking to the .csv file on the network, and amazing! I can automatically use this file in Access! So now the automated process at night will simply be a MAKE TABLE query that links to the .csv file, and within the MAKE TABLE query, spaces will be overlayed in the field if it contains nulls. I have tested everything, and it all works fine. I still have just one step to automate at night now, but it's a MAKE TABLE query instead of an import.

Thank-you everyone, you guys are great! I really appreciate your help.