Inconsistent date formatting when importing CSV through a macro

Question

I am a complete newbie to Excel VBA. I recently managed to install the following macro to my personal workbook so that I can import CSV files (selecting the necessary option) with the click of a button:

Sub OpenTextFile()

   filetoopen = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
   If filetoopen = Null Or filetoopen = Empty Then Exit Sub

   Workbooks.OpenText Filename:=filetoopen, _
   Origin:=65001, DataType:=xlDelimited, Comma:=True

End Sub

It works. However, it does not work consistently. I use it for different CSV files (all of which come preformatted in the ISO 8601 date system), but I get different results. In some of them the date output is DD/MM/YYYY hh:mm, but in others it is something strange I cannot understand (like 00:00,0, or 50:00,0). I can manually select the row contents and change the format to long-date, so at least I am sure that excel recognises the data as a date and not as text.

How can I make sure that the dates are all equally formatted? what does this depend on?

Thanks!

Answer 1

Solved: the issue is not with Excel, but with the service that provides me the CSV files. Apparently, they have two options to download the CSV from their web, and they format dates differently. One of them uses the ISO-8601 correctly, but the other one is adding ".0" at the end of the date string, so Excel flips out and treats it as a text string.

Thanks all for your replies anyway.

Answer 2

Different date-formats are notoriously when imported into excel, since the windows localization (with the default delimiters, date and number formats) can be hard to deal with.

Do not mess with the windows localization just to import one file.

When repeatedly importing csv-Files, I use the following approach :

1. I set-up schema.ini-file for the csv-Files in question. See this for further info

2. I either

   • query the csv-File with SQL and ADODB, which allows for a simple SELECT * simple pre-processing (reordering columns, filtering records, ...). I can output the resulting ADODB.Recordset into my workbook OR

   • I set up a linked table in my workbook an ADODB.Connection. The data can be updated with a simple macro or right-click.

Either way: IMO working with schema.ini-Files has the following advantages

• allows you to handle any date- and number-formats without adjusting your excel-workbooks or vba-code
• simple and transparent structure, readable
• one set of settings. each and every excel-sheet or database can utilise the same schema.ini-file to process your data.

Edit: this could provide a starting point.

1. create a new textfile named schema.ini in the folder where you store your csv

2. open in Notepad (or better: Notepad++), paste this into it

   [yourfile.csv]
   CharacterSet = ANSI
   ColNameHeader = FALSE
   Format = Delimited(;)
   DateFormat = "DD.MM.YYYY"
   DateTimeFormat = "DD.MM.YYYY hh:nn,ss"
   Col1 = yourdatefield DateTime
   Col2 = somelongfield Long
   

3. adjust the following after reading enter link description here

   • filename
   • format (what delimiter, or is it fixed)
   • DateFormat and/or DateTimeFormat. Realize that minutes are nn instead of the common mm
   • your desired column-headers and the columns data-types. You need ColNameHeader = FALSE for this.

4. Set-up an ADO-Connection in VBA that enables you to run SQL-Statements and return ADO-recordsets.

5. run an SQL-Statement like this one

   SELECT * FROM [Text;DATABASE=C:\Users\yourFolder].yourfile.csv

6. work with the recordset

Note: you mentioned some mixed time-decimal writing (50:00,0). As long as any digits in the column refer to time-units, e.g. seconds or minutes, there is no problem with declaring the field a DateTime-field.

However: if something like 00:00,50 means half a minute instead of 50 seconds, it might be required to read it as text, then convert it with SQL stuff like FORMAT(), LEFT() in your Import-SQL