asked on

CSV Date import

I am having a problem with dates in a file that is saved as CSV. I open the /csv through a macro in my Excel file. I found a reply on EE where someone was having a similar problem. I am posting this to see if there is an alternative solution to the one posted.

the problem is that any date from 1 -12 will change to American dates when the file is opened i.e. 1/7/2014 will change to 7/1/2014, 11/7/2014 will change to 7/11/2014 etc but 13/7/2014 will stay as 13/7/2014 etc

the solution posted was @ https://www.experts-exchange.com/questions/21635074/Problem-with-reading-date-in-uk-format-dd-mm-yyyy-from-excel-spreadsheet.html

To make a temporary csv based on the original csv, but only all dates are in "mm/dd/yyyy"-format

Sub FixDate(CSVFilename)

Name CVSFIlename As CSVFilename & "z"

Open CSVFilename & "z" For Input As #1
Open CSVFilename For Output As #2
Do While Not EOF(1)
Line Input #1, rawdata
'correct date zone
dateloc = 12 'for example
mnth = Mid(rawdata, dateloc, 2)
Select Case mnth
Case "01"
newdate = "Jan"
Case "02"
....
End Select

moddata = Left(rawdata, dateloc) & newdate & Mid(rawdata, daleloc, 3)

Print #2, moddata
Loop
Close #2
Close #1
Kill CSVFilename & "z" 'delete the temp file

End Sub

Is there am alternative to this. My dates are in Column H in my file.

Thanks

Jagwarman

ASKER

I should add the csv is an excel file saved as CSV (Comma Delimited)

Rob Henson

When doing the import, there is an option in one of the steps to set the format of the data being imported. One of the data formt options is Date and you can tell the import wizard which format the source data is in. If you don't specify, it will try and use the local default settings.

Thanks
Rob H

Jagwarman

ASKER

Hi Rob, All I am doing is opening the file with Workbooks.Open FileName:= _ etc etc etc XFile.csv

The csv file already has the data in it.

Rob Henson

Rather than physically opening the CSV file, have you tried using the Get External data wizard to import the data?

This wizard will ahve a step to specify the format for each column.

You could record the import into VBA to amend your existing code.

Thanks
Rob H

Jagwarman

ASKER

Hi Rob being a complete 'VBA Non-Expert' I am not familiar with 'Get External data wizard'

the process I do is:

step one, open csv file [which is blank] and copy details from active sheet onto the csv file, save csv and close it.

during the course of the day step one will be repeated many times and this is when the problem occurs.

so, when we go into step one the next time.... open csv file [which is not blank it will have previous data on it and any dates 1 -12 will be seen as US Date format] copy details from active sheet onto the csv file in the first blank row, [i.e. row 6] save csv and close it.

Would your 'Get External data wizard' method work?

Thanks

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

so each time I would add a new Wb do the import, add my new data and then save as?

Jagwarman

ASKER

tried it Rob and when it opens the file it looks like this

PK!╚ÏJ╔¿ñ[Content_Types].xml ó(á╠U█N1}7±6}5lcï^ı³ÇÊl├n[;â┬▀;╗á A╚}┘[w╬Ñù3¢█yY$´ÐzùëN┌

:-(

Rob Henson

Looks like its not converting the text correctly.

Step 1 of the Import Wizard gives option for File Origin. Try changing this option, I am using Windows Ansi, maybe you need Macintosh or other option.

Thanks
Rob H

Jagwarman

ASKER

Hi Rob

Turns out I needed to save the file with .......FileFormat:= _
xlCSV

Thanks for all your help.

Here's to the next time :-)

Regards