Re: open CSV.file

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

.

Relevant Pages

  • Re: error message
    ... I don't know what was causing that particular workbook to attach itself to ... every excel file I was saving but after I repaired my Excel and shut down my ... "Dave Peterson" wrote: ... I have an EXcel file that every time opens gives me a error message telling ...
    (microsoft.public.excel.misc)
  • Re: Manual Calculation in Excel 2000
    ... "Dave Peterson" wrote: ... I'm still betting that you have another workbook that opens and tells excel how ... I'd look to see what other files are opened when excel starts. ...
    (microsoft.public.excel.misc)
  • Re: access data from a closed wb protected with password without o
    ... It looks like that previous posted message locks just excel--not the desktop ... "Dave Peterson" wrote: ... don't save that workbook with the windows hidden. ... opens does not flash on the screen but it shows in the tool bar at the time ...
    (microsoft.public.excel.programming)
  • Re: Reset workbook back to Auto Calculate
    ... "Dave Peterson" wrote: ... Maybe you have a workbook in that folder. ... Sometimes those other folders can be hard to find. ... that opens when I first start Excel is in Automatic mode. ...
    (microsoft.public.excel.misc)
  • Re: Dynamic Dependant Downloads
    ... I had a book recently that opens a CSV file with dates. ... Send me the workbook, and I'll see what I can do. ... During operation after making a selection from the primary combo the ... I know that this is non-standard as VBA assumes that the US date format is ...
    (microsoft.public.excel.misc)