Convert CSV Files to XLS

[SowBelly]

Hello:

I have approximately 1300 .csv files that I need to convert to .xls
files so I can extract data from a master spreadsheet. Excel
apparently requires a .csv file be open to extract the data.

I could open each one individually and do a save as, but that would be
rather tedious. Is there a way to programmatically do this. The .csv
files are in the following format:

xxnzz.csv where:

xx is 01-29 (notice the leading 0)
n is n
zz is 01-110 (notice the leading 0 on 1 digit numbers and that the
highest number is 3 digits, 2 digit numbers have no leading 0 i.e.
21n99.

"Pigs can't be humans, but humans can be Pigs!"

Eating at the trough of life.

Oink!

SowBelly

 

[Guest]

Hello SowBelly,

I think next code will do the job.
Don't forget to change the start path

Good Luck

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''Start Code''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private StartPath As String

Private Sub Connvert()

Dim X As Integer
Dim Z As Integer
Dim XX As String
Dim ZZ As String

'Change next line to where the documents are
'Don't forget the last "\"
StartPath = "C:\Documents and Settings\Excel Csv\"


For X = 1 To 29
For Z = 1 To 110
'Put in a leading 0 if necesserie
XX = IIf(X < 10, "0" & X, X)
ZZ = IIf(Z < 10, "0" & Z, Z)
'Open the workbook as a CSV File
Workbooks.Open StartPath & XX & "n" & ZZ & ".csv"
'Save As a XLS file
ActiveWorkbook.SaveAs StartPath & XX & "n" & ZZ & ".Xls", xlExcel7
'Close the workbook
ActiveWorkbook.Close
Next Z
Next X

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''End Code''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[Myrna Larson]

I don't have your files to try it, but I think this should work correctly. YOu
said 1300 files, but 29 * 110 is 3190, so some combinations must be missing.
The code will handle that. Note that the new file is named the same, but with
extension XLS. If there's already a file by that name, it's deleted before the
new one is saved.

Option Explicit

Sub ConvertCSVFiles()
Dim xx As Long
Dim zz As Long
Dim BaseName As String
Dim CSVName As String
Dim XLSName As String

For xx = 1 To 29
For zz = 1 To 110
BaseName = Format$(xx, "00") & "n" & Format$(zz, "00")
CSVName = BaseName & ".csv"

If Len(Dir$(CSVName)) Then
Workbooks.Open Filename:=CSVName
XLSName = BaseName & ".xls"

'delete existing XLS file with this name
If Len(Dir$(XLSName)) Then Kill XLSName

With ActiveWorkbook
.SaveAs Filename:=XLSName, FileFormat:=xlNormal
.Close False
End With
End If
Next zz
Next xx
End Sub

 

[SowBelly]

Mr. T:

Code worked great!

Oink!

SowBelly

Hello SowBelly,

I think next code will do the job.
Don't forget to change the start path

Good Luck

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''Start Code''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private StartPath As String

Private Sub Connvert()

Dim X As Integer
Dim Z As Integer
Dim XX As String
Dim ZZ As String

'Change next line to where the documents are
'Don't forget the last "\"
StartPath = "C:\Documents and Settings\Excel Csv\"


For X = 1 To 29
For Z = 1 To 110
'Put in a leading 0 if necesserie
XX = IIf(X < 10, "0" & X, X)
ZZ = IIf(Z < 10, "0" & Z, Z)
'Open the workbook as a CSV File
Workbooks.Open StartPath & XX & "n" & ZZ & ".csv"
'Save As a XLS file
ActiveWorkbook.SaveAs StartPath & XX & "n" & ZZ & ".Xls", xlExcel7
'Close the workbook
ActiveWorkbook.Close
Next Z
Next X

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''End Code''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[SowBelly]

Myrna:

Code worked great!

Oink!

SowBelly

I don't have your files to try it, but I think this should work correctly. YOu
said 1300 files, but 29 * 110 is 3190, so some combinations must be missing.
The code will handle that. Note that the new file is named the same, but with
extension XLS. If there's already a file by that name, it's deleted before the
new one is saved.

Option Explicit

Sub ConvertCSVFiles()
Dim xx As Long
Dim zz As Long
Dim BaseName As String
Dim CSVName As String
Dim XLSName As String

For xx = 1 To 29
For zz = 1 To 110
BaseName = Format$(xx, "00") & "n" & Format$(zz, "00")
CSVName = BaseName & ".csv"

If Len(Dir$(CSVName)) Then
Workbooks.Open Filename:=CSVName
XLSName = BaseName & ".xls"

'delete existing XLS file with this name
If Len(Dir$(XLSName)) Then Kill XLSName

With ActiveWorkbook
.SaveAs Filename:=XLSName, FileFormat:=xlNormal
.Close False
End With
End If
Next zz
Next xx
End Sub