Import formats on CSV files (All)

[sjreynolds]

I cannot find any way to get Excel to ask me for the format of aCSV file, short of changing the file extension!

If I import a text file, Excel will ask me for the formats of the columns, but CSV files it automatically assumes a format for each field. Given that I have got fields which may have a leading zero and may be numeric, I get extremely frustrated that Excel lops the leading zero off every time. Even worse is when it decides that my numeric reference is a big number and displays it as an exponential. Even even worse is when I have some fields which are <numeric>E<number> as a (perfectly valid) reference ID. Excel assumes that those are exponentials and converts the E<number> into a perfectly spurious set of additional zeros.

Aargh!

Added to which, if you import a file and change something else and save it, Excel then saves what is displayed, rather than what the underlying data is. So if a long number was displayed as an exponential, it will be saved as an exponential, thus losing any richness in the data!

Double aargh!

So why does Excel do that? Why can't I specify the format of CSV files? And how can I make it do it (short of changing the file extension or unlinking the CSV extension from Excel)

Cheers.

Stuart

[LegareColeman]

You can do it in VBA with something like this:

<pre> With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:Wo rkDOS.csv", _
Destination:=Range("A1"))
.Name = "DOS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
</pre>


Or if you want to do it from the menu, select "Get External Data" from the Data menu, then select "Import Text File" from the fly out menu.

[stuck]

This example code has been a useful starting point for me but...

My CSV file is includes both text and numbers for example, "name","add1","add2","15.20","25.360". I am well aware that the trailing zeros are not mathematically significant but I wish to retain them for presentation purposes. My spreadsheets are already set up to hold such 'numbers' as text while doing the maths on the values. Thus I wish to import the CSV as all text. I expected that the line:
.TextFileTextQualifier = xlTextQualifierDoubleQuote

in conjunction with a line like:
.TextFileColumnDataTypes = Array(xlTextFormat)

would mean that anything inside double quotes (i.e. everything) would end up as text in columns formatted as text and thus the trailing zeros would be retained. Unfortunately the Array(xlTextFormat) bit only seems to set the first column, rather than all columns, to text. If the fix is to specify a column type for each field that's fine for a specific CSV with a given number of fields but not all my CSV's have the same number of fields so how can I handle that?

stuck

[sdckapr]

I would think the easiest thing would be to not associate CSVs with excel, so when you open them you would get the wizard.

Changing the extensions to txt would not be difficult before importing.

If those are not feasible, You would have to create your own parsing routine. It could be done many different ways, most likely to open the text file, loop thru each line, read the line, go char by char reading and looking for the delimiters and qualifiers and parsing as desired. You can parse into an array and ensure that the array is formatted as a string.

If you only are going to do comma delimited with dbl-quote qualifiers and make everything text it is not too involved, but if you need more "bells and whistles" it can get very complex.

Steve

[LegareColeman]

I think that what you want is:

<pre> .TextFileColumnDataTypes = Array(xlTextFormat,xlTextFormat,xlTextFormat,xlTex tFormat,xlTextFormat)
</pre>