Automating the conversion of CSV files to XLSX files

[Chris]

I have quite a few comma-separated-value (i.e., CSV) files that need to be
converted to XLSX format. Currently, I'm converting each file manually by
opening Excel, initiating an import, changing the original data type from
Fixed Width to Delimited, de-selecting the Tab delimiter, selecting the Comma
delimiter, changing some of the column data formats from General to Text (to
prevent incorrect conversion of those columns from text to numeric), and then
pressing the Finish button.

Is there any way that this process can be automated? Is there any way I
could initiate the process from a DOS command-line?

 

[Jon Peltier]

If the file extension is CSV, this should all happen automatically just by
opening the file, well, except for the column data formats.

Did you try recording a macro while you converted one file manually?

- Jon

 

[Chris]

Thanks, Jon. The values in the first column in these CSV files are 20-digit
numeric codes that Excel is mis-interpreting and incorrectly converting to
exponential format--hence the need to avoid opening the CSV file
directly...instead overriding Excel's default behavior by using the Import
function.

Inexplicably, for these CSV files the Text Import Wizard defaults to using
the Tab character as the delimiter.

Did you try recording a macro while you converted one file manually?

Using a macro, I've been able to automate the process for the files whose
first column contains a 20-digit code. Certain CSV files, however, have
additional columns with 20-digit codes. Rather than pick and choose which
columns to convert to Text fomat, it would be OK to convert *all* of the
columns; however, I can't figure out how to make the Text Import Wizard do
that.

Also, assuming the above can be resolved, can Excel be started from the
command-line with the name of a macro to run?

 

[Jon Peltier]

When I get a case like this, I usually import the data into VBA, process it,
and dump it into the worksheet.

This is going to be a little slow, since it outputs one row at a time, and
it's not totally automatic, but it's a start.

Sub inputTXTfile()
' input text file line by line without interpreting html tabs
' open new sheet first and select home cell

Dim linein As String, fh As Integer, FileName As String, lineNum As
Double
dim vArray as Variant

FileName = Application.GetOpenFilename
If FileName = "False" Then Exit Sub
Application.ScreenUpdating = False
lineNum = 0
fh = FreeFile
Workbooks.Add

Open FileName For Input As fh
Do Until EOF(fh)
Line Input #fh, linein
vArray = Split(linein, ",")
With ActiveCell.Offset(lineNum, 0).Resize(, UBound(vArray) + 1)
.Value = linein
.NumberFormat = "@"
End With
lineNum = lineNum + 1
Loop
Close #fh

Application.ScreenUpdating = True
End Sub


- Jon