open CSV.file

[Ranjith Kurian]

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]

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]

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.

 

[Ranjith Kurian]

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]

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.

 

[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]

Hi Dave

I tried it but did not work.

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

 

[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]

Hi Dave,

My date column contains both text value and date values, but if i change my
system setting to UK the date column will get changed to only date values,
after this iam able to change the format manually in format
cells-custom-dd-mmm-yy. but these are not happing through code, i tried your
below code but the file still opens in a US format.

I tried even doing Text to column-Delimited, through below code, there two
problem raised, one the date column contains both text value and date value,
and the second problem was the date did not convert to UK format.

 

[Dave Peterson]

Do it manually after renaming the .csv to .txt--without adjusting your windows
date settings.

Do you get the results you want?

 

[Ranjith Kurian]

Hi Dave,
Thanks for the reply,
As you said i manually changed the csv file to txt and tried to open the
file using the below code it did not allow me to open (macro did not work)
I hope this will work if we change it manually to txt file , if could you
correct my below code.


Sub combine()
Dim intTemp As Integer, arrWorkBook As Variant

arrWorkBook = Array("OPN CH CS.txt", "OPN CH UBS.txt")
For intTemp = 0 To UBound(arrWorkBook)
Workbooks.OpenText Filename:="C:\Ranjith Report\FGA DEBT\Raw\Week" &
arrWorkBook(intTemp), Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8,
1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)),
TrailingMinusNumbers:= _
True

Next
End Sub

 

[Dave Peterson]

Just a guess...

If "C:\Ranjith Report\FGA DEBT\Raw\Week" is a path, then you need to end with a
backslash:

Workbooks.OpenText Filename:="C:\Ranjith Report\FGA DEBT\Raw\Week\" _
& arrWorkBook(intTemp), ...

If that doesn't help, what error do you see when it fails?

 

[Ranjith Kurian]

Hi Dave,

Thanks,

The code works fine, but when the file is opened the date column contain
both text value and Date value. Date value is in UK format but what to do for
text value.

 

[Dave Peterson]

In my testing, the text values were untouched. They remained plain old text.

What happened when you did it?

 

[Ranjith Kurian]

Hi Dave,

For me the text vlaues got changed.

In my testing, the text values were untouched. They remained plain old text.

What happened when you did it?

 

[Dave Peterson]

What were the text values and what did they get changed to?

Really, I don't have another suggestion.