Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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" wrote: 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
Do it manually after renaming the .csv to .txt--without adjusting your windows
date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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" wrote: Do it manually after renaming the .csv to .txt--without adjusting your windows date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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 wrote: 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" wrote: Do it manually after renaming the .csv to .txt--without adjusting your windows date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson . -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
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" wrote: 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 wrote: 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" wrote: Do it manually after renaming the .csv to .txt--without adjusting your windows date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson . -- Dave Peterson . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
In my testing, the text values were untouched. They remained plain old text.
What happened when you did it? Ranjith Kurian wrote: 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" wrote: 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 wrote: 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" wrote: Do it manually after renaming the .csv to .txt--without adjusting your windows date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
Hi Dave,
For me the text vlaues got changed. "Dave Peterson" wrote: In my testing, the text values were untouched. They remained plain old text. What happened when you did it? Ranjith Kurian wrote: 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" wrote: 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 wrote: 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" wrote: Do it manually after renaming the .csv to .txt--without adjusting your windows date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
open CSV.file
What were the text values and what did they get changed to?
Really, I don't have another suggestion. Ranjith Kurian wrote: Hi Dave, For me the text vlaues got changed. "Dave Peterson" wrote: In my testing, the text values were untouched. They remained plain old text. What happened when you did it? Ranjith Kurian wrote: 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" wrote: 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 wrote: 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" wrote: Do it manually after renaming the .csv to .txt--without adjusting your windows date settings. Do you get the results you want? Ranjith Kurian wrote: 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" wrote: 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 . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
Open a file do a macro ( made) and open next succesive file | Excel Programming | |||
Open File or Switch Between Windows if File is Open | Excel Programming | |||
Open File or Switch Between Windows if File is Open | Excel Programming |