Since Microsoft Excel will read and write csv files, I wrote a fairly large macro to read the file from the call center and rearrange the values into the formats the other systems wanted. I was pretty happy, until I noticed two items where Excel "helped" me.
- Credit card expiration dates are recorded as 03/09 meaning end of March, 2009. Excel sees that and says, "That looks like a date, but not quite all the digits are there." So it guesses that you are really talking about 03/09/2006 (this year), so now when I say output the date as MM/YY or 03/06, the credit card is already expired. I can correct this by telling Excel to out put the date as MM/DD. A hack to be sure, but it works until 2032.
- When Excel encounters numbers over 9,999,999,999 it decides to represent them in scientific notation. These numbers can be reformatted to display correctly but after 15 digits, Excel starts dropping digits off. So lets pretend your credit card number is 16 digits long (most are) like 1234 5678 9012 3456. Excel reads it as 1.234567 E+15, when I reformat it, the credit card number becomes 1234567890123450, losing the last digit -- a check digit by the way.
Once the file was open by Excel it was too late. So I needed to find a way to put those =" " characters into the csv beforehand. I considered writing a small .net program, but then I thought about Python and did a quick google search.
From that search I ended with this.
# -*- coding: cp1252 -*-
import sys
import csv
args = sys.argv
inputCsv = args[1] + '.csv'
outputCsv = args[1] + '-post.csv'
reader = csv.reader(open(inputCsv, "rb"))
writer = csv.writer(open(outputCsv, "wb"))
for row in reader:
row[20] = '="' + row[20] + '"'
writer.writerow(row)
Which comes down to:
Open the input csv, and create an output csv, for each row in the file, wrap the 20 item with =" value ". That was easy. I should have written the whole thing in Python.
