Thread: opening csv files

I am opening csv files using:
Workbooks.Open Filename:="I:\My Documents\Soccer Predictions\E0.csv"
Sheets("E0").Move Before:=Workbooks("csvimport.xls").Sheets(1)

Workbooks.Open Filename:="I:\My Documents\Soccer
Predictions\E1.csv"
Sheets("E1").Move Before:=Workbooks("csvimport.xls").Sheets(1)

Workbooks.Open Filename:="I:\My Documents\Soccer
Predictions\E2.csv"
Sheets("E2").Move Before:=Workbooks("csvimport.xls").Sheets(1)

Workbooks.Open Filename:="I:\My Documents\Soccer
Predictions\E3.csv"
Sheets("E3").Move Before:=Workbooks("csvimport.xls").Sheets(1)

Workbooks.Open Filename:="I:\My Documents\Soccer
Predictions\Ec.csv"
Sheets("Ec").Move Before:=Workbooks("csvimport.xls").Sheets(1)

Workbooks.Open Filename:="I:\My Documents\Soccer
Predictions\sc0.csv"
Sheets("sc0").Move Before:=Workbooks("csvimport.xls").Sheets(1)
In these files col b contains dates, before they are moved these dates
are as 01/01/2005, but after moving some change to 01/01/05 and are
text. Ive tried just opening the files and moving them myself but the
ssame happens. Whats throwing me is, the files are in my documents, and
if I open each one manually and drag them into the destination folder
myself, I have no problems with the dates. Any Suggestions.
Regards Robert

As Aside is there any online courses for vba excel?

I've never seen moving a worksheet change the format or the value of a cell to
text.

You may want to step through your code and see if the dates are coming in
correctly.

With .csv files, it's very important that the dates you import match the same
format as the windows setting: mdy or dmy or ymd or...

If these don't match, then text like:

01/02/2005
could be brought in as Jan 2, 2005 or Feb 1, 2005.

And
13/02/2005
could be brought in as text--if windows was set up for mdy.



RobcPettit@yahoo.co.uk wrote:
>
> I am opening csv files using:
> Workbooks.Open Filename:="I:\My Documents\Soccer Predictions\E0.csv"
> Sheets("E0").Move Before:=Workbooks("csvimport.xls").Sheets(1)
>
> Workbooks.Open Filename:="I:\My Documents\Soccer
> Predictions\E1.csv"
> Sheets("E1").Move Before:=Workbooks("csvimport.xls").Sheets(1)
>
> Workbooks.Open Filename:="I:\My Documents\Soccer
> Predictions\E2.csv"
> Sheets("E2").Move Before:=Workbooks("csvimport.xls").Sheets(1)
>
> Workbooks.Open Filename:="I:\My Documents\Soccer
> Predictions\E3.csv"
> Sheets("E3").Move Before:=Workbooks("csvimport.xls").Sheets(1)
>
> Workbooks.Open Filename:="I:\My Documents\Soccer
> Predictions\Ec.csv"
> Sheets("Ec").Move Before:=Workbooks("csvimport.xls").Sheets(1)
>
> Workbooks.Open Filename:="I:\My Documents\Soccer
> Predictions\sc0.csv"
> Sheets("sc0").Move Before:=Workbooks("csvimport.xls").Sheets(1)
> In these files col b contains dates, before they are moved these dates
> are as 01/01/2005, but after moving some change to 01/01/05 and are
> text. Ive tried just opening the files and moving them myself but the
> ssame happens. Whats throwing me is, the files are in my documents, and
> if I open each one manually and drag them into the destination folder
> myself, I have no problems with the dates. Any Suggestions.
> Regards Robert
>
> As Aside is there any online courses for vba excel?

--

Dave Peterson

Thanks for your reply dave, Ive stepped through the code, and the
problem seems to when opening the file with vba. If i either open the
file using File/Open method the file opens and the dates colomn is as
it should be, or, if I just open the docs folder and double click again
its as it should be, but if I use the first line of code just to open
the file, the dates change there foremat some will = 01/01/2005 others
= 1/1/05. Ive looked for a pattern, eg particular months, but it pretty
random, It will do a complete month as 05, then the next month in
colomn as 2005. It seems to only change the year. I cna achieve what I
need to do manually, just got me curius as to why its doing this.
Regards Robert

I'd still guess it was a mismatch between windows and excel's VBA that was
causing the trouble.

If you want to have more control, you can rename the .csv files to .txt. Then
record a macro that brings in those .txt files. You'll be able to specify each
field the way you want.

If all those .csv files are the same layout, you can use that recorded macro to
import them all.

Kind of like:

Option Explicit
Sub testme()

Dim myFolderName As String
Dim myFileNames As Variant
Dim fCtr As Long
Dim wks As Worksheet

myFileNames = Array("EO.txt", "E1.txt", "E2.txt", _
"E3.txt", "EC.txt", "sc0.txt")

