Table import from text file that is 'lost' spaces

[amorosik]

I'm using a TransferText function to import the data contained within a file, and it basically

DoCmd.TransferText acImportDelim, "my_spec", "Items",file_name, True

On my_spec there is variables:
DateFourDigityear = true
DateLeadingZeroes = true
DateOrder = false
DecimalPoint = ,
FieldSeparator = ;
TextDelim = "
TimeDelim = :

I would expect the contents of the fields in the Items table to be exactly the same as a text rows contains
But it is not so, in the sense that some fields that previously contained spaces (for example ...;" ";" ";...) , after the import are empty fields
And this causes me problems because some queries are based on the presence of the space character inside some fields

Question is: how to prevent the import command from considering empty fields even the fields that contain only spaces?

 

[ebs17]

Does the field definition of the database table allow spaces?

because some queries are based on the presence of the space character inside some fields

Unusual and impractical design. Does a space mean anything other than "nothing"?

 

[CJ_London]

perhaps use sql instead in combination of a schema.ini file since this is a delimited file.

Using this data as an example

"Fld1";"Fld2";"Fld3"
"0";" ";"3"
"1";" ";"4"
"2";" ";"5"

saved as a file called dlim.txt

with this schema.ini file in the same directory

[dlim.txt]
format=delimited(;)

This select statement (easily converted to an update or append query) would look like this

SELECT fld1,nz(fld2," ") as f2, fld3
FROM (SELECT * FROM [TEXT;DATABASE=D:\Dev;HDR=Yes].dlim.txt) AS txt;

and produces this result

not easy to see but there is a space in the f2 column

easy to build a schema.ini file tailored to specific requirements (I loop through the destination fields to determine datatypes and any constraints for example) and you can use criteria and joins to restrict rows to be returned, and modify query to return only required fields plus modify data as part of the import process.

If you don't know about schema.ini files, google to find out more, they can do a lot more that in this example

 

[cheekybuddha]

