How to override text import for CSV ?

[StevePB]

I receive files from a customer, which are pipe (|) delimited (as required by my specification) but this particular customer's file names end with ".csv" (in fact they are like: "order1.txt.csv").

I have written code to import the file into a workbook, using the pipe symbol as the delimiter, but when the code is run, Excel just assumes that it should be comma seperated, and puts everything on one line into one cell.

Does anyone know how I can import a file with a ".csv" extension, overriding the default behaviour ?

The file format looks like this:

12345|2| |
67890|5| |

I want each field in a seperate cell, and the pipes removed.

My code looks like this:

Workbooks.OpenText fileName:=fName, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, _
        textqualifier:=xlTextQualifierNone, comma:=False, _
        Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2), _
        Array(2, 2), Array(3, 2))

I would be grateful for any suggestions.

 

[Chance1234]

reference the filesystem object and use the following

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "drder1.txt.csv", "drder1.txt"


Filmmaker, gentleman and [#Error]

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[StevePB]

Thanks Chance1234.

I was hoping to avoid renaming the file, as I am trying to standardise the code for all customers, and don't want to put too many exceptions in.

- SB.

 

[SkipVought]

Steve,

I don't know if there's any other way aroun this, but here's a post import process approch that will work for Excel versions greater than 97...

Sub Main()
'fName = "C:\Documents and Settings\My Documents\vbaexcel\aacsv.csv"
Workbooks.OpenText _
    Filename:=fName
    PostProcess
End Sub
Sub PostProcess()
    With ActiveSheet.UsedRange
        For r = 1 To .Rows.Count
            s = ""
            For c = 1 To .Columns.Count
                If s = "" Then
                    s = Cells(r, c).Value
                Else
                    s = s & ", " & Cells(r, c).Value
                End If
            Next
            If Right(s, 2) = ", " Then s = Left(s, Len(s) - 2)
            Rows(r).ClearContents
            a = Split(s, "|")
            For c = 0 To UBound(a, 1)
                Cells(r, c + 1).Value = a(c)
            Next
        Next
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Chance1234]

how about knocking it at source ?

so the user can only save the file as .txt

this could be done by using the instr function before the file is saved



Filmmaker, gentleman and [#Error]

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[SkipVought]

It sounded to me like he had no control of the source. It sure would be easier that way, though.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[StevePB]

Skip, thanks, but as I said I'm trying to avoid specific coding for this customer. I guess I'm looking for something I can add to the generic text import routine that will work for a file with any name.

Chance123 - that would work except that these are customers generating their own files to fit a broad specification by whatever means they have. I don't have as much choice in the matter as I would like.

I'm coming from the angle that if I have a coding tool, what is the point of Excel ignoring my code and assuming it knows better ? I was hoping there was a parameter somewhere that I'd missed. I'm thinking I may need to replace OpenText with Open ... For Input.

-SB.

 

[SkipVought]

Steve,

First you say,

I'm trying to avoid specific coding for this customer

Then you say,

I'm thinking I may need to replace OpenText with Open ... For Input.

Isn't that specific coding for this customer???


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Chance1234]

Or in your code just put a test

IF instr(filename, .txt.csv) then
rename the file
end if

then your open code

thats only three extra lines.

Alternativly when they send in the files, return it back to them and say you can only accept it in this format.



Filmmaker, gentleman and [#Error]

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[SkipVought]

Chance1234,

That's probably the best approch.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[StevePB]

Skip, I meant replacing the OpenText method that I am using for files from any customer (and which imposes the limitation on file extension) with Open...For Input which doesn't care what the file is called, and will therefore solve my current problem - it's just more fiddley.

Chance1234, I agree it's not much code - maybe I'll do that.

Thanks for your help guys.

-SB.