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]

Is the file Read-Only? Right-click on it in Explorer and select Properties (the Read Only attribute is at the bottom). If this isn't the case, please explain more fully.

Have you tried Thorin's suggestion?

Hi Richard, hope you are keeping well?

The file isn’t read only?

Basically, the file is downloaded from our FTP server (customers use this for price files)

We use the same files for a different purpose

I download the file from FTP, Save to a folder on my desktop, (try) and open the CSV in excel, from which just two columns are needed (Product ID & Price) this then goes into a model (Which I built) which analyses the data more, and uses the data gained from the CSV file for a vlookup, obviously Vlookup works on exact match, which is why I need the leading Zero’s to appear


Also the CSV filename isn't static

 

[MarkAndrews]

Can you give us a couple of samples of the data ?

Unfortunately not, my IT dept wont let me have HTML maker add in?

 

[Thorin]

You could just copy and paste some data, or if its sensitive just type in some examples of the same type.

Did you actually import the data into Excel from the CSV file, or just opened the CSV file in Excel?

 

[Richard Schollar]

Mark

It's easy enough to write a script that would rename the csv file (to a txt) and then open it up (which would fire the Text Import dialog). Where does it get downloaded to, where do you want it to end up (ie which folder), is it always a standard format (if so, you dont need the text import wizard - you can specify the import format)?

 

[MarkAndrews]

Mark

It's easy enough to write a script that would rename the csv file (to a txt) and then open it up (which would fire the Text Import dialog). Where does it get downloaded to, where do you want it to end up (ie which folder), is it always a standard format (if so, you dont need the text import wizard - you can specify the import format)?

Hi Richard the folder is called

C:\Documents and Settings\andrewm\Desktop\FTP Files

The CSV File name changes each time I download a file from the FTP server

The Excel File hasn’t been named yet, but will sit in the same folder in my desktop & is where the macro should sit

 

[Richard Schollar]

Possibly a simple amendment to your Vlookup would work as it sounds like you hve outwardly numerical data (that is in text format to maintain the leading zeros). If so, then you could simply modify your Vlookup:

=VLOOKUP(TEXT(A1,"0000000"),myRange,2,0)

etc

 

[MarkAndrews]

Possibly a simple amendment to your Vlookup would work as it sounds like you hve outwardly numerical data (that is in text format to maintain the leading zeros). If so, then you could simply modify your Vlookup:

=VLOOKUP(TEXT(A1,"0000000"),myRange,2,0)

etc

Not all of the product ID’s have leading zero’s tho? (In an ideal world they would have)

Even the ones which do, only have one leading zero?

 

[Richard Schollar]

Is there a standard product format though ie they are always 8 digits long (including leading zeros)? If not then I will put together that script I said was possible. Which columns/fields in the Csv file are required? The first two, first and third - which?

 

[MarkAndrews]

Is there a standard product format though ie they are always 8 digits long (including leading zeros)? If not then I will put together that script I said was possible. Which columns/fields in the Csv file are required? The first two, first and third - which?

Sadly all of the product ID's can be different lengths Richard

The columns used from the CSV file are

Column A (A:A) & Column J (J:J)

Apologies for my late reply, I had left for the day

Many thanks for all your help

 

[Richard Schollar]

Good morning Mark

Plonk this in and change the two CONST to whatever the drive letter is of the location where you save the downloaded csv files to and the full path of the location too. Then run it.

Sub OpenCSVAsText()
Const NEW_DRIVE As String = "C"
Const NEW_DIR As String = "C:\Test1\"
Dim currDir As String
Dim sFullName As String, 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 = "" 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