I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)


when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson

Hi Dave,

Thanks for your informations.

As you said in the below that its better to convert the .csv file to .txt
file, the problem here is the txt file allways separate the columns based on
Comma, so suppose if there are two columns like Names and Amounts, and if
any name contain comma, the name will be separated to next column where
amount was suppose to be.

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.

Rename or copy a .csv file to .txt

Record a macro when you open the file. You'll be prompted for the type of
data--fixed width or delimited. Choose delimited and then by a comma.

The wizard will continue and you'll be able to specify how each field should be
treated. And you'll be able to choose the correct mdy order for date fields,
too.



Ranjith Kurian wrote:

Hi Dave,

Thanks for your informations.

As you said in the below that its better to convert the .csv file to .txt
file, the problem here is the txt file allways separate the columns based on
Comma, so suppose if there are two columns like Names and Amounts, and if
any name contain comma, the name will be separated to next column where
amount was suppose to be.

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.


--

Dave Peterson

Look at the filecopy statement in VBA's help. One way (with no validity
checks):

Option Explicit
Sub testme02()

Dim myOrigFilename As String
Dim myNewFileName As String
Dim TempWkbk As Workbook
Dim Wkbk As Workbook

myOrigFilename = "C:\my documents\excel\book1.csv"

If LCase(Right(myOrigFilename, 4)) = LCase(".csv") Then
myNewFileName = myOrigFilename & ".txt" 'just append .txt
FileCopy Source:=myOrigFilename, Destination:=myNewFileName
Else
myNewFileName = myOrigFilename 'just plop it in
End If

'your modified recorded code goes here
Workbooks.OpenText Filename:=myNewFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 3), Array(2, 1), _
Array(3, 2), Array(4, 1)), _
TrailingMinusNumbers:=True

'opens the file in worksheet in a new workbook
Set TempWkbk = ActiveWorkbook

'copy the sheet to a different new workbook
'so that the text file can be closed
ActiveSheet.Copy 'to a new workbook
Set Wkbk = ActiveWorkbook

TempWkbk.Close savechanges:=False

Wkbk.Activate

If myOrigFilename < myNewFileName Then
'delete the temporary .txt file
Kill myNewFileName
End If

End Sub


Ranjith Kurian wrote:

Hi Dave,

Thanks for your informations.

As you said in the below that its better to convert the .csv file to .txt
file, the problem here is the txt file allways separate the columns based on
Comma, so suppose if there are two columns like Names and Amounts, and if
any name contain comma, the name will be separated to next column where
amount was suppose to be.

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.


--

Dave Peterson

Hi Dave

I tried it but did not work.



"Dave Peterson" wrote:

Look at the filecopy statement in VBA's help. One way (with no validity
checks):

Option Explicit
Sub testme02()

Dim myOrigFilename As String
Dim myNewFileName As String
Dim TempWkbk As Workbook
Dim Wkbk As Workbook

myOrigFilename = "C:\my documents\excel\book1.csv"

If LCase(Right(myOrigFilename, 4)) = LCase(".csv") Then
myNewFileName = myOrigFilename & ".txt" 'just append .txt
FileCopy Source:=myOrigFilename, Destination:=myNewFileName
Else
myNewFileName = myOrigFilename 'just plop it in
End If

'your modified recorded code goes here
Workbooks.OpenText Filename:=myNewFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 3), Array(2, 1), _
Array(3, 2), Array(4, 1)), _
TrailingMinusNumbers:=True

'opens the file in worksheet in a new workbook
Set TempWkbk = ActiveWorkbook

'copy the sheet to a different new workbook
'so that the text file can be closed
ActiveSheet.Copy 'to a new workbook
Set Wkbk = ActiveWorkbook

TempWkbk.Close savechanges:=False

Wkbk.Activate

If myOrigFilename < myNewFileName Then
'delete the temporary .txt file
Kill myNewFileName
End If

End Sub


Ranjith Kurian wrote:

Hi Dave,

Thanks for your informations.

As you said in the below that its better to convert the .csv file to .txt
file, the problem here is the txt file allways separate the columns based on
Comma, so suppose if there are two columns like Names and Amounts, and if
any name contain comma, the name will be separated to next column where
amount was suppose to be.

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.


--

Dave Peterson
.

It worked for me.

Maybe you should try it again or even explain what happened and how it failed
when you tried it.

Ranjith Kurian wrote:

Hi Dave

I tried it but did not work.

"Dave Peterson" wrote:

Look at the filecopy statement in VBA's help. One way (with no validity
checks):

Option Explicit
Sub testme02()

Dim myOrigFilename As String
Dim myNewFileName As String
Dim TempWkbk As Workbook
Dim Wkbk As Workbook

myOrigFilename = "C:\my documents\excel\book1.csv"

If LCase(Right(myOrigFilename, 4)) = LCase(".csv") Then
myNewFileName = myOrigFilename & ".txt" 'just append .txt
FileCopy Source:=myOrigFilename, Destination:=myNewFileName
Else
myNewFileName = myOrigFilename 'just plop it in
End If

'your modified recorded code goes here
Workbooks.OpenText Filename:=myNewFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 3), Array(2, 1), _
Array(3, 2), Array(4, 1)), _
TrailingMinusNumbers:=True

'opens the file in worksheet in a new workbook
Set TempWkbk = ActiveWorkbook

'copy the sheet to a different new workbook
'so that the text file can be closed
ActiveSheet.Copy 'to a new workbook
Set Wkbk = ActiveWorkbook

TempWkbk.Close savechanges:=False

Wkbk.Activate

If myOrigFilename < myNewFileName Then
'delete the temporary .txt file
Kill myNewFileName
End If

End Sub


Ranjith Kurian wrote:

Hi Dave,

Thanks for your informations.

As you said in the below that its better to convert the .csv file to .txt
file, the problem here is the txt file allways separate the columns based on
Comma, so suppose if there are two columns like Names and Amounts, and if
any name contain comma, the name will be separated to next column where
amount was suppose to be.

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson

could you tell me how exactly can i convert the csv file to text file and
again bring back to excel through macro

"Dave Peterson" wrote:

I'm surprised that there was any difference in the way the data was treated.

In my simple tests (using USA date settings (mdy order)), both worked the same
way.

But (if I recall correctly), opening CSV files via code will use USA settings.

I wouldn't do it. Instead I'd rename (or copy) the .csv files to .txt (manually
or in code) and use .opentext to open the file(s). Then I could specify each
field the way I wanted.

I'd be very concerned about date fields coming in as date fields--but not
representing the date in the actual source (01/02/03 could come in as Jan 2,
2003 or Feb 1, 2003 or ...).

I'd want to make sure all the ambiguous dates would be brought in correctly.

Ranjith Kurian wrote:

I have a lot of csv file, in that file the date column contain both text and
date values, but when i change my system settings to UK and if i manually
change the date format using custom type "dd-mmm-yy", all the date and text
value of that column will easily change to dd-mmm-yy, but when i created a
macro to do the same thing, the problem iam facing here is when it open each
csv files the date is getting changed(the column contains both text and date
values)

when i open the file using the below code it opens the file in a actual
format(date values will not change)

ChDir "C:\Ranjith Report\FGA DEBT\Raw"
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\ABC.csv"

but when i open the file using the below code it opens in a different
format(date value changes)

arrWorkBook = Array("ABC.csv","DEF.csv")
Workbooks.Open Filename:="C:\Ranjith Report\FGA DEBT\Raw\" &
arrWorkBook(intTemp)

--

Dave Peterson
.