Can anyone help me.
I have to import data from various General Practice computer systems to help UK GPS analyse their patients. The data is held on .csv files on floppy disks.
The date formats are general EDIFACT style ie 19970101. It is better to import theses dates and convert to excel 1990 dates as this format (35431 or 1/1/1997).
is easy to present data in
This bit is easy: the following macro imports from a floppy and formats the correct columns to year month day.
With ActiveSheet.QueryTables.Add(Connection:="TEXT;A:\CHDREP1.CSV", _
.Name = "CHDREP1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'it is important the the below row match, 5= date, 1 = number.
'................................a b c d e f g h i j k l m n o p q r s t u
.TextFileColumnDataTypes = Array(1, 5, 1, 1, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, _
5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5)
'v w x y z a b c d c d
The problem is that in some systems when only the year of a diagnosis is known 1997 is represented as 19970000 and the dates corrupt as in the example attached. The only way I can think of to correct this is to write a macro to search all the above dates at the end and if the dates are > 19000000 (ie corrupted) then perform the following function
(c.value-19000000)*0.3653 on the squares.
How would you correct the data on the attached spreadsheet (which is an example of what the dates look like after importing.
Dr Robin H