myFolderName = "I:\My Documents\Soccer Predictions\"
If Right(myFolderName, 1) <> "\" Then
myFolderName = myFolderName & "\"
End If

For fCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFolderName & myFileNames(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(11, 1), _
Array(19, 1), Array(21, 1))
Set wks = ActiveSheet

wks.Copy _
before:=Workbooks("CSVImport.xls").Sheets(1)

wks.Parent.Close savechanges:=False
Next fCtr

End Sub

This part will change:

Workbooks.OpenText Filename:=myFolderName & myFileNames(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(11, 1), _
Array(19, 1), Array(21, 1))

To what you got when you recorded the macro. (Keep the first line the same--to
be able to loop through the file names.)

RobcPettit@yahoo.co.uk wrote:
>
> Thanks for your reply dave, Ive stepped through the code, and the
> problem seems to when opening the file with vba. If i either open the
> file using File/Open method the file opens and the dates colomn is as
> it should be, or, if I just open the docs folder and double click again
> its as it should be, but if I use the first line of code just to open
> the file, the dates change there foremat some will = 01/01/2005 others
> = 1/1/05. Ive looked for a pattern, eg particular months, but it pretty
> random, It will do a complete month as 05, then the next month in
> colomn as 2005. It seems to only change the year. I cna achieve what I
> need to do manually, just got me curius as to why its doing this.
> Regards Robert

--

Dave Peterson

Or maybe there's something else in those cells??

If you select that column and do
edit|Replace
what: / (slash)
with: / (slash)
replace all

do you get the dates you want--I'd test it by giving it an unambiguous date
format: January, 1, 2005 (say).

If it works, you could include that mass change in your code and walk away
happy???

RobcPettit@yahoo.co.uk wrote:
>
> Thanks for your reply dave, Ive stepped through the code, and the
> problem seems to when opening the file with vba. If i either open the
> file using File/Open method the file opens and the dates colomn is as
> it should be, or, if I just open the docs folder and double click again
> its as it should be, but if I use the first line of code just to open
> the file, the dates change there foremat some will = 01/01/2005 others
> = 1/1/05. Ive looked for a pattern, eg particular months, but it pretty
> random, It will do a complete month as 05, then the next month in
> colomn as 2005. It seems to only change the year. I cna achieve what I
> need to do manually, just got me curius as to why its doing this.
> Regards Robert

--

Dave Peterson

Dave, that is brilliant. I used on the dates as they are and it worked
perfect, as you say I can inclide that in my code. Thanks for the help
and ofcourse the solution.
Regards Robert

oops, spoke to soon, solved the dates ok, but now in usa format, tried
to format manually but wouldnt let me.
Regards Robert

If it's just a formatting problem, just record a macro when you format that
column the way you like.

If it's not a formatting problem, you're gonna have to share what you did--and
what did the unambiguous format show in those cells???

RobcPettit@yahoo.co.uk wrote:
>
> oops, spoke to soon, solved the dates ok, but now in usa format, tried
> to format manually but wouldnt let me.
> Regards Robert

--

Dave Peterson

Thanks again dave for the reply. Basically what seems to happening is
when, with vba I open the csv file and move into my workbook some of
the years get trimmed from 2005 to 05, and entered as text, so i then
used your suggestion of replacing '/' and this corrected the years. But
altered the dates to us format. So what I then did was to write a macro
which trimmed of the day and month, asked if the year was 05, if so
replaced with 2005, then pasted back to the cell. Again though when
going back to cell it changed to usa format. Now what I tried is after
altering the dates with either yours or my method, Ive used text to
columns, and altered colomn to dates as m/d/y. So far it seem to work
although I habent done this as a macro. The stange thing is there
already m/d/y, but using m/d/y alters to the format I want d/m/y. I
have to alter the year problem first. The only reason I noticed this
problem is because I have to sort the data.
Regards Robert

I'd still make sure the dates are what you expect--using one of those umbiguous
date formats: Feb 10, 2006.

And if you're really opening the .csv file via VBA, you'll have to rename it to
..txt first. Excel's VBA ignores those field settings when it sees the .csv
extension.



RobcPettit@yahoo.co.uk wrote:
>
> Thanks again dave for the reply. Basically what seems to happening is
> when, with vba I open the csv file and move into my workbook some of
> the years get trimmed from 2005 to 05, and entered as text, so i then
> used your suggestion of replacing '/' and this corrected the years. But
> altered the dates to us format. So what I then did was to write a macro
> which trimmed of the day and month, asked if the year was 05, if so
> replaced with 2005, then pasted back to the cell. Again though when
> going back to cell it changed to usa format. Now what I tried is after
> altering the dates with either yours or my method, Ive used text to
> columns, and altered colomn to dates as m/d/y. So far it seem to work
> although I habent done this as a macro. The stange thing is there
> already m/d/y, but using m/d/y alters to the format I want d/m/y. I
> have to alter the year problem first. The only reason I noticed this
> problem is because I have to sort the data.
> Regards Robert

--

Dave Peterson