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 @ http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_21635074.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
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JagwarmanAuthor Commented:
I should add the csv is an excel file saved as CSV (Comma Delimited)
0
Rob HensonFinance AnalystCommented:
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
0
JagwarmanAuthor Commented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rob HensonFinance AnalystCommented:
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
0
JagwarmanAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
Slightly different approach:

Step one, first occurence - copy details from active sheet to new workbook and save as csv.

Step one, next occurence - with a blank workbook open don't open the csv file but import data from it using import data wizard (see below). Add details as required and save as csv file with the same name overwriting the original file.

Get External Data isn't VBA. In Excel go to the Data tab on the Ribbon and over to the left hand end there are various buttons for getting data. Use the "From Text" button to open a browser window and locate your csv file. This will step through the wizard, final step of three is where you can define the formats for each column to be imported.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
so each time I would add a new Wb do the import, add my new data and then save as?
0
JagwarmanAuthor Commented:
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┌

:-(
0
Rob HensonFinance AnalystCommented:
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
0
JagwarmanAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.