Csv To Xlsx: Import Export values from Comma delimeted text file to Excel worksheet

Code:

---

Sub CSVToXLS()
    Dim fPath  As String, fPathDONE As String, fCOUNT As Long
    Dim fName  As String, fType    As String
    Dim fAfter As String, NwName    As String
 
    fPath = ThisWorkbook.Path & "\Test\"
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
 
    fPathDONE = fPath & "\Converted\"
    MakeFolders fPathDONE
 
    fName = Dir(fPath & "*.CSV")
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Do While Len(fName) > 0
        NwName = Left(fName, InStrRev(fName, ".") - 1)
        Workbooks.Open fPath & fName
        ActiveSheet.Name = NwName
        ActiveWorkbook.SaveAs fPath & NwName & ".xls", FileFormat:=xlNormal
        ActiveWorkbook.Close
     
        Name fPath & fName As fPathDONE & fName
     
        fCOUNT = fCOUNT + 1
        fName = Dir()
    Loop
 
    MsgBox "A Total Of " & fCOUNT & " Files Were Processed"
    Application.ScreenUpdating = True
End Sub

Function MakeFolders(MyStr As String)
    Dim MyArr  As Variant
    Dim pNum    As Long
    Dim pBuf    As String
 
    On Error Resume Next
 
    MyArr = Split(MyStr, "\")
    pBuf = MyArr(LBound(MyArr)) & "\"
    For pNum = LBound(MyArr) + 1 To UBound(MyArr)
        pBuf = pBuf & MyArr(pNum) & "\"
        MkDir pBuf
    Next pNum
End Function

---

I Have a Macro that works perfectly & I need a little changes in it, Changes are mentioned Below
1)this macro works only if the file is located in test folder & I want to define the path in the macro, My file alway's be located in C:\Users\WolfieeeStyle\Desktop\Alert..csv & this macro should convert only that file from .csv to .xls & it should delete the .csv file & keep the .xls file to the same location
And I don't want any msg & any notification stating anything
So request u to plz look into it

