Problems with CSV file input

[Alan]

I have a macro that opens a CSV file. The problem is that it
recognizes text fields containing slashes as dates, which I do not
want.

I have searched for a solution with Google, but none of them seem to
work. I tried from the Excel menu importing CSV files as external
data using General and then Text format, but this did not work. I
tried changing the file extension to .vcs or .txt and then importing.
This did not work with either General nor Text form.

Can anyone tell me how to get around this problem?

I am using Excel 2003. I also have 2007.

Thanks, Alan

 

[Patrick Molloy]

one way

read the data in using the OPEN method, then you control the input more
easily

use the READ LINE method to real a comma separated line of text out of your
file, the use the SPLIT command to break it into pieces
All you need to do then is from each into a cell....and if the cell is
formatted as TEXT then Excel won't try top convert it.

there are other ways to do this of course, but this is meant to be quite
simplistic

not tested, but this will help get you going

Option Explicit

Sub readCSV()

Dim ff As Long
Dim text As String
Dim index As Long
Dim data As Variant
Dim rw As Long

ff = FreeFile
Open "C:\temp\demo4.csv" For Input As #ff
Do While Not EOF(ff)
Line Input #ff, text
data = Split(text, ",")
rw = rw + 1
For index = LBound(data, 1) To UBound(data, 1)
With Cells(rw, index + 1)
.NumberFormat = "@"
.Value = data(index)
End With
Next
Loop


End Sub

 

[Gary''s Student]

Your macro must do exactly what a human would do faced with the same situation.

HUMAN:

Data > Import External Data > Import data...

The human would then tell the Import Wizard the filespec and the format of
the various fields. Thus 12/25/2009 can be imported as Text rather than Date.

Just switch on the Recorder while the human pecks away at the keyboard and
then make adaptations:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/2/2009 by James Ravenswood
'

'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\test
folder\x.csv", _
Destination:=Range("A1"))
.Name = "x"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 18
End Sub

 

[Alan]

Your macro must do exactly what a human would do faced with the same situation.

HUMAN:

Data > Import External Data > Import data...

The human would then tell the Import Wizard the filespec and the format of
the various fields.  Thus 12/25/2009 can be imported as Text rather than Date.

BUT I tried this. It did not work. Neither General nor Text import
worked. See original post.

Thanks, Alan

 

[Jacob Skaria]

Alan

Try below

1. Rename the file to txt
2. File>Open>*.txt
3. From Text Import Wizard Step 1 ..hit Next
4. Select comma as the delimiter in Step 2
5. In Step 3 , from Data Preview area..>select the column you want to be
displayed as text. If you want all columns to be selected with Shift key
pressed select all fields.. The background of selected fields wil be black.

Hit OK

Try this and proceed with recording a macro....if this is not for a one time
business.

If this post helps click Yes

 

[Alan]

This did not work either. Alan