Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CSV Date import

Posted on 2014-08-06
10
Medium Priority
?
180 Views
Last Modified: 2014-08-07
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
0
Comment
Question by:Jagwarman
  • 6
  • 4
10 Comments
 

Author Comment

by:Jagwarman
ID: 40243324
I should add the csv is an excel file saved as CSV (Comma Delimited)
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40243368
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
 

Author Comment

by:Jagwarman
ID: 40243383
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Rob Henson
ID: 40243388
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
 

Author Comment

by:Jagwarman
ID: 40243778
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
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40243828
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
 

Author Comment

by:Jagwarman
ID: 40243860
so each time I would add a new Wb do the import, add my new data and then save as?
0
 

Author Comment

by:Jagwarman
ID: 40243965
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40243982
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
 

Author Comment

by:Jagwarman
ID: 40245597
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question