Wierd CSV Import - Cell Values Change! 1

[PBAPaul]

I download my BT telephone bill from the web in CSV format. I then tidy it up and make various calculations to determine client bills etc.

I open the CSV file manually and a date of "01/10/2002" is shown for one entry. Formatting the cell as General, I get a date value of 37530 which if I then format in another date mode comes out as "01-Oct-02". Fine!

I get the filename and then use the VBA routine

NoAction = False
ChDir "D:\Downloads\Household\"
MyFileName = Application.GetOpenFilename("All Files (*.csv),*.csv", , "BT CSV Telephone File", , False)
If MyFileName = "" Or MyFileName = "False" Then
    MsgBox "No file was selected."
    NoAction = True
    Exit Sub
End If
Workbooks.Open FileName:=MyFileName, Format:=2

The same entry now shows "10/01/2002" and formatted as General shows 37266!

The same thing happens if I
There are 200 entries and about 30 of these change when I use the VBA command.

Has anybody any ideas as to what is happening?

Paul

 

[AgatheB]

Hi Paul, I've been having sort of the same problem between SQL and Excel forms. I took all dates, and converted them to textstrings (using serial date, month, year). and after that convert them back to a correct date....

 

[cheerio]

The fundamental problem is that while Excel will try to respect your UK regional settings VBA will always try to use US settings so it sees 01/10/2002 as 10 Jan 2002.

You either need to open the files manually or your VBA code has to read the data as a line of text at a time and parse it. You cannot use VBA to reliably open a csv file that has dates in the format you suggest.

If the forward slash is used only in dates you could do a global replace of / with a comma so that the day, month, year come in as separate fields.

Of course you could ask BT to format the data in a different way but somehow I think you might wait a very long time

 

[PBAPaul]

Thank you both.

This has been driving me crazy. Now I know that it is an inherent Excel VBA problem, I can get around it.

Thanks for your help.

Paul

 

[Tiglet]

I've had exactly the same problem, but I don't know how to fix it!

Conversion to textstrings (using serial date, month, year) has been mentioned as a solution. could you please explain to me what that means so I can do it too.

Thanks in advance


Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]

 

[cheerio]

The whole point of this process is to get data from one computer system to another in a reliable way.

The fundamental problem is that a date such as 10/1/2003 would be interpreted by an Englishman as 10 Jan and by an American as 1 Oct. So we have an ambiguity and that can lead to problems both with humans and with computers.

In particular, VBA always assumes data is in a US format and that applies to when it takes control of Excel. The example above was with data transfer but you would get similar issues if you were trying to use the Advanced Filter with date criteria from VBA.

The simple answer is to avoid the ambiguity. However, that does involve having some influence on the source data. In the example above the csv file from BT would look a more complicated version of something like this:

10/1/2003, "011812345678", 5.0

If you could persuade them to do

10,1,2003, "011812345678", 5.0

then you could pick up the day the month aand the year in any system as they are just numbers.

In Excel you can recreate the date with the DATE function which has the syntax of DATE(year,month,day) and returns a date. In Access you use DATESERIAL with the same syntax and result.

An alternative approach is for the date to be exported as a text string so the above date would come across as

"10 Jan 2003", "011812345678", 5.0

Again you would need to convert the string to a date using a function such as DATEVALUE or CDATE.

This second solution has some risks. If the computer trying to import the dates is French or German then the month names will not all match the English names. Oddly this is where VBA helps as you can guarantee it thinks in English. Worse, if the source computer is french the text string would be 10 fev not 10 Feb.

So my preference is for dates to be exported as separate numbers for day, month, year and for these to be recombined. However, that solution is only available if you can influence the computer that is producing the csv file. As indicated in my original post if the source is a major telecoms company you stand no chance.

 

[Tiglet]

Cheerio,

Thanks for taking the trouble to explain, I understand exactly now, I don't know why but I was thinking that the conversion to text strings could be done automatically after the file was imported from Excel rather than changing the format of the source file - Maybe it was PBAPauls comment that he knew a workaround for the VBA issue. If I am importing a csv, could I first open say Notepad, do a find and replace of the / into commas and then save as a csv, then concatenate the date back up into one column or is there a better way?
I've got no idea how to do the Notepad bit in VBA (If the code for find and replace would be the same as in Excel then I could probably manage that!) but I'll give it a go and see what happens.





Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]

 

[cheerio]

VBA has inherited from the BASIC language commands for reading and writing pure text files. My preferred route would be to have a little procedure that opened the source csv file, read in a line at a time, replaced each / with a comma and wrote out each line to a new file. This is actually much easier than trying to control another application.

An alternative is to read in each line and write the data directly into a worksheet. In effect you are writing a customised csv loader. However, this option is only wise if you can be sure the source data format will not change.

 

[Tiglet]

PBAPaul, I had exactly the same problem and have solved it by doing the following: in Excel use the menu item, "Data", "Get External Data", "Import Text File"

Change the "Files of type" drop down box to "All Files" and select your csv and then follow the on screen selections. "Delimited", click "Next" select "comma", click "Next" and then you can format the columns how you want. Set your column of data with dates as a DMY format and the problem of the dates in csv changing disappears!

Enjoy



Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]