Convert all CSV files in a folder to excel files

  • Hello All,
    I is it possible to convert all comma separated text files in a single folder in to excel files.
    But the requirement is to have 2 sheets in each new file.
    first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.


    One more thing.
    The third column in the second is called "COUL".
    there are short letters for colors in french


    can they be converted with the sort in English like it goes:


    NO = B
    BA = W
    RG = R
    SO = P
    JA = Y
    BE = L
    VE = GY
    GR = G
    VI = V
    MA = BR
    BJ = TA
    OR = O



    Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors.
    It takes me too much time with the find and replace function. And at the moment i`m really pushed from time. sorry for that.


    https://dl.dropboxusercontent.com/u/10255673/CSV_wlist.zip


    Thank you All in advance !

  • Re: Convert all CSV files in a folder to excel files


    Try this..


    Just change the file path from "D:\CSV_wlist\" to whatever suits..


    All created excel files will be placed in the same folder as the Workbook running this code..


    I commented the code heaps (for once).. :)


  • Re: Convert all CSV files in a folder to excel files


    Hello there.
    Thanks for the code, but i really cant find the files - where are they saved ?
    i see the code

    Code
    .SaveAs Filename:=ThisWorkbook.Path & "\" & ZZZ


    but it seems that it is not happening nothing :/

  • Re: Convert all CSV files in a folder to excel files


    very good job apo, but I think need some changes


  • Re: Convert all CSV files in a folder to excel files


    Hi Patel.. thanks.. but I don't think anything needed changing..


    It works perfectly when I test it here..


    My code:

    Code
    ZZ = Split(f(i), "\")(2)

    - Result = "wlist-240140429R_-.txt"


    Your code:

    Code
    ZZ = Right(f(i), Len(f(i)) - Z) ' <<<< changed

    - Result = "wlist-240140429R_-.txt"


    Also..


    My Code:

    Code
    ZZZ = Left(ZZ, Len(ZZ) - 4)

    - Result = "wlist-240140429R_-"


    Your code:

    Code
    ZZZ = Left(ZZ, Len(ZZ) - 4)

    - Result = "wlist-240140429R_-"


    As you can see.. there is no difference in the result..


    Also..


    Your code:

    Code
    .Sheets(1).Delete ' <<<< changed


    Why delete sheet1.. I am pretty sure the OP wanted just 2 sheets in each Workbook.. and as a new workbook starts of with 3 sheets.. and the first 2 sheets contain the data he wants.. not sure why you would want to delete the first sheet??

  • Re: Convert all CSV files in a folder to excel files


    Hi kiler40..


    Quote

    Thanks for the code, but i really cant find the files - where are they saved ?


    It saves the new workbooks to the same path as the Workbook that contains the code..


    So.. for example.. when I run the attached file from the desktop.. the newly created workbooks (with your 2 sheets in each one) are created on the desktop..

  • Re: Convert all CSV files in a folder to excel files


    @ apo
    when I run your code I'm getting zzz = "a", a is the first letter of my user

  • Re: Convert all CSV files in a folder to excel files


    Really nothing is happening :(
    i have placed the file in the same folder where the text files are. Changed the path in the code, and run the program.
    it is thinking for some time, and after "job done" there are no new files in the folder.

  • Re: Convert all CSV files in a folder to excel files


    k.. not sure what is going on..


    See the attached.. i have cut it down to just show the first 400 rows on both sheets (to be under the file size limitations for attachments at Ozgrid)...


    This is created on my system in the folder where the Workbook with the code in it is..


    I have tested it by creating several 'near' copies of your txt files.. and each time i run it.. it creates the correct Excel file similar to the one attached..


    One question.. are yourunning this locally or on a network drive.. if so.. can you post here the filepath you're using..

  • Re: Convert all CSV files in a folder to excel files


    Yes this is the result (without the 2 " " in every cell. but this can be easily deleted.)


    the folder is local "C:\WhireList"


    really don`t know what is happening.

  • Re: Convert all CSV files in a folder to excel files


    Hi kiler40..


    This is strange indeed.. I emulated exactly what you are doing..


    I created the following folder:
    C:\WhireList


    And then added a couple of txt files to it.. and then ran the code..


    Two excel files similar to the ones i attached previously were created in that same Folder..


    Just to check.. are you sure you have put the last backslash in the file path (the backslash after the word "WhireList") ..?


    It should be:

    Code
    f() = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & "C:\WhireList\" & "*.txt"" /s/b").stdout.readall, vbCrLf)
  • Re: Convert all CSV files in a folder to excel files


    Yes.
    first i have downloaded the file you share, and just change the path.
    nothing happened.
    I have tried to copy the code provided from patel. Again nothing.
    then turned to your code back from the post - and again nothing (every-time i change the path as it should be...)


    i have also changed the folder several times - and again nothing...

  • Re: Convert all CSV files in a folder to excel files


    i have just found how to run the code step by step (i`m not very familiar with coding... don`t laugh :) ) and i have found that


    after

    Code
    f() = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & "C:\WhireList\" & "*.txt"" /s/b").stdout.readall, vbCrLf)


    the macro returns "0" on all variables, and jumps directly to the end.

  • Re: Convert all CSV files in a folder to excel files


    ok.. that line is just basically returning all *.txt files in the stated folder and subfolders(/s) in Bare format(/b)..


    If you have files with ".txt" file extension in that folder.. I can't see why it would possibly not be finding them..


    Try going to the Command Window.. (Type cmd and press Enter at the Start>Search) ..


    Type this..


    cd\CSV_wlist then press enter


    then type


    dir *.txt then press enter


    If you have for example 2 txt files in that directory.. it should list them..


    I am determined to get this working for you..

  • Re: Convert all CSV files in a folder to excel files


    yes.
    i`m playing from sometime with cmd to understand what this row is doing.


    if i write in in the starting location of my CMD window it shows



    if i go inside the directory it shows only the files with *.txt and not showing Create Excel Files from CSV.xlsm and File Not Found


    not shure if this have something relevant with the problem.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!