Your macro above uses the Workbooks.Open ____________.csv type way to open/ use a .CSV file which we said must be avoided since there will always be problems in it ( https://excelfox.com/forum/showthrea...ll=1#post13710
https://excelfox.com/forum/showthrea...ll=1#post13709 )
Your macro above uses the CSV as you have said you will never do. ( But based on passed experience with you , I expect you will use it and we will go around in circles again for ever and get no where...)
So best is to ignore that macro and any variations of it. That is very important since that has already wasted a lot of peoples time, including yours for the last two months...
So start again / try again to explain...

---

We are not really talking about converting Csv to Xlsx
Csv are usually text files
Xlsx are usually Excel files.
You cannot convert one to the other.

I think you want to do this question:
Question.
_ Import values from a comma separated values text file into an Excel Worksheet.
_ Save the Excel File with the imported values. This will be a new file. It will be an Excel file containing just the values from the text file. It should not contain the comma separators. Each value should be in a cell. The text file lines should correspond to the Excel file rows.
Give Excel file a similar name to that of the text file, ( For example: If text file name is Alert..csv, then the name of the Excel file should be Alert..xls

Answer
( Same question and answer as many of your forum posts. Here are just a few of many example: Code for text file to Excel : http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364 )

Try again….
A comma separated values file will usually also need a separator for the lines. So far I have seen you use 2 different forms,
vbCr & vbLf
and
vbLf
I have also seen you supply so called ".csv" files , which take many forms, and can also have different forms values separators ( http://www.eileenslounge.com/viewtop...268716#p268716 )
The most common used values separators are commas , _ But others include | ; vbTab

Therefore, I think it will save us time to make a function which takes in the values separators and the line.

Note: With Text files we must concern ourselves with the Record/Line(row) separator and the Field(column) Separator : They may vary. ##We must know about these. ##
( In Excel we do not have to concern ourselves with the row separator used internally by Excel ( vbCr & vbLf ), or the column Separator used internally by Excel ( vbTab ) : Excel does this for us. We do not need to add these when working with Excel Files. Internally, Excel uses those separators to make the cells that we see and work with. )

Different File Types used for simple values
See here ( This post https://excelfox.com/forum/showthrea...ge30#post13349 ) for typical comparisons of text Files, Excel files, and data files
Text File: https://excelfox.com/forum/showthrea...ll=1#post13693
Excel File: https://excelfox.com/forum/showthrea...ll=1#post13694
Data File: https://excelfox.com/forum/showthrea...ll=1#post13695

---

Function to make an Excel files from a text file containing values and separators

XLFlNme is the Excel File name wanted for the new File
TxtFlNme is Text File name of an existing text file
valSep is the values separator used in the existing text file##
LineSep is the line separator used in thee existing text file##
Paf it the path to the files. ( I assume they are at the same place for the existing text file and the new Excel File )

The function is almost identical to the macro I did for you here: Code for Text File to Excel https://eileenslounge.com/viewtopic....269105#p269105
The function is here: https://excelfox.com/forum/showthrea...ll=1#post13717

It is a function.
So you will need to call it with a test macro such as this:

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
Sub Test_MakeXLFileusingvaluesInTextFile()
Dim Pf As String
Let Pf = ThisWorkbook.Path  '                ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "sample2BEFORE..csv", "Test.xlsx", ",", vbCr & vbLf)
End Sub


---

I tested it using this text file: Share 'sample2BEFORE..csv' : https://app.box.com/s/a3o4irgofydb71e3o0c4aaxefg6dw3bi
NSE,101010,6,<,12783,A,,,,,GTT
NSE,22,6,<,12783,A,,,,,GTT
NSE,17388,6,<,12783,A,,,,,GTT

Running the test macro results in an Excel File being made looking like this:

_____ Workbook: Test.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE	101010	6	<	12783	A					GTT
2	NSE	22	6	<	12783	A					GTT
3	NSE	17388	6	<	12783	A					GTT
4

Worksheet: Sheet1

---

I have not addressed the issue of deleting the text file. You can probably do that yourself. ( It is not a good idea to do that, since a back up of your data is probably a good idea to have. )

I am mostly away now until Sunday.
Alan

---

sample2BEFORE..csv : https://app.box.com/s/a3o4irgofydb71e3o0c4aaxefg6dw3bi
Test.xlsx : https://app.box.com/s/cty8ldv85f4kn61pk2u049yqv0szk0uc
Vba.xlsm : https://app.box.com/s/lf6otsrl42m6vxxvycjo04zidya6pd2m
Function MakeXLFileusingvaluesInTextFile() : https://excelfox.com/forum/showthrea...ll=1#post13717

Which macro i have to run to get the desired result?
As i can see u have given

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
Sub Test_MakeXLFileusingvaluesInTextFile()
Dim Pf As String
Let Pf = ThisWorkbook.Path  '                ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "sample2BEFORE..csv", "Test.xlsx", ",", vbCr & vbLf)
End Sub

---

And this

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
'XLFlNme is the Excel File name wanted for the new File
'TxtFlNme is Text File name of an existing text file
'valSep is the values separator used in the existing text file
'LineSep is the line separator used in thee existing text file
'Paf it the path to the files. ( I assume they are at the same place for the existing text file and the new Excel File )

Function MakeXLFileusingvaluesInTextFile(ByVal Paf As String, ByVal TxtFlNme As String, ByVal XLFlNme As String, ByVal valSep As String, ByVal LineSep As String)

Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = Paf & Application.PathSeparator & TxtFlNme  '                                                              CHANGE TO SUIT                                                                                                        From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629    DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by Line Seperator
Dim arrRws() As String: Let arrRws() = Split(TotalFile, LineSep, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5  etc...
' 2c) split first line to determine the Field(column) number
Dim arrClms() As String: Let arrClms() = Split(arrRws(0), valSep, -1, vbBinaryCompare)
Dim ClmCnt As Long: Let ClmCnt = UBound(arrClms()) + 1
' 2d) we can now make an array for all the rows, and  columns
Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To ClmCnt)

Rem 3 An array is built up by _....
Dim Cnt As Long
    For Cnt = 1 To RwCnt '              _.. considering each row of data
    'Dim arrClms() As String
    Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
    Dim Clm As Long  '
        For Clm = 1 To UBound(arrClms()) + 1
        Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt

Rem 4  Finally the array is pasted to a worksheet in a new file
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=Paf & Application.PathSeparator & XLFlNme, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 Workbooks("" & XLFlNme & "").Worksheets.Item(1).Range("A1").Resize(RwCnt, ClmCnt).Value = arrOut()

End Function

---

there are two things
can i get one macro that will do the process,I will run the macro and it will do the process

You have used functions before, both from me, and from other people, many, many times, ( like Function CL() )
You put both macros in the same place
You run just Sub Test_MakeXLFileusingvaluesInTextFile()
The function, Function MakeXLFileusingvaluesInTextFile(), will be used when needed by Sub Test_MakeXLFileusingvaluesInTextFile()

I explained in great detail why I am using a function.

I suggest you read again what I have written.

You run just Sub Test_MakeXLFileusingvaluesInTextFile() and it will do the process!

You have just one macro that will do the process, you run the macro , Sub Test_MakeXLFileusingvaluesInTextFile() , and it will do the process....

I wanted to inform u that in that file there are 25 macros & it will be run back to back
If I used this it will not cause any errors with other macros?

It should not cause any problems. I cannot think of any reasons why it should

Error details:
Run time error 76
Path not found

I have used this

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
Sub Test_MakeXLFileusingvaluesInTextFile()
Dim Pf As String
Let Pf = "C:\Users\WolfieeeStyle\Desktop\Alert..csv"              ' CHANGE TO SUIT
'let pf = "C:\Users\WolfieeeStyle\Desktop"  ' CHANGE TO SUIT
 Call MakeXLFileusingvaluesInTextFile(Pf, "sample2BEFORE..csv", "Test.xlsx", ",", vbCr & vbLf)
End Sub

---

and

Code:

---

' https://excelfox.com/forum/showthread.php/2519-Convert-Csv-To-Xlsx
'XLFlNme is the Excel File name wanted for the new File
'TxtFlNme is Text File name of an existing text file
'valSep is the values separator used in the existing text file
'LineSep is the line separator used in thee existing text file
'Paf it the path to the files. ( I assume they are at the same place for the existing text file and the new Excel File )

Function MakeXLFileusingvaluesInTextFile(ByVal Paf As String, ByVal TxtFlNme As String, ByVal XLFlNme As String, ByVal valSep As String, ByVal LineSep As String)

Rem 2 Text file info
' 2a) get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = Paf & Application.PathSeparator & TxtFlNme  '                                                              CHANGE TO SUIT                                                                                                        From vixer zyxw1234  : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629    DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' 2b) Split into wholes line _ splitting the text file into rows by splitting by Line Seperator
Dim arrRws() As String: Let arrRws() = Split(TotalFile, LineSep, -1, vbBinaryCompare)
Dim RwCnt As Long: Let RwCnt = UBound(arrRws()) + 1    '  +1 is nedeed as the  Split Function  returns indicies 0 1 2 3 4 5  etc...
' 2c) split first line to determine the Field(column) number
Dim arrClms() As String: Let arrClms() = Split(arrRws(0), valSep, -1, vbBinaryCompare)
Dim ClmCnt As Long: Let ClmCnt = UBound(arrClms()) + 1
' 2d) we can now make an array for all the rows, and  columns
Dim arrOut() As String: ReDim arrOut(1 To RwCnt, 1 To ClmCnt)

Rem 3 An array is built up by _....
Dim Cnt As Long
    For Cnt = 1 To RwCnt '              _.. considering each row of data
    'Dim arrClms() As String
    Let arrClms() = Split(arrRws(Cnt - 1), ",", -1, vbBinaryCompare)  '  ___.. splitting each row into columns by splitting by the comma
    Dim Clm As Long  '
        For Clm = 1 To UBound(arrClms()) + 1
        Let arrOut(Cnt, Clm) = arrClms(Clm - 1)
        Next Clm
    Next Cnt

Rem 4  Finally the array is pasted to a worksheet in a new file
 Workbooks.Add
 ActiveWorkbook.SaveAs Filename:=Paf & Application.PathSeparator & XLFlNme, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 Workbooks("" & XLFlNme & "").Worksheets.Item(1).Range("A1").Resize(RwCnt, ClmCnt).Value = arrOut()

End Function

---

I placed the macro in vba.xlsm
My csv file name is alert..csv

You are making very, very simle mistakes because you are not taking any time to read or understand anything.
Please take more time to read all carefully , otherwise you are wasting my time.

Look again at my macro ... path should be "C:\Users\WolfieeeStyle\Desktop"

You are going too quickly and missing everything.

path should be this

Code:

---

"C:\Users\WolfieeeStyle\Desktop"

---

I agree
But may i know where the csv file name is mentioned in the macro?
File name is not mentioned in the macro(Alert..csv, So how the macro will know which file it has to convert)

Quote:

---

Originally Posted by fixer

file name is not mention in the macro (Alert..csv)
How macro will know which file it has to convert?



..............
1SEC




......
path should be this

Code:

---

"C:\Users\WolfieeeStyle\Desktop"

---

I agree
But may i know where the csv file name is mentioned in the macro?
File name is not mentioned in the macro(Alert..csv, So how the macro will know which file it has to convert)

---

You see what I mean...

you are wasting my time becoz you go too quick and read nothing

I have no more time to waste today.
maybe tomorrow or Sunday

Alan