Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the conversion of CSV files to XLSX files
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the conversion of CSV files to XLSX files
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chris" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the conversion of CSV files to XLSX files
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the conversion of CSV files to XLSX files
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chris" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I batch convert 97-2003 .xls files to 2007 .xlsx files | Excel Discussion (Misc queries) | |||
.xls files not deleted after conversion to .xlsx; help please | Excel Discussion (Misc queries) | |||
Cannot Open .xlsx files | Excel Discussion (Misc queries) | |||
Help with xlsx files | New Users to Excel | |||
Batch conversion of XP files to 97 files | Excel Discussion (Misc queries) |