Creating a FolderItem seems to be reformatting data

General
1

This is making me nuts - as short a hike as that is.

I have a CSV downloaded from a web based Database (I know it to be a PostgreSQL database) that has 32 columns - a combination of text, integers, one Double, a couple of boolean and a 3 dates.

The problem is with the date column. Looking at the original file in Excel the date column is “12/10/77”. In the following code, after the sRecord = tisTextInput.ReadLine, the sRecord variable contains the date column as 1977-12-10. The complete sRecord value is Candidate,506480,Mister,"",Nathan,Louis,Vick,"",Nathan,1977-12-10,Regular,0,8229 FM 1649,"",Gilmer,TX,75645-6571,"","",903.720.0401,1,YES,YES.

The only code that should execute is as follows:

Sub Open()
dim tisTextInput As TextInputStream
dim sRecord As String

Try
tisTextInput = TextInputStream.Open(Globals.fiFolderItem)
//tisTextInput.Encoding = Encodings.MacRoman
Catch e As IOException
tisTextInput.Close
MsgBox(“Error accessing file.”)
self.Close
End Try

If tisTextInput.EOF Then
MsgBox(“End of File”)
self.Close
End If

Listbox1.DeleteAllRows
sRecord = tisTextInput.ReadLine //first record contains column headings and isn’t used

Do

**sRecord = tisTextInput.ReadLine**
sFields = sRecord.Split(",")
ListBox1.AddRow(sFields())

Loop Until tisTextInput.EOF

Return
End Sub

2

You haven’t indicated what the problem is. I’m intuiting that some listbox column content looks wrong. What do you actually get and what should it be? And what is the csv file data that corresponds to that column?

3

The problem is that something is reformatting the date column behind the scenes. Actually, I like the reformatted format, and I was going to do that myself. It’s just that I don’t know what is reformatting the date and can I depend on it to ALWAYS reformat dates the same way.

The column in the CSV is 12/10/77, after the file is read by the Text Input Stream and I look at the record using the debugger, it has been reformatted to 1977-12-10.

4

What date format do you see if you open the CSV file in a plain text editor instead of Excel?

5

Blockquote
What date format do you see if you open the CSV file in a plain text editor instead of Excel?
Blockquote

Okay, that’s interesting, I see in the “reformatted” format - 1977-12-10. So the change is being made somewhere else, not in Xojo. Then it looks like it’s Excel that changing the format.

I guess that solves the issue,
Thanks everyone.

6

That is normal. The format you are seeing is the usual one employed when storing dates in databases.

Excel is “smart” and detects it as a date so it applies the short format to it (something you can change for the cell, in fact.

7

Excel reformats a lot of things for display. Don’t use it to examine data.

2 Likes
8

Exactly. NEVER use Excel for data - its “intellisense” is the bane of Science and Medicine …

2 Likes