If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Importing Data from ACCESS into SQL Server 2005
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 |
#3
|
|||
|
|||
Importing Data from ACCESS into SQL Server 2005
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Importing Data from ACCESS into SQL Server 2005
"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 |
#6
|
|||
|
|||
Importing Data from ACCESS into SQL Server 2005
"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 |
#7
|
|||
|
|||
Importing Data from ACCESS into SQL Server 2005
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 |
Thread Tools | |
Display Modes | |
|
|