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

 

[texasalynn]

when you use the text-to-columns on the 3rd part of the wizard select the column with the leading zeros and then click on radio button "text". That will keep the leading zeros

 

[Thorin]

Try using the Data -> Import External Data -> Import Data command, then select your CSV file, set the delimter to Comma and change the field type for your product code to TEXT, this will retain your leading zero's although this will of course now be a text field.

Hope this helps.

 

[Richard Schollar]

Mark

I am slightly unsure as to exactly what you want any code to do - you do know that you don't need to open the file in Notepad to rename it? You can simply change the extension in Windows Explorer (ie via rename) and make it a .txt file?

 

[MarkAndrews]

Mark

I am slightly unsure as to exactly what you want any code to do - you do know that you don't need to open the file in Notepad to rename it? You can simply change the extension in Windows Explorer (ie via rename) and make it a .txt file?

I can, your right, but my previous experiments just wont accept that change Richard?

 

[MarkAndrews]

when you use the text-to-columns on the 3rd part of the wizard select the column with the leading zeros and then click on radio button "text". That will keep the leading zeros

If I save the CSV file to my desktop, then right click & open with Excel the text to columns wizard does not appear? Not are my leading zero’s carried into the file?

 

[Richard Schollar]

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?

 

[MarkAndrews]

Try using the Data -> Import External Data -> Import Data command, then select your CSV file, set the delimter to Comma and change the field type for your product code to TEXT, this will retain your leading zero's although this will of course now be a text field.

Hope this helps.

Good idea in principal, however the CVS file is made up of 6 digits, digits which change each time, so the query would have the be edited each time

 

[MarkAndrews]

Duplicate post - sorry

 

[Thorin]

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

Did you try the import method instead of just opening the CSV file in Excel ?