473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Table Row to CSV string function???

I'd like to convert any particular table row into a Comma Separated Value string and move the string into a memo field. This is to be done as a part of a transaction logging subsystem of an existing application to store the before snapshot of a row that's been updated.

Is there any built-in functionality to do this?

Best Pratice?
Dec 1 '06 #1
7 3733
MMcCarthy
14,534 Expert Mod 8TB
I'd like to convert any particular table row into a Comma Separated Value string and move the string into a memo field. This is to be done as a part of a transaction logging subsystem of an existing application to store the before snapshot of a row that's been updated.

Is there any built-in functionality to do this?

Best Pratice?
I don't know of any built-in fucntion but you could always use vba to create a string of the values with commas inserted between the values.
Dec 1 '06 #2
nico5038
3,080 Expert 2GB
Why the effort of creating a string in a Memo field?
It will ask extra processing and introduce problems reverting the process when (one or more of) the fields hold also a comma.....

I simply use a copy of the original table with an additional DateTime field holding a timestamp (Use the Now() function).
By adding this field to the unique key you have an excellent log table that can be exported into a .csv once in a while to store the data off-line.

Idea ?

Nic;o)
Dec 1 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
Why the effort of creating a string in a Memo field?
It will ask extra processing and introduce problems reverting the process when (one or more of) the fields hold also a comma.....

I simply use a copy of the original table with an additional DateTime field holding a timestamp (Use the Now() function).
By adding this field to the unique key you have an excellent log table that can be exported into a .csv once in a while to store the data off-line.

Idea ?

Nic;o)
Sounds good to me Nico. It's what I would do.

BTW, thanks for looking at that other question for me. I appreciate your prompt responses to my little requests. ;)

Mary
Dec 1 '06 #4
Thank-you for your responses! I should have elaborated on the requirements a bit more but I tried to keep it simple. There are several factors that led to the CSVString to Memo field approach.

The application is comprised of multiple tables (MANY).

The application is transaction based with each transaction updating multiple tables.

Each transaction must be captured in a transaction log.

Each log entry must capture the entire transaction (I.e., the before images of all of the table rows involved in the transaction).

All tables involved in a particular transaction are disparate in length, number of columns, format and content. All of the tables' attributes are either numbers or short text (names, addresses).

The transaction log will be utilized additionally for undo/redo functionality as well as a significant piece of a disaster recovery process/procedure.

Comments?

WHEW ;-)


Now, in response to the posts:

Ordinarily, I would have choosen the same approach recommended by your posts. But since this application involves many tables in a given update situation and that the multi-table update must be captured in a transaction log entry and that all of the tables are "disparate in length, number of columns, format and content", it seemed appropriate to take advantage of memo field's atributes as a very flexible datatype for this type of use. (I.e., containing multiple CSVStrings delimited by table-name/colon and semi-colon/EOL.)

In addition, any embedded commas are easily mitigated by quoted text strings. (BTW,There are no quotes as part of the text within the application.)

Is there a variation on your recommendation that you may have worked with before that may address the above requirements?

I welcome any methodologies, comments, suggestions, etc., addressing the above requirements. Thanks to all who have and will respond!

R
Dec 2 '06 #5
nico5038
3,080 Expert 2GB
I would still use a log table per table and add a transactionID (sequencenumber and/or datetimestamp) to be able to extract the complete transaction afterwards.

Another alternative that's really flexible would be to have one log table like:
TransID
UniqueKeyOfChangedTable
TableName
Fieldname
FieldOldValue

The FieldOldValue can be a textstring able to hold all different values or you could use an OldValue per datatype.

Nic;o)
Dec 2 '06 #6
I don't know of any built-in fucntion but you could always use vba to create a string of the values with commas inserted between the values.
What would the VBA code look like to do this generically, given any table row presented to this code. Ideally, I'd like to input some sort of table row reference for 1 of any of numerous tables and output a comma separated string of values equivalent to the number of columns of the table row. Thanks!

R
Dec 2 '06 #7
NeoPa
32,556 Expert Mod 16PB
Does this help at all?
Expand|Select|Wrap|Line Numbers
  1. 'ToCSV Converts the parameters to a Comma Separated Values record.
  2. Public Function ToCSV(ParamArray avarArgs() As Variant) As String
  3.     Dim strParam As String
  4.     Dim intIdx As Integer
  5.  
  6.     ToCSV = ""
  7.     For intIdx = LBound(avarArgs) To UBound(avarArgs)
  8.         strParam = avarArgs(intIdx)
  9.         If InStr(1, strParam, ",") > 0 Then strParam = """" & strParam & """"
  10.         ToCSV = ToCSV & "," & Nz(avarArgs(intIdx), "")
  11.     Next intIdx
  12.     ToCSV = Mid(ToCSV, 2)
  13. End Function
To call it you would enter each field from the record in the arguments list.
EG. If you had a Table with Four fields (ID; Name; Age; Phone) you would call it as :
Expand|Select|Wrap|Line Numbers
  1. StrLog = ToCSV([ID], [Name], [Age], [Phone])
Dec 3 '06 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Thomas Matthews | last post by:
Hi, I would like to create a table (or vector) of pointers to templated functions. 1. How do I declare a typedef of a pointer to a templated function? For example, I have some functions...
6
by: Lewis Veale | last post by:
I have an Access 2000 front-end pointing at a SQL Server backend, with around 80 linked tables and views. I frequently need to point the front-end at different versions of the back-end, and achieve...
5
by: Marty | last post by:
Is it possible to attach a SQL Server table to Access without using a DSN? Thanks.
2
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so far no luck with field lookup properties. What...
21
by: Johan Tibell | last post by:
I would be grateful if someone had a minute or two to review my hash table implementation. It's not yet commented but hopefully it's short and idiomatic enough to be readable. Some of the code...
4
by: Bob | last post by:
Hi all, I'm trying to import data, modify the data then insert it into a new table. The code below works fine for it but it takes a really long time for 15,000 odd records. Is there a way I...
1
by: since | last post by:
I figured I would post my solution to the following. Resizable column tables. Search and replace values in a table. (IE only) Scrollable tables. Sortable tables. It is based on a lot...
1
by: rakeshnair | last post by:
i wrote a code in jsp to create dynamic table..the problem is i need data base connection when cursor moves from one cell to other... eg...when i enter product id in the first cell, the product name...
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
5
by: jrod11 | last post by:
hi, I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.