Bulk Insert and spaces trimmed at the end of string

  • Thread starter Thread starter Sebastien Lange
  • Start date Start date
S

Sebastien Lange

Hi,

I'm inserting a lot of rows in my access db (via odbc) by using bulk insert
from text files.
Schema.ini file exists. It's working fine, until I have spaces at end of
strings: it is trimmed!!!
e.g.:
" Hello World!!! " becomes after insert
" Hello World!!!"

Any idea why and how to avoid it?
Thanks,
Sebastien
Schema.ini contains
[myTable.txt]
Format=Delimited(;)
CharacterSet=ANSI

ConnectionString:

Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb

Sql:

INSERT INTO [myTable] SELECT * FROM [Text;DATABASE=c:\temp].[myTable.txt]
 
Is the column in question defined as CHAR or VarChar?

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
The MS Access Type is Memo, it's the same with Text type.
And... it seems to be a 'feature' of MS Access!!!
Try it! Open an Access db, then modify a text field by adding some blank
spaces, it will be trimmed off!


William (Bill) Vaughn said:
Is the column in question defined as CHAR or VarChar?

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Sebastien Lange said:
Hi,

I'm inserting a lot of rows in my access db (via odbc) by using bulk insert
from text files.
Schema.ini file exists. It's working fine, until I have spaces at end of
strings: it is trimmed!!!
e.g.:
" Hello World!!! " becomes after insert
" Hello World!!!"

Any idea why and how to avoid it?
Thanks,
Sebastien
Schema.ini contains
[myTable.txt]
Format=Delimited(;)
CharacterSet=ANSI

ConnectionString:

Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb

Sql:

INSERT INTO [myTable] SELECT * FROM [Text;DATABASE=c:\temp].[myTable.txt]
Click to expand...
Click to expand...
 
Access doesn't support fixed width character columns -- Text fields
are variable-length strings, and Access likely trims strings in the
interests of efficiency. As far as I know, there' s no way to change
this behavior. As far as avoiding it -- perhaps inserting non-printing
extended characters at the end of the string instead of blanks?

-- Mary
MCW Technologies
http://www.mcwtech.com

The MS Access Type is Memo, it's the same with Text type.
And... it seems to be a 'feature' of MS Access!!!
Try it! Open an Access db, then modify a text field by adding some blank
spaces, it will be trimmed off!


William (Bill) Vaughn said:
Is the column in question defined as CHAR or VarChar?

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Sebastien Lange said:
Hi,

I'm inserting a lot of rows in my access db (via odbc) by using bulk insert
from text files.
Schema.ini file exists. It's working fine, until I have spaces at end of
strings: it is trimmed!!!
e.g.:
" Hello World!!! " becomes after insert
" Hello World!!!"

Any idea why and how to avoid it?
Thanks,
Sebastien
Schema.ini contains
[myTable.txt]
Format=Delimited(;)
CharacterSet=ANSI

ConnectionString:

Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb

Sql:

INSERT INTO [myTable] SELECT * FROM [Text;DATABASE=c:\temp].[myTable.txt]
Click to expand...
Click to expand...
Click to expand...
 
There's a workaround!
When a string ends with blank caracters, I do not use bulk insert, but I use
the classic way, with a sql INSERT INTO...
And it doesn't trim strings!

Mary Chipman said:
Access doesn't support fixed width character columns -- Text fields
are variable-length strings, and Access likely trims strings in the
interests of efficiency. As far as I know, there' s no way to change
this behavior. As far as avoiding it -- perhaps inserting non-printing
extended characters at the end of the string instead of blanks?

-- Mary
MCW Technologies
http://www.mcwtech.com

The MS Access Type is Memo, it's the same with Text type.
And... it seems to be a 'feature' of MS Access!!!
Try it! Open an Access db, then modify a text field by adding some blank
spaces, it will be trimmed off!


William (Bill) Vaughn said:
Is the column in question defined as CHAR or VarChar?

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Hi,

I'm inserting a lot of rows in my access db (via odbc) by using bulk
insert
from text files.
Schema.ini file exists. It's working fine, until I have spaces at end of
strings: it is trimmed!!!
e.g.:
" Hello World!!! " becomes after insert
" Hello World!!!"

Any idea why and how to avoid it?
Thanks,
Sebastien
Schema.ini contains
[myTable.txt]
Format=Delimited(;)
CharacterSet=ANSI

ConnectionString:

Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb

Sql:

INSERT INTO [myTable] SELECT * FROM [Text;DATABASE=c:\temp].[myTable.txt]
Click to expand...
Click to expand...
Click to expand...
 
Great workaround -- glad you found the solution.

-- Mary
MCW Technologies
http://www.mcwtech.com

There's a workaround!
When a string ends with blank caracters, I do not use bulk insert, but I use
the classic way, with a sql INSERT INTO...
And it doesn't trim strings!

Mary Chipman said:
Access doesn't support fixed width character columns -- Text fields
are variable-length strings, and Access likely trims strings in the
interests of efficiency. As far as I know, there' s no way to change
this behavior. As far as avoiding it -- perhaps inserting non-printing
extended characters at the end of the string instead of blanks?

-- Mary
MCW Technologies
http://www.mcwtech.com

The MS Access Type is Memo, it's the same with Text type.
And... it seems to be a 'feature' of MS Access!!!
Try it! Open an Access db, then modify a text field by adding some blank
spaces, it will be trimmed off!


Is the column in question defined as CHAR or VarChar?

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Hi,

I'm inserting a lot of rows in my access db (via odbc) by using bulk
insert
from text files.
Schema.ini file exists. It's working fine, until I have spaces at end of
strings: it is trimmed!!!
e.g.:
" Hello World!!! " becomes after insert
" Hello World!!!"

Any idea why and how to avoid it?
Thanks,
Sebastien
Schema.ini contains
[myTable.txt]
Format=Delimited(;)
CharacterSet=ANSI

ConnectionString:

Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb

Sql:

INSERT INTO [myTable] SELECT * FROM
[Text;DATABASE=c:\temp].[myTable.txt]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top