Acctg DB: CSV as Data Container

So we talked about The MS Office team. It's like the Chicago Bulls during it's heyday. MS Excel is our Michael Jordan and MS Access is our Pippen. What a tandem! Nevertheless, we need a center player and more, to handle the data. We are using data like a ball, we pass it in many creative ways and have it thru the goal in equally many ways.

Using SQL Express is like importing Shaquille to our team. But for now, there are many other center players. MJ and Pippen could be enough to carry our project, but there are many times that personal differences may happen. That is just like the data coordination using Excel and Access. Things may get in the way that I will have difficulty to explain to the intended layman audiences.

For automating these applications, we need the coach. The audience would not care about what length the coach has to do, to get them to work. So I decided to make an Excel "Add-in" for these matters. Running a team costs money and I may not be able to share this add-in freely.

So for our enjoyment, we're bringing in another center player - CSV. With this, we skirt the thorny issues between MS Excel and MS Access (automation - like security, hidden instances, etc.). We are also able to distribute the burden to a fairly versatile database player.

According to Wiki:

- "A comma-separated values (CSV) file is a simple text format for a database table. Each record in the table is one line of the text file. Each field value of a record is separated from the next with a comma. Implementations of CSV can often handle field values with embedded line breaks or separator characters by using quotation marks or escape sequences. CSV is a simple file format that is widely supported, so it is often used to move tabular data between different computer programs that support the format."





It is a very adaptable player and can be handled by the nuances of other applications or team players.

According to Wiki:

- "The CSV file format is very simple and supported by almost all spreadsheets and database management systems. Many programming languages have libraries available that support CSV files. Even modern software applications support CSV imports and/or exports because the format is so widely recognized. In fact, many applications allow .csv-named files to use any delimiter character."


So here is the beginnings of a triangle offense, that you can easily follow with some modifications as needed:




We are using the file "List.xls" we have earlier discussed. The said file has a VBA code for posting to MS Access but we are going to another range to create a version for posting to CSV.

We created a user form on an area. We have created a tabular container linked to that form and we have another table that will store each entry, accumulating them in rows of data. The same form or another sheet can be used as a Dashboard for viewing the data before eventually posting it to the CSV holder (but we are not showing it just now - the "viewer or dashboard").

Note on the video:
Part A)
- we are showing on the earlier part a review of how the List.xls file was able to undertake posting to MS Access.
- the "=COUNTA(C7:C12)" formula was used to determine the count of rows to post
- concatenation using the formula [="C7:G" &] plus the determined end row number designation was used to get the text containing the address of the tabular data to post (range name with dynamic dimension is not visible to MS Access)
- this address is complemented by the VBA code: [ Sheet1.Range([j5].Value).Name = "usrlist"] viewable on the VBA section privy for sheet1, w/c redefines the area covered by the range name
- there are also various notes/comments shown which reflect a possible way to determine completeness of the info needed - via count of the dimension of the table
- the code however for prompting and disallowing the posting of incomplete data was not included for now

Part B)
- we created a spreadsheet form via cell format, borders, sheet background picture (w/c was also made available on a separate video before this series installment)
- Selecting disconnected cell is done by pressing the 'Ctrl' button while we are clicking various cells or ranges. That way we can quickly apply similar formatting.
- The single row table is directly linked to various input cell area and is used to data-form it for posting to the temporary tabular storage
- the temporary storage range which will accumulate each posting for review is directly below
- We used the record facility of Excel to copy paste value from the data-former to the temporary data container
- The said data container can serve the purpose of final validation, as in when a check voucher is forwarded to the accounting manager for approval.
- Notice that for simplicity we have created no id field yet (w/c will be helpful when operating searches for deletion or edit of a specified row entry)
- For your purposes, you can also include the Data validation feature of Excel (for example on the input cell for age - to limit it to, say, '18' to '50' yrs)
- You can also create formula using the "If" functions, to catch when an input cell has undesirable entry (like "0" for middle initial) or to replace that entry by other characters (See Excel help for "If" function)
- Because of video length limit, we did not include application of cell and sheet protection

VBA Part:

More than 5 years ago, when I was doing a similar DB Acctg System, I was not fortunate to have an internet connection whether at home or office. Now, I'm glad, because of the wealth of materials available with which you can improve or build upon these techniques.

For this exercise, we are perusing MrExcel's forum post (opens in new window). And editing it to our taste. Kudos to "TommyGun" who posted the code, made available for us.

- we commented the MS Access code for posting the earlier section of the spreadsheet
- note that the code here is using the shell approach of calling the mdb file and also note that the mdb file has an "AutoExec" named macro that transfers spreadsheet data to another mdb container and then closes itself automatically

- for the "CSV" operation, we created a module and pasted the code from the forum
- then we replace the "*" character with null so the code is usable
- we edited the target CSV file: we included the path of our spreadsheet (but you may adjust this if the target file is not on the same folder as the spreadsheet) and we rename the target file to "UsersDB.csv"

The modified code will depend on the following cell contents (which may differ with you):






note:
-test data are already used and some were posted to the temporary storage
-there are formula for certain cells (cells which will be used by the code execution to find where to append and what data are involved)
- the file exist has a value of "1" if there is already a CSV file existing to make the append with or without field names

The temporary storage append is as follows:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/27/2010 by ****
'

'
Range("C33:H33").Select
Selection.Copy
Range("C" & [p37]).PasteSpecial Paste:=xlPasteValues

'replaced macro recorder codes:
'Range("C36").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

On the other hand we made some adjustment to the code for appending to the csv file as follows:

Sub Append2CSV()

Dim tmpCSV As String 'string to hold the CSV info
Dim f As Integer

CSVFile = ThisWorkbook.Path & "\UsersDB.csv"

f = FreeFile

Open CSVFile For Append As #f
tmpCSV = Range2CSV(Range([P36].Value)) 'we used a pointer cell to our source range
Print #f, tmpCSV
Close #f
[P33].Value = 1 'this is our signal that a file already existed and add'l append will skirt the field names
End Sub


Also we made a slight change to the function portion, because the code presupposes that the table to append starts with "A1" or first row as follows:

cr = list.Row '1 '''make adjustment to validate the starting row of table: if file is existent already don't include field names

Also we have defined the delimiter in the following section of the said code:

For Each r In list.Cells
If r.Row = cr Then
If tmp = vbNullString Then
tmp = r.Value
delimiter = ","
Else
tmp = tmp & delimiter & r.Value

At the start, the delimiter is null but gets the value "," thereafter (we can adjust this value if needed).


Conclusions:

We have found the starting technique for using csv file in our IT system. MS Access should have no problem linking from such files and then combining them based on some field IDs that are common to various data files gathered.

The implication is that if we create a system for PO Form (for purchasing) and another system for receiving (by the Warehouse), there is then a possibility of cross reference (all the way to an Accounting system, to the HR's mail-merge letters for certain parties).

Also if you are presently using "Quickbooks" or "Peachtree", these strategies can be used to extend the system. All you have to do is study the csv-import field requirements of your existing systems. This will be useful, especially that each business has a different pre-printed transaction tickets like the Check Vouchers, Checks, etc. which usually require typing. So we can type on them and capture the data for export to an existing system.

Most of this effort stems from our conceptualization and the spreadsheet know-how, which we can expect from most office personnel.

If this series may help you then subscribe! Enjoy.