[dlim.txt]
format=delimited(

Gotta love over-helpful software!

Probably ought to read:

[dlim.txt]
format=delimited(;)

 

[ebs17]

DoCmd.TransferText acImportDelim, "my_spec", "Items",file_name, True

acImportDelim => acLinkDelim

This should show the same effect as viewing @CJ_London. Reading the text file will be correct, the problem is saving in an improper definition of a field.

 

[CJ_London]

Gotta love over-helpful software!

yeah - doesn't happen until you post and I wasn't fast enough off the block to correct it

 

[NauticalGent]

If you don't know about schema.ini files, google to find out more, they can do a lot more that in this example

Where do they hide this info?!? With a little effort this fills the gaps where saved imports fall short - and a hell of a lot easier to modify.

 

[amorosik]

Does the field definition of the database table allow spaces?

Unusual and impractical design. Does a space mean anything other than "nothing"?

The field is defined as varchar(250) and therefore can also contain 'blank space character'
A 'blank space character' is, of course, different from no information

 

[amorosik]

perhaps use sql instead in combination of a schema.ini file since this is a delimited file.

Using this data as an example

"Fld1";"Fld2";"Fld3"
"0";" ";"3"
"1";" ";"4"
"2";" ";"5"

saved as a file called dlim.txt

with this schema.ini file in the same directory

[dlim.txt]
format=delimited(;)

This select statement (easily converted to an update or append query) would look like this

SELECT fld1,nz(fld2," ") as f2, fld3
FROM (SELECT * FROM [TEXT;DATABASE=D:\Dev;HDR=Yes].dlim.txt) AS txt;

and produces this result
View attachment 108590

not easy to see but there is a space in the f2 column

easy to build a schema.ini file tailored to specific requirements (I loop through the destination fields to determine datatypes and any constraints for example) and you can use criteria and joins to restrict rows to be returned, and modify query to return only required fields plus modify data as part of the import process.

If you don't know about schema.ini files, google to find out more, they can do a lot more that in this example

You see, space can be seen
Yes of course but this is the 'by hand' method
What I wanted to understand is whether the native import function can work correctly, also taking spaces, or not

 

[Pat Hartman]

If your source table is Jet/ACE, you can prevent "spaces" by changing the setting of AllowZeroLengthString to No. MS has vascilated over time regarding this default. The current default is Yes which is not helpful and just causes confusion. It also defeats the purpose of setting the column to be required.

To solve your problem on import, do not import directly. Use the link option instead. Then use an append query which will allow you to use the Trim() function to turn the ZLS into Null.

 

[amorosik]

If your source table is Jet/ACE, you can prevent "spaces" by changing the setting of AllowZeroLengthString to No. MS has vascilated over time regarding this default. The current default is Yes which is not helpful and just causes confusion. It also defeats the purpose of setting the column to be required.

To solve your problem on import, do not import directly. Use the link option instead. Then use an append query which will allow you to use the Trim() function to turn the ZLS into Null.

Sorry but I don't understand the answer, maybe I explained myself badly
I want to keep spaces in the fields that contain them (I put an image where you can see that some fields are just spaces)
I don't want to put a null where was a space before
I use tables linked to an odbc data source, the physical db is Firebird

 

[CJ_London]

I suspect the issue is access removes trailing spaces which leaves you with either a zls or null. You have trailing spaces in some of your other fields- are they retained or lost?

It may be you can specify width of the column, but that applies to fixed width formats. Your file is a bit of a hybrid in that it appears to be fixed width from what can be seen but also has text delimiters so probably won't work - I expect you would get a whole bunch of double quotes in your data or spend a lot of time setting up the spec to exclude them.

And no guarantee that will work anyway to preserve the space, you would need to try it and see. Just tried it on the schema.ini method and spaces were not retained.

 

[amorosik]

I suspect the issue is access removes trailing spaces which leaves you with either a zls or null. You have trailing spaces in some of your other fields- are they retained or lost?

It may be you can specify width of the column, but that applies to fixed width formats. Your file is a bit of a hybrid in that it appears to be fixed width from what can be seen but also has text delimiters so probably won't work - I expect you would get a whole bunch of double quotes in your data or spend a lot of time setting up the spec to exclude them.

And no guarantee that will work anyway to preserve the space, you would need to try it and see. Just tried it on the schema.ini method and spaces were not retained.

Yes, spaces are eliminated if there are only spaces in the field
No the text file is not hybrid, it is a classic text file with fields delimited by "
I highlighted in red a field that contains only one space, and that space is lost after import

 

[Pat Hartman]

Sorry but I don't understand the answer, maybe I explained myself badly

My fault. I misread the question. I thought you wanted to get rid of the ZLS.

I don't think you will get this to work by using the import action. I think you can get it to work if you use the link action and use an append query. Make sure that your column definition has Allow ZLS set to Yes.

Using code/queries that depend on a space value will be problematic going forward because it is extremely difficult to get Access to save a space that is typed into control. The only time I have had this happen is if the user types something into a control and then rather than using the escape key to get rid of the typing, backspaces over the typed characters. You can try this to verify. I generally don't have the problem because I always set my text fields to Allow ZLS = No. It is way too confusing to handle both null and ZLS in a string so I don't allow ZLS.

 

[CJ_London]

Yes, spaces are eliminated if there are only spaces in the field

you haven't answered the question - you have spaces after user1, Banco1, etc. Are they imported or lost?

 

[Pat Hartman]

I think the append method will keep them but I don't know of any way to type them into a form field.

 

[amorosik]

you haven't answered the question - you have spaces after user1, Banco1, etc. Are they imported or lost?

Importing with

DoCmd.TransferText acImportDelim, "my_spec", "Items",file_name, True

spaces after user1, Banco1 are imported correctly
But space on the second column 'Record_Eliminato', and on all column where the field is only spaces, is lost

 

[CJ_London]

Thanks - In that case I don't think there is a solution other than the 'manual' methods suggested by me in post #3 or by @ebs17 in post #5

this causes me problems because some queries are based on the presence of the space character

An alternative perhaps is to leave the value as null and modify your code/sql down the line to convert nulls to a space as and when required.

If your BE is sql server or similar, perhaps try using the import functionality provided by those applications, maybe they will preserve spaces

 

[Pat Hartman]

But space on the second column 'Record_Eliminato', and on all column where the field is only spaces, is lost

Then fix that in the append query using Nz()

 

[amorosik]

Then fix that in the append query using Nz()

To find out where the fields containing only spaces are, I have to scroll line by line, field by fie, the csv file
Might as well make a completely new routine to export and import and have complete control over the code
This question was originally asked to understand why the native TransferText function didn't import correctly
And if there was a solution with some particular parameter or setting that I didn't know about
But it seems to me that it is not possible
And so I'll have to do it all again by hand