Importing Data from ACCESS into SQL Server 2005

I've inherited a project which has numerous fields that have extra spaces in
them -- this is causing a problem with data Inserts, Edits, and Deletions in
my .ASP Website.

I'm importing data tables from ACCESS 2003 into SQL Server 2005, and I need
to know if there's a way that I can Right Trim all fields on the imported
tables during the Import Process.

Will I have to scrub all these tables in ACCESS before hand, or is there a
much simplier method to trim the spaces during import.

Thanks in advance for any ideas offered.

Shane

Why posting to so many newsgroups? Second, m.p.a.adp.sqlserver is about ADP
and SQL-Server and not about upzising problems or problems with only
SQL-Server.

Finally, are your extra spaces in the Access tables before their importation
to SQL-Server or after? Is it's after, then I suspect that you have used
fields of type char and nchar instead of varchar and nvarchar.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Shane Jones" wrote in message
...
I've inherited a project which has numerous fields that have extra spaces
in
them -- this is causing a problem with data Inserts, Edits, and Deletions
in
my .ASP Website.

I'm importing data tables from ACCESS 2003 into SQL Server 2005, and I
need
to know if there's a way that I can Right Trim all fields on the imported
tables during the Import Process.

Will I have to scrub all these tables in ACCESS before hand, or is there a
much simplier method to trim the spaces during import.

Thanks in advance for any ideas offered.

Shane

Sylvain,

Sorry for the Cross-Posting -- I'v been told (by another MVP) that
Cross-Posting is preferred to posting duplicates in several different
groups -- I wasn't sure which group this applied to best (nor which groups
got the most traffic -- as some have only sparse posts).

The extra spaces are in the ACCESS db before importing them to SQL Server --
needless-to-say -- I inherited a mess of data. I'm looking for a way to
RTRIM all fields in all tables (without having to write seperate query
statements for each) -- can this be done?

Thanks in advance for any pointers.

Shane
========================
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
Why posting to so many newsgroups? Second, m.p.a.adp.sqlserver is about
ADP and SQL-Server and not about upzising problems or problems with only
SQL-Server.

Finally, are your extra spaces in the Access tables before their
importation to SQL-Server or after? Is it's after, then I suspect that
you have used fields of type char and nchar instead of varchar and
nvarchar.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Shane Jones" wrote in message
...
I've inherited a project which has numerous fields that have extra spaces
in
them -- this is causing a problem with data Inserts, Edits, and Deletions
in
my .ASP Website.

I'm importing data tables from ACCESS 2003 into SQL Server 2005, and I
need
to know if there's a way that I can Right Trim all fields on the imported
tables during the Import Process.

Will I have to scrub all these tables in ACCESS before hand, or is there
a
much simplier method to trim the spaces during import.

Thanks in advance for any ideas offered.

Shane

I've inherited a project which has numerous fields that have extra spaces
in
them -- this is causing a problem with data Inserts, Edits, and Deletions
in
my .ASP Website.

I'm importing data tables from ACCESS 2003 into SQL Server 2005, and I
need
to know if there's a way that I can Right Trim all fields on the imported
tables during the Import Process.

Will I have to scrub all these tables in ACCESS before hand, or is there a
much simplier method to trim the spaces during import.

Thanks in advance for any ideas offered.

Shane

"Shane Jones" wrote in message
...
Sylvain,

Sorry for the Cross-Posting -- I'v been told (by another MVP) that
Cross-Posting is preferred to posting duplicates in several different
groups

Yes, cross posting is preferred. however, *excessive* cross posting is not.
Or, posting to a good number of newsgroups that are NOT related to your
question is the problem here (don't worry, not a big deal here, but I might
as well point out what the norms are here).


The extra spaces are in the ACCESS db before importing them to SQL
Server -- needless-to-say -- I inherited a mess of data. I'm looking for
a way to RTRIM all fields in all tables (without having to write seperate
query statements for each) -- can this be done?


Sure, you could run the follwing code:

Dim rst As DAO.Recordset
Dim vField As DAO.Field

Set rst = CurrentDb.OpenRecordset("tblAnswers")

Do While rst.EOF = False
Debug.Print "working on " & rst!ID
rst.Edit
For Each vField In rst.Fields
If (vField.Type = dbText) Or (vField.Type = dbMemo) Then
If IsNull(rst(vField.Name)) = False Then
rst(vField.Name) = Trim(rst(vField.Name))
End If
End If
Next vField
rst.Update
rst.MoveNext
Loop

rst.Close

debug.print "done"

The above will trim text fields, and memo.

If your table don't allow zero length strings, then the above could error if
you have a field with *only* blanks, and we trim it. The result trim will
be a ZLS, and that will cause an error (the the assignment to fail).

If you use null for blank fields, then you could go something like:

If IsNull(rst(vField.Name)) = False Then
strBuf = trim(rst(vField.Name))
if len(strbuf) 0 then
rst(vField.Name) = strBuf
else
rst(vField.Name) = null
end if
end if
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Shane Jones" wrote in message
...
I've inherited a project which has numerous fields that have extra spaces
in
them -- this is causing a problem with data Inserts, Edits, and Deletions
in
my .ASP Website.

I'm importing data tables from ACCESS 2003 into SQL Server 2005, and I
need
to know if there's a way that I can Right Trim all fields on the imported
tables during the Import Process.

Will I have to scrub all these tables in ACCESS before hand, or is there a
much simplier method to trim the spaces during import.

Thanks in advance for any ideas offered.

Shane

ghjgkjhkj
"Albert D. Kallal" wrote in message
...
"Shane Jones" wrote in message
...
Sylvain,

Sorry for the Cross-Posting -- I'v been told (by another MVP) that
Cross-Posting is preferred to posting duplicates in several different
groups

Yes, cross posting is preferred. however, *excessive* cross posting is
not.
Or, posting to a good number of newsgroups that are NOT related to your
question is the problem here (don't worry, not a big deal here, but I
might
as well point out what the norms are here).


The extra spaces are in the ACCESS db before importing them to SQL
Server -- needless-to-say -- I inherited a mess of data. I'm looking
for
a way to RTRIM all fields in all tables (without having to write
seperate
query statements for each) -- can this be done?


Sure, you could run the follwing code:

Dim rst As DAO.Recordset
Dim vField As DAO.Field

Set rst = CurrentDb.OpenRecordset("tblAnswers")

Do While rst.EOF = False
Debug.Print "working on " & rst!ID
rst.Edit
For Each vField In rst.Fields
If (vField.Type = dbText) Or (vField.Type = dbMemo) Then
If IsNull(rst(vField.Name)) = False Then
rst(vField.Name) = Trim(rst(vField.Name))
End If
End If
Next vField
rst.Update
rst.MoveNext
Loop

rst.Close

debug.print "done"

The above will trim text fields, and memo.

If your table don't allow zero length strings, then the above could error
if
you have a field with *only* blanks, and we trim it. The result trim will
be a ZLS, and that will cause an error (the the assignment to fail).

If you use null for blank fields, then you could go something like:

If IsNull(rst(vField.Name)) = False Then
strBuf = trim(rst(vField.Name))
if len(strbuf) 0 then
rst(vField.Name) = strBuf
else
rst(vField.Name) = null
end if
end if
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada