Update all fields in a recordset/table using function? 1

[jtseltmann]

I have a generic question that I am looking for some guidance on...here is the situation...

The main goal is to remove out extra commas (within double-quotes) and trim leading/trailing spaces from each field in a comma delimited file.
sample data row:
CODE," Name, Name ", CODE2, CODE3,#

sample end result:
CODE,Name Name,CODE2,CODE3,#

I currently have code that imports to a temp table then I create a recordset and loop through each field for each record and processes a "data cleaning" routine to do just what I said earlier. The code works great but it is not a very fast process and my data files can get quite large. It only can process about two records per second. My sample file has about 10 rows per record that it is looping through.

Does anyone have an idea as to how to update each field in a recordset any faster?

Thank you in advance for any tips or hints...
Jeff

 

[SgtJarrow]

Have you tried creating an update query for the table and using Replace and Trims for each field???

UPDATE Table4 SET Table4.field1 = Trim(Replace([field1],",","")), Table4.field2 = Trim(Replace([field2],",","")), Table4.field3 = Trim(Replace([field3],",","")), Table4.field4 = Trim(Replace([field4],",",""));

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[jtseltmann]

Thanks for the idea..honestly I had not thinking it would take about the same amount of time and there are up to 80 columns. I'll give it a try and we'll see...
thanks!