Data Modeling: CSV conversions

I’m still on a programming kick with my blog, so here’s another article. I used to run into this problem a lot so for those of you that are or know Excel users, here’s my solution. The below code is run as a VBA script in Excel.

Here’s a problem you may face. You ask someone for information and they send it to you in an Excel sheet. You then happily open the sheet only to find that it’s not in an easy-to-use format.

For example, let’s say you had an Excel sheet with several hundred business IDs and each business ID has a code set that you need to use.

Ex: A | B | C
ID | Business Name | Codes
72566477 | McDonald’s | Chicken_Sandwich,Fries,Diet_Coke,etc.

If you wanted to make a table out of these, you could do a text-to-column and then transpose but, obviously, this isn’t a good approach for any but the smallest datasets.The code below will take these lists, create a new excel sheet, and then match up the static values with each item in the comma-seperated list. Within seconds, you can have a useable dataset instead of a bunch of CSVs.

The function can be called as such:

Public Function CallF()

Dim Keys(0 To 2) As Long
Keys(0) = 1
Keys(1) = 2
Keys(2) = 3

CSVToTable "Sheet to Convert", 4, 4, Keys, True, 3

End Function

In the converting function, the StartRow is the row the data starts on, the CSVColumn is the column number (A = 1, B = 2, etc.) containing the CSV data, the KeyColumns are the column numbers that need to repeat, and the HasHeader and HeaderRow variables tell the script if and where you column headers are.

Here’s the converting function:

Public Function CSVToTable(WkSheetName As String, StartRow As Long, CSVColumn As Long, KeyColumns As Variant, Optional HasHeader As Boolean = False, Optional HeaderRow As Long = 0)

Dim x As Long, y As Long, z As Long, sSheetRow As Long
Dim ParsedValues As Variant
Dim LockValues() As String
Dim sSheet As Worksheet
Dim tSheet As Worksheet
Set sSheet = Worksheets(WkSheetName)
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Set tSheet = Worksheets(Application.Worksheets.Count)
tSheet.Name = "Table"
y = 1

'Read the columns to the new sheet
If HasHeader = True And HeaderRow > 0 Then
'Add the header columns
For x = LBound(KeyColumns) To UBound(KeyColumns)
'Format the column
tSheet.Columns.EntireColumn.NumberFormat = "@"
tSheet.Cells(1, y).Value = sSheet.Cells(HeaderRow, KeyColumns(x)).Value
y = y + 1
Next x
'Add the CSV column
tSheet.Cells(1, y).Value = sSheet.Cells(HeaderRow, CSVColumn).Value
'Reset y for row values
y = 2
End If

'Now read off the data
sSheetRow = HeaderRow + 1
Do While IsNull(sSheet.Cells(sSheetRow, KeyColumns(0))) = False And sSheet.Cells(sSheetRow, KeyColumns(0)) <> ""
ReDim LockValues(UBound(KeyColumns))
'Read in the lock values
For z = LBound(KeyColumns) To UBound(KeyColumns)
LockValues(z) = CStr(sSheet.Cells(sSheetRow, KeyColumns(z)))
Next z
'Parse the CSV
ParsedValues = Split(sSheet.Cells(sSheetRow, CSVColumn), ",")
If IsArray(ParsedValues) And UBound(ParsedValues) <> -1 Then
For x = LBound(ParsedValues) To UBound(ParsedValues)
'For each entry, print the static values and the parsed CSV value
'Print the lock values first
For z = 1 To UBound(LockValues) + 1
tSheet.Cells(y, z) = Trim(CStr(LockValues(z - 1)))
Next z
'Now print the CSV value - y is the row number, z is the column number
tSheet.Cells(y, z) = Trim(CStr(ParsedValues(x)))
'Increment the row
y = y + 1
Next x
Else
'Increment the table sheet row
y = y + 1
End If
'Increment the source sheet row
sSheetRow = sSheetRow + 1
Loop

'Format the table
With tSheet.Cells.EntireColumn
.Font.Name = "Arial"
.Font.Size = 8
.AutoFit
End With

'Close the objects
Set sSheet = Nothing
Set tSheet = Nothing

End Function

Share

Medical Literature and Classes of Physicians

Bennet’s Classification for Reading the Medical Literature JAMA 1992, 267:920

Med student
Reads entire article, doesn’t understand what any of it means

Intern
Uses journals as pillows during nights on call [osmosis technique]

Resident
Wants to read entire article, eats dinner instead

Chief resident
Skips articles entirely, reads classified ads for jobs

Junior attending
Reads & analyzes everything so he can pimp the med students (See Imagining the Possibilities)

Senior attending
Reads abstracts only, quotes liberally to impress students and housestaff

Research attending
Reads and analyzes everything, including stats, in lieu of sex

Chief of service
Reads references only to see if he was referenced anywhere

Private attending
Reads Time & Newsweek for medical articles to keep up with patients

Emeritus attending
Reads entire article, doesn’t understand what any of it means

Share

<span>%d</span> bloggers like this: