Convert all CSV files in a folder to excel files

kiler40 · Apr 8th 2014

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 !

apo · Apr 9th 2014

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)..

Code

Private Sub CommandButton1_Click()
    Dim i As Long, ii As Long, x, xx, Z, ZZ, ZZZ, s As String, f() As String
    '\\ Find all txt files in folder and put them into an array
    f() = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & "D:\CSV_wlist\" & "*.txt"" /s/b").stdout.readall, vbCrLf)
    '\\ Loop through the array
    For i = LBound(f) To UBound(f) - 1
        '\\ Determine number of "\" characters in filename
        Z = Len(f(i)) - Len(Replace(f(i), "\", ""))
        '\\ Split string to remove folder path
        ZZ = Split(f(i), "\")(2)
        '\\ Split to remove ".txt" characters(last 4 charactesr)
        ZZZ = Left(ZZ, Len(ZZ) - 4)
        '\\ Read  the txt file
        s = CreateObject("scripting.filesystemobject").getfile(f(i)).openastextstream.readall
        '\\ Split the txt file by the Carriage return at end of each line
        x = Split(s, vbCrLf)
        '\\ Create new Workbook
        With CreateObject("Excel.Application").Workbooks.Add
            .Application.DisplayAlerts = False
            '\\ Loop through all lines and split data by "," delimiter
            For ii = LBound(x) To UBound(x) - 1
                xx = Split(x(ii), ",")
                '\\ Put data into sheet
                .Sheets("Sheet1").Cells(ii + 1, 1).Resize(1, UBound(xx) + 1).Value = xx
            Next ii
            '\\ Rename sheets, Format, delete columns and Save Workbook
            .Sheets("Sheet1").Name = ZZZ
            .Sheets(ZZZ).Columns.AutoFit
            .Sheets(ZZZ).Cells.Copy .Sheets("Sheet2").Cells
            '\\ Replace characters
            With .Sheets("Sheet2").Columns(4)
                .Replace "NO", "B", xlPart
                .Replace "BA", "W", xlPart
                .Replace "RG", "R", xlPart
                .Replace "SO", "P", xlPart
                .Replace "JA", "Y", xlPart
                .Replace "BE", "L", xlPart
                .Replace "VE", "GY", xlPart
                .Replace "GR", "G", xlPart
                .Replace "VI", "V", xlPart
                .Replace "MA", "BR", xlPart
                .Replace "BJ", "TA", xlPart
                .Replace "OR", "O", xlPart
            End With
            .Sheets("Sheet2").Range("C:C,E:E,F:F,I:I,J:J,N:N,Q:Q,U:U").Delete
            .Sheets("Sheet2").Name = Split(ZZZ, "_")(1) & "_-"
            .Sheets("Sheet3").Delete
            .SaveAs Filename:=ThisWorkbook.Path & "\" & ZZZ
            .Application.DisplayAlerts = True
            .Close
        End With
    Next i
    MsgBox "Job Done..!"
End Sub

Display More

kiler40 · Apr 9th 2014

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

patel · Apr 9th 2014

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

very good job apo, but I think need some changes

Code

Private Sub CommandButton1_Click()
    Dim i As Long, ii As Long, x, xx, Z, ZZ, ZZZ, s As String, f() As String
    '\\ Find all txt files in folder and put them into an array
    f() = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & "D:\CSV_wlist\" & "*.txt"" /s/b").stdout.readall, vbCrLf)
    '\\ Loop through the array
    For i = LBound(f) To UBound(f) - 1
        Z = InStrRev(f(i), "\") ' <<<< changed
        ZZ = Right(f(i), Len(f(i)) - Z)' <<<< changed
        ZZZ = Left(ZZ, Len(ZZ) - 4)
        '\\ Read  the txt file
        s = CreateObject("scripting.filesystemobject").getfile(f(i)).openastextstream.readall
        '\\ Split the txt file by the Carriage return at end of each line
        x = Split(s, vbCrLf)
        '\\ Create new Workbook
        With CreateObject("Excel.Application").Workbooks.Add
            .Application.DisplayAlerts = False
            '\\ Loop through all lines and split data by "," delimiter
            For ii = LBound(x) To UBound(x) - 1
                xx = Split(x(ii), ",")
                '\\ Put data into sheet
                .Sheets(1).Cells(ii + 1, 1).Resize(1, UBound(xx) + 1).Value = xx
            Next ii
            '\\ Rename sheets, Format, delete columns and Save Workbook
            .Sheets(1).Name = ZZZ
            .Sheets(ZZZ).Columns.AutoFit
            .Sheets(ZZZ).Cells.Copy .Sheets(2).Cells
            '\\ Replace characters
            With .Sheets(2).Columns(4)
                .Replace "NO", "B", xlPart
                .Replace "BA", "W", xlPart
                .Replace "RG", "R", xlPart
                .Replace "SO", "P", xlPart
                .Replace "JA", "Y", xlPart
                .Replace "BE", "L", xlPart
                .Replace "VE", "GY", xlPart
                .Replace "GR", "G", xlPart
                .Replace "VI", "V", xlPart
                .Replace "MA", "BR", xlPart
                .Replace "BJ", "TA", xlPart
                .Replace "OR", "O", xlPart
            End With
            .Sheets(2).Range("C:C,E:E,F:F,I:I,J:J,N:N,Q:Q,U:U").Delete 
            .Sheets(2).Name = Split(ZZZ, "_")(1) & "_-"
            .Sheets(1).Delete' <<<< changed
            .SaveAs Filename:=ThisWorkbook.Path & "\" & ZZZ
            .Application.DisplayAlerts = True
            .Close
        End With
    Next i
    MsgBox "Job Done..!"
End Sub

Display More

apo · Apr 9th 2014

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??

apo · Apr 9th 2014

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..

patel · Apr 9th 2014

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

kiler40 · Apr 9th 2014

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.

apo · Apr 9th 2014

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..

kiler40 · Apr 9th 2014

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.

apo · Apr 9th 2014

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

Just quick one..

Try changing that to another drive/location.. you may have permissions issues..

apo · Apr 9th 2014

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

@ patel..

Take a look at a screenshot of my Locals Window.. maybe that will shed more light..

Image:

http://www.mediafire.com/view/…nq5nlcw/Locals_Window.png

snb · Apr 9th 2014

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

The title of this thread is talking about csv files.

The code is looking for txt files

apo · Apr 9th 2014

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)

apo · Apr 9th 2014

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

snb..

yeah.. but the files he attached that had his raw data were txt files..

kiler40 · Apr 9th 2014

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...

kiler40 · Apr 9th 2014

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.

apo · Apr 9th 2014

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..

kiler40 · Apr 9th 2014

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

Code

C:\Users\andre>dir C:\WhireList *.txt /b
Create Excel Files from CSV.xlsm
wlist_240140429R_-.txt
wlist_240140460R_-.txt
.
.
.
.
.
.
wlist_240184671S_-.txt
wlist_240189592S_-.txt
File Not Found




C:\Users\andrei.kotelnikov>

Display More

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.

snb · Apr 9th 2014

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

use

Code

sub M_snb()
  msgbox dir("C:\WhireList\*.txt")
End sub

Convert all CSV files in a folder to excel files

Participate now!

Share