CSV & Excel File giving me a headache

[MarkAndrews]

Hi Guys,

I have a huge problem I need your help with

I have a file which I download from a FTP server (CSV)

I copy the file into a folder in my desktop (The file name changes each time & isn’t static)

Then, open the file in notepad, save again to the folder in my desktop (As .TXT)

Then use excel to open the TXT version of the file (from the folder on my desktop) & perform text to columns in excel

I know that the easiest way of doing this would be to open the CSV file in excel & perform Text To Columns, except some of the data in the CSV file has leading zero’s in some of the product codes & for whatever reason it will not perform as required

Is the above even possible in VBA?

TIA

 

[MarkAndrews]

Good afternoon Richard

I get an error "Path Not Found" on this part of your code

"ChDir NEW_DIR"

 

[Richard Schollar]

Did you do as I advised in my post and change the following two constants to whatever was relevant for your file location?

Const NEW_DRIVE As String = "C" 
Const NEW_DIR As String = "C:\Test1\"

 

[MarkAndrews]

Duplicate reply

 

[MarkAndrews]

Duplicate reply

 

[MarkAndrews]

Duplicate reply

 

[MarkAndrews]

Did you do as I advised in my post and change the following two constants to whatever was relevant for your file location?

Const NEW_DRIVE As String = "C" 
Const NEW_DIR As String = "C:\Test1\"

Doh, I forgot that bit

I'll retry, thanks Richard

 

[Richard Schollar]

Mark

I should have dim'ed one of the variables as a variant (that holds the return value of GetOpenFileName) - so you should replace the original code with the following:

Sub OpenCSVAsText()
Const NEW_DRIVE As String = "C"
Const NEW_DIR As String = "C:\Test1\"
Dim currDir As String
Dim sFullName As Variant, sNewName As String
Dim myArray

myArray = Array(Array(1, xlTextFormat), Array(2, xlSkipColumn), Array(3, xlSkipColumn), _
        Array(4, xlSkipColumn), Array(5, xlSkipColumn), Array(6, xlSkipColumn), _
        Array(7, xlSkipColumn), Array(8, xlSkipColumn), Array(9, xlSkipColumn), _
        Array(10, xlTextFormat))

currDir = CurDir

ChDrive NEW_DRIVE
ChDir NEW_DIR

sFullName = Application.GetOpenFilename("CSV Files (*.csv),*.csv")

If sFullName = False Then MsgBox "No File Selected": Exit Sub

sNewName = Left$(sFullName, Len(sFullName) - 3) & "txt"

Name sFullName As sNewName

Workbooks.OpenText Filename:=sNewName, DataType:=xlDelimited, _
    comma:=True, fieldinfo:=myArray
    
ChDrive Left$(currDir, 1)
ChDir currDir
End Sub

 

[MarkAndrews]

Hi Richard

I've adapted the code as follows

However, someone who has seen this would like to use the code to extract all the columns from the CSV file, can this be done? (I dont know how to)

Thanks so much for your help

Sub OpenCSVAsText()
Const NEW_DRIVE As String = "C"
Const NEW_DIR As String = "C:\Documents and Settings\andrewm\Desktop\FTP Files"
Dim currDir As String
Dim sFullName As Variant, sNewName As String
Dim myArray

myArray = Array(Array(1, xlTextFormat), Array(2, xlSkipColumn), Array(3, xlSkipColumn), _
        Array(4, xlSkipColumn), Array(5, xlSkipColumn), Array(6, xlSkipColumn), _
        Array(7, xlSkipColumn), Array(8, xlSkipColumn), Array(9, xlSkipColumn), _
        Array(10, xlTextFormat))

currDir = CurDir

ChDrive NEW_DRIVE
ChDir NEW_DIR

sFullName = Application.GetOpenFilename("CSV Files (*.csv),*.csv")

If sFullName = False Then MsgBox "No File Selected": Exit Sub

sNewName = Left$(sFullName, Len(sFullName) - 3) & "txt"

Name sFullName As sNewName

Workbooks.OpenText Filename:=sNewName, DataType:=xlDelimited, _
    comma:=True, fieldinfo:=myArray
    
ChDrive Left$(currDir, 1)
ChDir currDir
End Sub

 

[Richard Schollar]

Mark

Assuming that whoever wants all the columns up to column J extracted as Text (ie so Excel doesn't try and remove any leading zeros) then you just need to amend the array passed to FieldInfo:

Sub OpenCSVAsText() 
Const NEW_DRIVE As String = "C" 
Const NEW_DIR As String = "C:\Documents and Settings\andrewm\Desktop\FTP Files" 
Dim currDir As String 
Dim sFullName As Variant, sNewName As String 
Dim myArray 

myArray = Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), _ 
        Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), _ 
        Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), _ 
        Array(10, xlTextFormat)) 

currDir = CurDir 

ChDrive NEW_DRIVE 
ChDir NEW_DIR 

sFullName = Application.GetOpenFilename("CSV Files (*.csv),*.csv") 

If sFullName = False Then MsgBox "No File Selected": Exit Sub 

sNewName = Left$(sFullName, Len(sFullName) - 3) & "txt" 

Name sFullName As sNewName 

Workbooks.OpenText Filename:=sNewName, DataType:=xlDelimited, _ 
    comma:=True, fieldinfo:=myArray 
    
ChDrive Left$(currDir, 1) 
ChDir currDir 
End Sub

If you have columns beyond column J, then you simply need to add new arrays to myArray:

myArray = Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), _ 
        Array(4, xlTextFormat), Array(5, xlTextFormat), Array(6, xlTextFormat), _ 
        Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), _ 
        Array(10, xlTextFormat), Array(11, xlTextFormat), Array(12, xlTextFormat), Array(13, xlTextFormat), Array(14, xlTextFormat))

'etc ect

...remembering to increment the column reference (ie Array(12,xlTextFormat) refers to column 12 - ie "L" column).

Does this help?

 

[MarkAndrews]

ray:

Thanks Richard

I'll let you know how i get on