Jagwarman
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
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
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
Thanks
Rob H
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.
The csv file already has the data in it.
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so each time I would add a new Wb do the import, add my new data and then save as?
ASKER
tried it Rob and when it opens the file it looks like this
PK!╚ÏJ╔¿ñ[Conten t_Types].x ml ó(á╠U█N1}7±6}5lcï^ı³ÇÊl├n[;â┬▀;╗á A╚}┘[w╬Ñù3¢█yY$´ÐzùëN┌
:-(
PK!╚ÏJ╔¿ñ[Conten
:-(
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
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
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
Turns out I needed to save the file with .......FileFormat:= _
xlCSV
Thanks for all your help.
Here's to the next time :-)
Regards
ASKER